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;
| name | score | pct_rank | pc_05 | pc_06 | pc_07 | pc_075 | pd_05 | pd_06 | pd_07 | pd_075 |
|---|---|---|---|---|---|---|---|---|---|---|
| LeeWhoeeUniversity | 10 | 0.00 | 30 | 34 | 38 | 40 | 30 | 30 | 40 | 40 |
| LeeWhoeeUniversity | 20 | 0.25 | 30 | 34 | 38 | 40 | 30 | 30 | 40 | 40 |
| LeeWhoeeUniversity | 30 | 0.50 | 30 | 34 | 38 | 40 | 30 | 30 | 40 | 40 |
| LeeWhoeeUniversity | 40 | 0.75 | 30 | 34 | 38 | 40 | 30 | 30 | 40 | 40 |
| LeeWhoeeUniversity | 50 | 1.00 | 30 | 34 | 38 | 40 | 30 | 30 | 40 | 40 |
| DePaul | 60 | 0.00 | 80 | 84 | 88 | 90 | 80 | 80 | 90 | 90 |
| DePaul | 70 | 0.25 | 80 | 84 | 88 | 90 | 80 | 80 | 90 | 90 |
| DePaul | 80 | 0.50 | 80 | 84 | 88 | 90 | 80 | 80 | 90 | 90 |
| DePaul | 90 | 0.75 | 80 | 84 | 88 | 90 | 80 | 80 | 90 | 90 |
| DePaul | 100 | 1.00 | 80 | 84 | 88 | 90 | 80 | 80 | 90 | 90 |
以 pc_06 = 34 為例 (LeeWhoeeUniversity 組):
p=0.5 → v=30, p=0.75 → v=40。v = v_low + (p - p_low) / (p_high - p_low) * (v_high - v_low)v = 30 + (0.6 - 0.5) / (0.75 - 0.5) * (40 - 30) = 30 + 0.1/0.25 * 10 = 30 + 4 = 34。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 運算子。