求TopN问题解答

【题目】

“成绩表”记录了学生的学号，学生选修的课程，以及对应课程的成绩。

为了对学生成绩进行考核，现需要查询每门课程的前3高成绩。
注意：如果出现并列第一的情况，则同为第一名。


【解题思路】
题目要求找出每个课程获得前三高成绩的所有学生。难点在于每个课程前3高成绩。
前3高的成绩意味着要对成绩排名。

这种题类型其实是“分组排名”，专用窗口函数rank, dense_rank, row_number有什么区别呢？

它们的区别我举个例子，你们一下就能看懂：

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级;

得到结果：


从上面的结果可以看出：
1) rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

2) dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

3) row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

这三个函数的区别如下：



题目要求“如果出现并列第一的情况，则同为第一名”。所以，我们使用窗口函数dense_rank。

步骤一：按课程分组(partiotion by 课程号)，并按成绩降序排列(order by 成绩 desc)，套入窗口函数的语法，就是下面的sql语句：

select *,
     dense_rank() over(partition by 课程号
                       order by 成绩 desc) as排名
from 成绩表;

运行结果如下：


步骤二：筛选出前3高的成绩，所以我们在上一步基础上加入一个where字句来筛选出符合条件的数据。（where 排名 <=3）

select 课程号,学号,成绩,排名 
from
  (select *,
       dense_rank() over (partition by 课程号
                    order by 成绩 desc) as 排名
  from 成绩表) as t1
where 排名 <=3;

【本题考点】

1）考察如何使用窗口函数及专用窗口函数排名的区别：rank, dense_rank, row_number
2）经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

-- topN问题 sql模板
select *
from (
   select *, 
          row_number() over (partition by 要分组的列名
                       order by 要排序的列名 desc) as 排名
   from 表名) as a
where 排名 <= N;

【举一反三】

Employee 表包含所有员工信息，每个员工有其对应的工号( Id)，姓名 (Name)，工资 (Salary) 和部门编号( DepartmentId) 。


部门表、员工表
查找每个部门前三高工资的员工。例如，根据上述给定的表，查询结果应返回：


参考答案：

SELECT
    d.Name,
    t2.Name,
    t2.Salary
FROM
    (
        SELECT
            DepartmentId,
            Name,
            Salary
        FROM
            (
                SELECT *,
                       dense_rank() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS ranking
                FROM
                    Employee
            ) AS t1
        WHERE
            ranking <= 3
    ) t2
        JOIN department d
             ON t2.DepartmentId = d.Id