题解 | #考试分数(五)#

这道题目要求我们查询每个岗位分数的中位数位置上的所有成绩信息，并按用户ID升序排序。我们要做的事情如下：

1. 确定总体问题

我们需要查询每个岗位分数的中位数位置上的所有成绩信息，并按用户ID升序排序。中位数位置的确定取决于岗位人数的奇偶性：如果人数是奇数，中位数位置是一个数；如果人数是偶数，中位数位置是两个数。我们需要使用窗口函数ROW_NUMBER和COUNT来实现这一点。

2. 分析关键问题

• 计算岗位人数和分数排名：使用COUNT和ROW_NUMBER窗口函数计算每个岗位的总人数和分数排名。
• 确定中位数位置：根据岗位人数的奇偶性确定中位数位置。
• 筛选中位数位置的记录：通过WHERE子句筛选出中位数位置的记录。
• 排序输出：按用户ID升序排列输出结果。

3. 解决每个关键问题的代码及讲解

步骤1：计算岗位人数和分数排名

我们使用COUNT和ROW_NUMBER窗口函数计算每个岗位的总人数和分数排名：

    count(*) over(partition by job) as total,
    row_number() over(partition by job order by score desc) as t_rank

• COUNT(*) OVER (PARTITION BY job) AS total: 计算每个岗位的总人数。
• ROW_NUMBER() OVER (PARTITION BY job ORDER BY score DESC) AS t_rank: 计算每个岗位的分数排名，按分数降序排列。

COUNT(*)：普通聚合函数，用于计算整个表或分组的行数，必须与 GROUP BY 子句一起使用，否则不能与非聚合列一起使用。

COUNT(*) OVER (...)：窗口函数，用于在一组行上执行聚合计算，同时保留每一行的细节，可以与非聚合列一起使用，不会违反 ONLY_FULL_GROUP_BY 模式。

步骤2：确定中位数位置

我们使用CASE语句根据岗位人数的奇偶性确定中位数位置：

t_rank = case when total%2=1 then ceil(total/2) else ceil(total/2)+1 end or t_rank = ceil(total/2)

• CASE WHEN total%2=1 THEN CEIL(total/2) ELSE CEIL(total/2)+1 END: 确定中位数位置，若总数为奇数，则中位数位置为[ceil(total/2),ceil(total/2)]，否则为[ceil(total/2),ceil(total/2)+1]。
• CEIL：对浮点数上取整

步骤3：筛选中位数位置的记录

我们通过WHERE子句筛选出中位数位置的记录：

where 
t_rank = case when total%2=1 then ceil(total/2) else ceil(total/2)+1 end or t_rank = ceil(total/2)

• WHERE t_rank = ...: 筛选出中位数位置的记录。

步骤4：排序输出

我们按用户ID升序排列输出结果：

order by id

• ORDER BY id: 按用户ID升序排列。

完整代码

select
    id,job,score,t_rank
from (
    select
        id,job,score,
        count(*) over(partition by job) as total,
        row_number() over(partition by job order by score desc) as t_rank
    from 
        grade
) sub
where 
    t_rank = case when total%2=1 then ceil(total/2) else ceil(total/2)+1 end or t_rank = ceil(total/2)
order by id;