PERCENTILE_CONT 函數 - Amazon Redshift

# PERCENTILE_CONT 函數

PERCENTILE_CONT 是採用連續分佈模型的反向分佈函數。它採用百分位數值和排序規格，且會傳回插入值，該值將根據排序規格落入給定的百分位數值。

PERCENTILE_CONT 在值排序後計算值之間的線性插值。此函數在列根據排序規格來排序後，使用彙總群組中的百分位數值 `(P)` 和非 Null 列數 `(N)` 來計算列號。此列號 `(RN)` 是根據公式 `RN = (1+ (P*(N-1))` 來計算。彙總函數的最終結果是以列號 `CRN = CEILING(RN)``FRN = FLOOR(RN)` 各列的值之間的線性插值來計算。

```(CRN - RN) * (value of expression for row at FRN) + (RN - FRN) * (value of expression for row at CRN)```.

PERCENTILE_CONT 是僅限於運算節點的函數。如果查詢未參考使用者定義的資料表或 Amazon Redshift 系統資料表，此函數會傳回錯誤。

## Syntax (語法)

``````PERCENTILE_CONT ( percentile )
WITHIN GROUP (ORDER BY expr)``````

## 引數

WITHIN GROUP ( ORDER BY expr)

## 傳回值

INT2、INT4、INT8、NUMERIC、DECIMAL DECIMAL
FLOAT、DOUBLE DOUBLE
DATE DATE
TIMESTAMP TIMESTAMP
TIMESTAMPTZ TIMESTAMPTZ

## 使用須知

``````select top 10 salesid, sum(pricepaid),
percentile_cont(0.6) within group (order by salesid),
median (pricepaid)
from sales group by salesid, pricepaid;

An error occurred when executing the SQL command:
select top 10 salesid, sum(pricepaid),
percentile_cont(0.6) within group (order by salesid),
median (pricepaid)
from sales group by salesid, pricepai...

ERROR: within group ORDER BY clauses for aggregate functions must be the same``````

``````select top 10 salesid, sum(pricepaid),
percentile_cont(0.6) within group (order by salesid),
median (salesid)
from sales group by salesid, pricepaid;``````

## 範例

``````select top 10  distinct sellerid, qtysold,
percentile_cont(0.5) within group (order by qtysold),
median (qtysold)
from sales
group by sellerid, qtysold;

sellerid | qtysold | percentile_cont | median
---------+---------+-----------------+-------
1 |       1 |             1.0 |    1.0
2 |       3 |             3.0 |    3.0
5 |       2 |             2.0 |    2.0
9 |       4 |             4.0 |    4.0
12 |       1 |             1.0 |    1.0
16 |       1 |             1.0 |    1.0
19 |       2 |             2.0 |    2.0
19 |       3 |             3.0 |    3.0
22 |       2 |             2.0 |    2.0
25 |       2 |             2.0 |    2.0``````