萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 百分位數函數詳解:PERCENTILE_CONT vs PERCENTILE_DISC 差異與插值邏輯

函數定義 (SQL Server 2012+)

  • PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY expr) OVER (PARTITION BY ...):連續分布插值,結果**不一定出現在資料中**。
  • PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY expr) OVER (PARTITION BY ...):離散分布,結果**一定是資料中實際存在的值** (最接近百分位的第一個值)。

測試資料與查詢

WITH test 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_075,
       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_075
FROM test;

執行結果分析

namescorepct_rankpc_05pc_06pc_07pc_075pd_05pd_06pd_07pd_075
LeeWhoeeUniversity100.003034384030304040
LeeWhoeeUniversity200.253034384030304040
LeeWhoeeUniversity300.503034384030304040
LeeWhoeeUniversity400.753034384030304040
LeeWhoeeUniversity501.003034384030304040
DePaul600.008084889080809090
DePaul700.258084889080809090
DePaul800.508084889080809090
DePaul900.758084889080809090
DePaul1001.008084889080809090

插值計算邏輯 (PERCENTILE_CONT)

以 pc_06 = 34 為例 (LeeWhoeeUniversity 組):

  1. 已知百分位點:p=0.5 → v=30, p=0.75 → v=40。
  2. 線性插值:v = v_low + (p - p_low) / (p_high - p_low) * (v_high - v_low)
  3. v = 30 + (0.6 - 0.5) / (0.75 - 0.5) * (40 - 30) = 30 + 0.1/0.25 * 10 = 30 + 4 = 34。
  4. 34 **不存在於原始資料** 中,這就是連續插值特性。

離散取值邏輯 (PERCENTILE_DISC)

pd_06 = 40:目標百分位 0.6 介於 0.5 與 0.75 之間,取第一個百分位 ≥ 0.6 的實際值,即 0.75 對應的 40。

應用場景選型

場景推薦函數理由
薪資中位數報表 (參考實際薪資檔)PERCENTILE_DISC(0.5)業務期望看到真實存在的薪資值
SLA 回應時間 P90/P99 (監控儀表板)PERCENTILE_CONT(0.9/0.99)插值更平滑反映分布尾部特徵
分位數分組 (NTILE 替代)PERCENTILE_DISC分組邊界落在實際資料點
統計建模特徵工程PERCENTILE_CONT連續分布假設下的量化特徵

效能注意

  • 需配合 OVER (PARTITION BY ... ORDER BY ...),大分區時會排序,成本較高。
  • 可搭配索引 INDEX idx_partition_order (partition_col, order_col) 避免 Sort 運算子。
  • 僅支援數值/日期時間型別,字串型別需轉換。
copyright © 萬盛學電腦網 all rights reserved