PERCENTILE_CONT 範圍函數

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

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

最終結果如下。

如果 (CRN = FRN = RN)，則結果為 (value of expression from row at RN)

否則結果如下：

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

您只能在 OVER 子句中指定 PARTITION 子句。如果指定 PARTITION，則對於每一列，PERCENTILE_CONT 會傳回在給定分割區內的一組值之中落在指定百分位數的值。

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

語法

PERCENTILE_CONT ( percentile )
WITHIN GROUP (ORDER BY expr)
OVER (  [ PARTITION BY expr_list ]  )

引數

percentile

介於 0 和 1 之間的數值常數。計算時會忽略 Null。

WITHIN GROUP ( ORDER BY expr)

指定要排序和計算百分位數的數值或日期/時間值。

OVER

指定視窗分割。OVER 子句不能包含視窗排序或視窗框規格。

PARTITION BY expr

選擇性引數，針對 OVER 子句中的每一個群組，設定記錄範圍。

傳回值

傳回類型取決於 WITHIN GROUP 子句中 ORDER BY 表達式的資料類型。下表顯示每一個 ORDER BY 表達式資料類型的傳回類型。

輸入類型	傳回類型
INT2、INT4、INT8、NUMERIC、DECIMAL	DECIMAL
FLOAT、DOUBLE	DOUBLE
DATE	DATE
TIMESTAMP	TIMESTAMP

使用須知

如果 ORDER BY 表達式是以最大精確度 38 位數定義的 DECIMAL 資料類型，PERCENTILE_CONT 可能會傳回不準確的結果或錯誤。如果 PERCENTILE_CONT 函數的傳回值超過 38 位數，結果會截斷為適合長度，導致精確度降低。在插補期間，如果中間結果超過最大精確度，則會發生數值溢位，且函數會傳回錯誤。為了避免這些情況，建議使用精確度較低的資料類型，或將 ORDER BY 表達式轉換為較低精確度。

例如，搭配 DECIMAL 引數的 SUM 函數傳回的預設精確度為 38 位數。結果的小數位數和引數的小數位數相同。因此，例如，DECIMAL(5,2) 欄的 SUM 會傳回 DECIMAL(38,2) 資料類型。

下列範例在 PERCENTILE_CONT 函數的 ORDER BY 子句中使用 SUM 函數。PRICEPAID 欄的資料類型是 DECIMAL (8,2)，所以 SUM 函數會傳回 DECIMAL(38,2)。

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

為了避免可能降低精確度或溢位錯誤，請將結果轉換為精確度較低的 DECIMAL 資料類型，如下列範例所示。

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;

範例

下列範例使用 WINSALES 資料表。如需 WINSALES 資料表的描述，請參閱範圍函數範例的範例資料表。

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)

以下範例計算華盛頓州之賣方門票銷售的 PERCENTILE_CONT 和 PERCENTILE_DISC。

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)