PERCENTILE_DISC 範圍函數

PERCENTILE_DISC 是採用離散分佈模型的反向分佈函數。它採用百分位數值和排序規格，且會傳回給定集裡的一個元素。

針對給定的百分位數值 P，PERCENTILE_DISC 排序 ORDER BY 子句中的表達式值，且傳回的值具有大於或等於 P 的最小累積分佈值 (根據相同的排序規格)。

您只能在 OVER 子句中指定 PARTITION 子句。

語法

PERCENTILE_DISC ( 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 表達式相同。

範例

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

SELECT sellerid, qty, PERCENTILE_DISC(0.5) 
WITHIN GROUP (ORDER BY qty) 
OVER() AS MEDIAN FROM winsales;

+----------+-----+--------+
| sellerid | qty | median |
+----------+-----+--------+
| 3        | 10  | 20     |
| 1        | 10  | 20     |
| 1        | 10  | 20     |
| 4        | 10  | 20     |
| 3        | 15  | 20     |
| 2        | 20  | 20     |
| 2        | 20  | 20     |
| 3        | 20  | 20     |
| 1        | 30  | 20     |
| 3        | 30  | 20     |
| 4        | 40  | 20     |
+----------+-----+--------+

SELECT sellerid, qty, PERCENTILE_DISC(0.5) 
WITHIN GROUP (ORDER BY qty) 
OVER(PARTITION BY sellerid) AS MEDIAN FROM winsales;

+----------+-----+--------+
| sellerid | qty | median |
+----------+-----+--------+
| 4        | 10  | 10     |
| 4        | 40  | 10     |
| 3        | 10  | 15     |
| 3        | 15  | 15     |
| 3        | 20  | 15     |
| 3        | 30  | 15     |
| 2        | 20  | 20     |
| 2        | 20  | 20     |
| 1        | 10  | 10     |
| 1        | 10  | 10     |
| 1        | 30  | 10     |
+----------+-----+--------+

若要尋找依賣家 ID 分割時數量的 PERCENTILE_DISC(0.25) 和 PERCENTILE_DISC(0.75)，請使用下列範例。

SELECT sellerid, qty, PERCENTILE_DISC(0.25) 
WITHIN GROUP (ORDER BY qty) 
OVER(PARTITION BY sellerid) AS quartile1 FROM winsales;

+----------+-----+-----------+
| sellerid | qty | quartile1 |
+----------+-----+-----------+
| 4        | 10  | 10        |
| 4        | 40  | 10        |
| 2        | 20  | 20        |
| 2        | 20  | 20        |
| 3        | 10  | 10        |
| 3        | 15  | 10        |
| 3        | 20  | 10        |
| 3        | 30  | 10        |
| 1        | 10  | 10        |
| 1        | 10  | 10        |
| 1        | 30  | 10        |
+----------+-----+-----------+

SELECT sellerid, qty, PERCENTILE_DISC(0.75) 
WITHIN GROUP (ORDER BY qty) 
OVER(PARTITION BY sellerid) AS quartile3 FROM winsales;

+----------+-----+-----------+
| sellerid | qty | quartile3 |
+----------+-----+-----------+
| 3        | 10  | 20        |
| 3        | 15  | 20        |
| 3        | 20  | 20        |
| 3        | 30  | 20        |
| 4        | 10  | 40        |
| 4        | 40  | 40        |
| 2        | 20  | 20        |
| 2        | 20  | 20        |
| 1        | 10  | 30        |
| 1        | 10  | 30        |
| 1        | 30  | 30        |
+----------+-----+-----------+