SQL查询问题：特定学生的排名与位次获取异常

问题描述 #

编写SQL查询，根据特定班级、学期的平均分获取学生的排名与位次，但添加WHERE student_id=870查询特定学生时，位次列始终返回1，实际应为4。移除该条件后，能得到班级学生的完整排名表。

原查询代码：

WITH RankedAverages AS (
    SELECT
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`,
        CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS unique_average,
        TRUNCATE(AVG(ft_tot_score), 0) AS average_range
    FROM
        ftscores_primary
    WHERE
        `student_id`=870 AND 
        class_id = 9 AND
        section_id = 3 AND
        session_id = 19
    GROUP BY
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`
),
RankedWithDenseRank AS (
    SELECT
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`,
        unique_average,
        DENSE_RANK() OVER (ORDER BY average_range DESC) AS dense_rank
    FROM
        RankedAverages
),
RankedWithPositions AS (
    SELECT
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`,
        unique_average,
        CASE
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 1 THEN 1
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 2 THEN 2
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 3 THEN 3
            ELSE dense_rank + 1
        END AS position
    FROM
        RankedWithDenseRank
)
SELECT
    `student_id`,
    `class_id`,
    `section_id`,
    `session_id`,
    unique_average,
    position
FROM
    RankedWithPositions
ORDER BY
    position, unique_average DESC;

问题原因 #

原查询在第一个CTE RankedAverages 中直接过滤了student_id=870，导致后续窗口函数（DENSE_RANK()、RANK()）仅基于单条数据计算。窗口函数的排名逻辑依赖当前结果集的全部数据，当结果集只有一条记录时，排名必然为1，无法得到正确的班级位次。

解决方案 #

先计算整个班级所有学生的平均分和排名，最后再筛选目标学生。这样窗口函数能基于完整的班级数据计算正确位次。

修改后的查询代码：

WITH RankedAverages AS (
    SELECT
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`,
        CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS unique_average,
        TRUNCATE(AVG(ft_tot_score), 0) AS average_range
    FROM
        ftscores_primary
    WHERE
        class_id = 9 AND
        section_id = 3 AND
        session_id = 19
    GROUP BY
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`
),
RankedWithPositions AS (
    SELECT
        `student_id`,
        `class_id`,
        `section_id`,
        `session_id`,
        unique_average,
        -- 按照原逻辑计算position
        CASE
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 1 THEN 1
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 2 THEN 2
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 3 THEN 3
            ELSE DENSE_RANK() OVER (ORDER BY average_range DESC) + 1
        END AS position
    FROM
        RankedAverages
)
SELECT
    `student_id`,
    `class_id`,
    `section_id`,
    `session_id`,
    unique_average,
    position
FROM
    RankedWithPositions
WHERE
    student_id = 870 -- 最后筛选目标学生
ORDER BY
    position, unique_average DESC;

优化说明 #

1. 移除第一个CTE中的student_id=870过滤条件，确保计算所有班级学生的平均分
2. 合并多层CTE，在一个步骤内完成排名逻辑计算，简化查询结构
3. 最后一步再筛选目标学生，保证排名基于完整班级数据计算

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