轻松应对面试手撕SQL之TopN问题，数开面试必考系列二（社招校招均适用）

社会主义打工人即将结束周末（加班一天）的愉快生活，只能说一个字：爽！实在没继续工作的状态，来给大家总结一道面试中大热门的面试题，可以说是top1的：TOPN问题。

工作中也会经常遇到这样的业务问题：如何找到每个产品种类下用户最喜欢的产品是哪个？如果找到每个商品下用户点击最多的5个商品是什么？这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。面对该类问题，如何解决呢？

举例：根据下面示例数据选出, 今年每个学校,每个年级,每个科目前三的学生姓名和他的成绩。

【解题思路】

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

这里是“每个学校,每个年级,每个科目”，结合表的结构，是按学生“学校、年级、科目”来分组。

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

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

4.使用哪个专用窗口函数？为了不受并列成绩的影响，使用row_number专用窗口函数：row_number函数：也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

【解题步骤】

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

SELECT
  year,
  school,
  class,
  subject,
  name,
  score,
  row_number() OVER (
    PARTITION BY year,
    school,
    class,
    subject
    ORDER BY
      score DESC
  ) AS rank
FROM
  topn_scores
WHERE
  year = 2023

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

SELECT
  year,
  school,
  class,
  subject,
  name,
  score
FROM
  (
    SELECT
      year,
      school,
      class,
      subject,
      name,
      score,
      row_number() OVER (
        PARTITION BY year,
        school,
        class,
        subject
        ORDER BY
          score DESC
      ) AS rank
    FROM
      topn_scores
    WHERE
      year = 2023 
  ) subquery
WHERE
  rank <= 3;

【问题总结】

1）考察如何使用窗口函数及专用窗口函数排名的区别：rank, dense_rank, row_number

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

 TopN问题 sql模板

select
  *
from
  (
    select
      *,
      row_number() over (
        partition by 要 分 组 的 列 名
        order by
          要 排 序 的 列 名 desc
      ) as rank
    from
      表 名
  ) as a
where
  rank <= N;

类似的问题还有很多，我们都可以用上面的模板来处理。很重要哈。