本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
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)
.
語法
PERCENTILE_CONT(percentile) WITHIN GROUP(ORDER BY expr)
引數
- percentile
-
介於 0 到 1 之間的數值常數。計算中會忽略
NULL
值。 - expr
-
指定要排序和計算百分位數的數值或日期/時間值。
傳回值
傳回類型是由子句中 ORDER WITHIN GROUP BY 運算式的資料類型決定。下表顯示每個 ORDER BY 運算式資料類型的傳回類型。
輸入類型 | 傳回類型 |
---|---|
INT2 , INT4 , INT8 , NUMERIC , DECIMAL |
DECIMAL |
FLOAT , DOUBLE |
DOUBLE |
DATE |
DATE |
TIMESTAMP |
TIMESTAMP |
TIMESTAMPTZ |
TIMESTAMPTZ |
使用須知
如果 ORDER BY 運算式是以 38 位數的最大精確度定義的DECIMAL資料類型,則 PERCENTILE _ 可能CONT會傳回不正確的結果或錯誤。如果 PERCENTILE _ CONT 函數的返回值超過 38 位數,則結果將被截斷以適應,這會導致精度損失。在插補期間,如果中間結果超過最大精確度,則會發生數值溢位,且函數會傳回錯誤。若要避免這些情況,我們建議您使用精確度較低的資料類型,或將 ORDER BY 運算式轉換為較低的精確度。
如果陳述式包含對以排序為基礎的彙總函式 (LISTAGGCONT、PERCENTILE _ 或MEDIAN) 的多個呼叫,它們都必須使用相同的 ORDER BY 值。請注意,在運算式值上MEDIAN套用隱含順序。
例如,下列陳述式會傳回錯誤。
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, pricepaid; 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;
範例
下列範例使用範TICKIT例資料庫。如需詳細資訊,請參閱範本資料庫。
下面的例子顯示 PERCENTILE _ CONT (0.5) 產生的結果相同MEDIAN。
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 | +----------+---------+-----------------+--------+ | 2 | 2 | 2 | 2 | | 26 | 1 | 1 | 1 | | 33 | 1 | 1 | 1 | | 38 | 1 | 1 | 1 | | 43 | 1 | 1 | 1 | | 48 | 2 | 2 | 2 | | 48 | 3 | 3 | 3 | | 77 | 4 | 4 | 4 | | 85 | 4 | 4 | 4 | | 95 | 2 | 2 | 2 | +----------+---------+-----------------+--------+
下列範例會針對表格中每個賣家編號的售出數量,找PERCENTILE出 PERCENTILE _ CONT (0.5) 和 _ (0.75)。CONT SALES
SELECT sellerid, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY qtysold) as pct_05, PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY qtysold) as pct_075 FROM sales GROUP BY sellerid ORDER BY sellerid LIMIT 10;
+----------+--------+---------+ | sellerid | pct_05 | pct_075 | +----------+--------+---------+ | 1 | 1.5 | 1.75 | | 2 | 2 | 2.25 | | 3 | 2 | 3 | | 4 | 2 | 2 | | 5 | 1 | 1.5 | | 6 | 1 | 1 | | 7 | 1.5 | 1.75 | | 8 | 1 | 1 | | 9 | 4 | 4 | | 12 | 2 | 3.25 | +----------+--------+---------+
若要驗證第一個 sellerid 的先前查詢結果,請使用下列範例。
SELECT qtysold FROM sales WHERE sellerid=1;
+---------+ | qtysold | +---------+ | 2 | | 1 | +---------+