基于平均分计算运动员百分位的SQL Server查询需求

现有运动员得分原始数据：

已通过以下SQL计算每位运动员的平均分：

SELECT t.athleteId, AVG(t.points) AS average 
FROM table t 
GROUP BY t.athleteId

得到平均分结果：

现在需要编写SQL Server查询，基于平均分计算运动员在原始表中的百分位，同时获取最佳得分、总得分数量、最佳得分排名，期望结果如下：

WITH AthleteStats AS (
    -- 计算每位运动员的基础统计数据：平均分、最佳得分、个人得分数量
    SELECT 
        athleteId,
        AVG(points) AS average,
        MAX(points) AS bestScore,
        COUNT(*) AS athleteScoreCount
    FROM table
    GROUP BY athleteId
),
TotalScores AS (
    -- 获取原始表的总得分记录数
    SELECT COUNT(*) AS totalScores FROM table
),
RankData AS (
    -- 关联总记录数，计算最佳得分的排名
    SELECT 
        s.athleteId,
        s.average,
        s.bestScore,
        ts.totalScores,
        RANK() OVER (ORDER BY s.bestScore DESC) AS rank
    FROM AthleteStats s
    CROSS JOIN TotalScores ts
)
-- 计算百分位并格式化结果
SELECT 
    athleteId,
    average,
    bestScore,
    totalScores,
    rank,
    ROUND((rank - 1) * 1.0 / (totalScores - 1), 2) AS percentile
FROM RankData
ORDER BY rank;

• AthleteStats：聚合得到每个运动员的平均分、最高得分（最佳得分）以及该运动员自身的得分数量。
• TotalScores：单独统计原始表中所有得分记录的总数。
• RankData：通过CROSS JOIN关联总记录数，使用RANK()函数按最佳得分倒序计算排名。
• 最终查询：用公式(排名 - 1)/(总记录数 - 1)计算百分位，通过ROUND()保留两位小数，匹配期望结果格式。

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