图解面试题:经典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 成绩 descas rankingfrom 成绩表

运行结果如下:

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

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

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

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

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

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

解决方法是什么呢?

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

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

得到结果:

【本题考点】

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

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

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

【举一反三】

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

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

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

推荐:如何从零学会SQL?

发布了333 篇原创文章 · 获赞 672 · 访问量 55万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览