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)
OVER (  [ PARTITION BY expr_list ]  )
``````

## 引數

WITHIN GROUP ( ORDER BY expr)

OVER

PARTITION BY expr

## 傳回值

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

## 使用須知

``````select salesid, sum(pricepaid), percentile_cont(0.6)
within group (order by sum(pricepaid) desc) over()
from sales where salesid < 10 group by salesid;``````

``````select salesid, sum(pricepaid), percentile_cont(0.6)
within group (order by sum(pricepaid)::decimal(30,2) desc) over()
from sales where salesid < 10 group by salesid;``````

## 範例

``````select sellerid, qty, percentile_cont(0.5)
within group (order by qty)
over() as median from winsales;

sellerid | qty | median
----------+-----+--------
1 |  10 |   20.0
1 |  10 |   20.0
3 |  10 |   20.0
4 |  10 |   20.0
3 |  15 |   20.0
2 |  20 |   20.0
3 |  20 |   20.0
2 |  20 |   20.0
3 |  30 |   20.0
1 |  30 |   20.0
4 |  40 |   20.0
(11 rows)
``````
``````select sellerid, qty, percentile_cont(0.5)
within group (order by qty)
over(partition by sellerid) as median from winsales;

sellerid | qty | median
----------+-----+--------
2 |  20 |   20.0
2 |  20 |   20.0
4 |  10 |   25.0
4 |  40 |   25.0
1 |  10 |   10.0
1 |  10 |   10.0
1 |  30 |   10.0
3 |  10 |   17.5
3 |  15 |   17.5
3 |  20 |   17.5
3 |  30 |   17.5
(11 rows)
``````

``````SELECT sellerid, state, sum(qtysold*pricepaid) sales,
percentile_cont(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over(),
percentile_disc(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over()
from sales s, users u
where s.sellerid = u.userid and state = 'WA' and sellerid < 1000
group by sellerid, state;

sellerid | state |  sales  | percentile_cont | percentile_disc
----------+-------+---------+-----------------+-----------------
127 | WA    | 6076.00 |         2044.20 |         1531.00
787 | WA    | 6035.00 |         2044.20 |         1531.00
381 | WA    | 5881.00 |         2044.20 |         1531.00
777 | WA    | 2814.00 |         2044.20 |         1531.00
33 | WA    | 1531.00 |         2044.20 |         1531.00
800 | WA    | 1476.00 |         2044.20 |         1531.00
1 | WA    | 1177.00 |         2044.20 |         1531.00
(7 rows)``````