SQL Server PERCENTILE_CONT 與 PERCENTILE_DISC 差異深度解析：連續 vs 離散百分位數

前言

SQL Server 2012 引入兩個視窗函數計算**百分位數**：

• PERCENTILE_CONT：連續分布插值，**結果可能不存在於資料中**。
• PERCENTILE_DISC：離散分布，**結果必定存在於資料中** (最接近百分位的實際值)。

語法：PERCENTILE_CONT | _DISC (numeric_literal) WITHIN GROUP (ORDER BY expression) OVER (PARTITION BY ...)

範例資料與查詢

WITH TestData AS (
    SELECT N'LeeWhoeeUniversity' AS name, 10 AS score UNION ALL
    SELECT N'LeeWhoeeUniversity', 20 UNION ALL
    SELECT N'LeeWhoeeUniversity', 30 UNION ALL
    SELECT N'LeeWhoeeUniversity', 40 UNION ALL
    SELECT N'LeeWhoeeUniversity', 50 UNION ALL
    SELECT N'DePaul', 60 UNION ALL
    SELECT N'DePaul', 70 UNION ALL
    SELECT N'DePaul', 80 UNION ALL
    SELECT N'DePaul', 90 UNION ALL
    SELECT N'DePaul', 100
)
SELECT 
    name, score,
    PERCENT_RANK() OVER (PARTITION BY name ORDER BY score) AS pct_rank,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY name) AS pc_05,
    PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY name) AS pc_06,
    PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY name) AS pc_07,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY name) AS pc_75,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY name) AS pd_05,
    PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY name) AS pd_06,
    PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY name) AS pd_07,
    PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY name) AS pd_75
FROM TestData;

結果對照表

核心差異圖解

LeeWhoeeUniversity 分數排序: 10, 20, 30, 40, 50
百分位定位:
  0.5 (中位數) -> 正好落在 30 (第 3 個) -> 兩函數同為 30
  0.6 -> 落在 30(0.5) 與 40(0.75) 之間
    PERCENTILE_CONT: 線性插值 = 30 + (40-30) * (0.6-0.5)/(0.75-0.5) = 30 + 10 * 0.4 = 34  (資料中無 34)
    PERCENTILE_DISC: 取「大於等於 0.6 的第一個百分位對應值」= 0.75 對應 40  (資料中有 40)
  0.7 -> CONT 插值 38, DISC 取 40
  0.75 -> 正好落在 40 -> 同為 40

數學定義

• PERCENTILE_CONT(p)：將排序值視為連續分布累積分佈函數 (CDF)，對 p 做線性插值。
  公式：v = v_k + (v_{k+1} - v_k) * (p - p_k) / (p_{k+1} - p_k)，其中 p_k ≤ p ≤ p_{k+1}。
• PERCENTILE_DISC(p)：回傳 **CUME_DIST ≥ p 的第一個值**。
  CUME_DIST = (該值排名) / (總列數)，等同 PERCENT_RANK 但分母不同。

選型指南

效能與注意事項

1. 皆為**視窗函數**，需配合 OVER (PARTITION BY ...)；無 PARTITION 視為單一分區。
2. WITHIN GROUP (ORDER BY ...) 必須指定排序欄位，決定百分位計算順序。
3. 大資料集計算較耗資源 (需排序)；可考慮預先彙總到摘要表、或使用 Columnstore Index 批次模式加速。
4. numeric_literal 必須為常數 (0.0 ~ 1.0)，不可為變數/欄位；動態百分位需動態 SQL。

相關函數速查