图解SQL面试题：经典topN问题

工作中会经常遇到这样的业务问题：

如果找到每个类别下用户点击最多的5个商品是什么？

这类问题其实就是常见的：每组最大的N条记录(topN)。

【题目】

现有“成绩表”，记录了每个学生各科的成绩。表内容如下：

问题：查找每个学生成绩最高的2个科目

【解题思路】

1.看到问题中要查“每个”学生最高的成绩。还记得我们之前课程里讲过的吗？当有“每个”出现的时候，就要想到是要分组了。

这里是“每个学生”，结合表的结构，是按学生“姓名”来分组。

2.将表按学生姓名分组后，把成绩按降序排列，排在最前面的2个就是我们要找的“成绩最高的2个科目”。

3.现在分组后，需要排序，又不减少原表的行数，这种功能自然想到是窗口函数。

4.使用哪个专用窗口函数？ 为了不受并列成绩的影响，使用row_number专用窗口函数：

row_number函数：也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

【解题步骤】

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

select *, 
      row_number() over (partition by 姓名
                   order by 成绩 desc) as ranking
from 成绩表

运行结果如下：

步骤二：如上表黄色框内的数据，每个同学成绩最好的2个科目，就是要求的解。

想得到这个解，只要提取出“ranking”值小于等于2的数据就可以了。那么，只需要在上一步的slq语句里加入条件字句where就可以了

select *, 
      row_number() over (partition by 姓名
                   order by 成绩 desc) as ranking
from 成绩表
where ranking <=2

很多同学都会用这样的思路解题，但是这样写，sql会报错，为什么呢？

我们在《从零学会sql》里多次强调过，要牢记sql的书写顺序和运行顺序。在运行顺序中，select字句是最后被运行的。

当明白了运行顺序以后，就知道错误的原因了：运行到”where ranking > 2”的时候，因为select字句还没有被执行，因此select中的“ranking”列还没有出现，从而导致报错。

解决方法是什么呢？

这种情况就可以用子查询，也就是把第一步得到查询结果作为一个新的表，sql语句如下：

select *
from (
   select *, 
          row_number() over (partition by 姓名
                       order by 成绩 desc) as ranking
   from 成绩表) as a
where ranking <=2

得到结果：

【本题考点】

1.主要考查对窗口函数的灵活使用。

2.在筛选过程中，非常容易因为子查询问题报错，本题也考察了对子查询的熟练运用。

3.本题间接考察了对sql语句执行顺序的熟悉程度。

【举一反三】

经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

本题的sql语句修改下（将where字句里的条件修改成N），就可以成为这类问题的一个万能模板，遇到这类问题往里面套就可以了：

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

我是猴子，中科院硕士/前IBM高级软件工程师/豆瓣8分《数据分析思维》作者