在SQL Server中自定义百分位计算：如何实现目标百分位？

问题背景 #

现有T-SQL查询中，使用PERCENT_RANK()得到的百分位（pctile）与期望的百分位（desired_pctile）存在差异：

• pctile：排名低于当前记录的其他记录占比
• desired_pctile：排名小于等于当前记录的记录占比

核心差异点：

1. 同排名记录需取最大值而非最小值（参考Python示例逻辑）
2. 期望以总记录数N作为分母，而非PERCENT_RANK()默认的N-1

Python中等效实现为使用pandas的rank()函数，参数method='max'、ascending=False、pct=True，示例代码：

pd.Series([1,1,1,2,3,3,4,5]).rank(method='max', ascending=False, pct=True)

解答 #

能否用PERCENT_RANK或类似函数直接实现？ #

不能。PERCENT_RANK()的计算逻辑是(当前行排名-1)/(总记录数-1)，既不满足同排名取最大值的要求，也使用了N-1作为分母，无法直接得到期望结果。

最简单的实现方法 #

通过窗口函数结合聚合计算即可实现，核心思路是先计算每个值在降序排列下的最大排名，再用该排名除以总记录数得到百分位。

示例代码1（分步清晰版）

WITH data AS (
    SELECT value
    FROM (VALUES (1),(1),(1),(2),(3),(3),(4),(5)) t(value)
),
ranked_data AS (
    SELECT 
        value,
        -- 降序排列，同值取最大排名
        MAX(RANK() OVER(ORDER BY value DESC)) OVER(PARTITION BY value) AS max_rank,
        COUNT(*) OVER() AS total_count
    FROM data
)
SELECT 
    value,
    -- 计算期望的百分位：最大排名 / 总记录数
    CAST(max_rank AS FLOAT) / total_count AS desired_pctile
FROM ranked_data
GROUP BY value, max_rank, total_count
ORDER BY value;

示例代码2（简洁版）

WITH data AS (
    SELECT value
    FROM (VALUES (1),(1),(1),(2),(3),(3),(4),(5)) t(value)
)
SELECT 
    value,
    CAST(MAX(ROW_NUMBER() OVER(ORDER BY value DESC)) OVER(PARTITION BY value) AS FLOAT) 
    / COUNT(*) OVER() AS desired_pctile
FROM data
GROUP BY value
ORDER BY value;

关于"pctile乘以COUNT()/(COUNT()-1)"的疑问 #

这种方式只能解决分母从N-1改为N的问题，但无法处理同排名取最大值的核心需求，因此不能直接通过该转换得到正确的desired_pctile。

内容的提问来源于stack exchange，提问作者Raisin