掌握SQL高级功能 ！实战业务问题分析

点击上方“ 涛哥聊Python ”，选择“星标”公众号

重磅干货，第一时间送达

来源：知乎

作者：快乐鸭

地址： https://zhuanlan.zhihu.com/p/105871965

本文仅作学术分享，若侵权，请联系后台删文处理

前言： 本文使用的窗口函数需要 Mysql 8

1. 窗口函数

基本语法：

<窗口函数> over (partition by <用户分组的列名> order by <用户 排序 的列名>)

窗口函数的位置可以放一下两种函数：

• 专用窗口函数：rank,dense_rank,low_number

• 聚合函数：sum,avg,count,max,min

窗口函数是对where或者group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在select 子句中。

2. 专用窗口函数rank

转成

select *,rank() over(partition by 班级 order by 成绩 desc) as ranking from 班级表

3. 专用函数rank,dense_rank,row_number有什么区别呢？

select *,rank() over(order by 成绩 desc) as ranking,dense_rank() over (order by 成绩 desc)as desc_rank,run_number() over (order by 成绩 desc)as row_num from 班级表

4. 题目

下图是"班级"表中的内容，记录了每个学生所在班级，和对应的成绩。

正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2。 所以用dense_rank

5. 【面试题类型】topN问题

• 分组取每组最大值

案例：按课程号分组取成绩最大值所在行的数据

select 课程号,max(成绩) as 最大成绩from scoregroup by 课程号;

• 分组取每组最小值

案例：按课程号分组取成绩最小值所在行的数据（意思是每个课程最小值的学生信息都要出来）

• 关联子查询

select *from score awhere 成绩=(select min(成绩)from score bwhere b.课程号=a.课程号)

案例：查询各科成绩前两名的记录

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

很容易写成这样的错误写法，是因为where先执行，但是where就用了select里面的东西所以会报错

select *from (select *,row_number() over(partition by 学号order by 成绩 desc) as ranking from score) as awhere ranking <=2

所以我们要把内容转移到from里面，然后select * 因为from和select是一起运行的

select *from (select *,row_number() over(partition by 学号order by 成绩 desc) as ranking from score) as awhere ranking <=2

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

select *from(select *,row_number() over(partition by 要分组的列order by 要排序的列 desc) as ranking from 表名) as awhere ranking<= n;

6. 聚和窗口函数

select *,sum(成绩) over(order by 学号) as current_sum,avg(成绩) over(order by 学号) as current_avg,count(成绩) over (order by 学号) as current_countmin(成绩) over (order by 学号) as current_minfrom 班级表

得到

这样使用窗口函数的作用就是，可以在每一行的数据可以直观的看到，截止到本行数据，统计数据是多少行，同时可以看到每一行数据，对整体统计数据的影响。

7. 如何在每个组里面比较

问题：查找单科成绩高于该科目平均成绩的学生名单

• 窗口函数写法

select *from(select *,avg(成绩) over(PARTITION by 课程号) as 平均成绩from score)as awhere 成绩>平均成绩

• 关联子查询：

select *from score awhere 成绩=(select avg(成绩)from score bwhere b.课程号=a.课程号)

输出结果还是有所不同的，要注意！

8. 窗口函数的移动平均

select *,avg(成绩) over (order by 学号 rows 2 preceding) as current_avgfrom 班级表

用了rows和preceding这两个关键字是之前-行的意思，也就是自身结果的之前两行的平均，一共三行平均。

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：

在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。

9. 总结

partition是可以省略的，省略就是不指定分组。

order by 加上去如果是用avg,sum这样的函数的话就是计算相邻的数据，所以如果遇到要每组数据大于平均数据的业务问题的话就不能加order by了，不然出来的平均数就不对了

窗口函数使用场景

1. 经典top N问题

找出每个部门排名前N的员工进行奖励

2. 经典排名问题

业务需求“在每组内排名”，比如：每个部门按业绩来排名

3. 在每个组里比较的问题

比如查找每个组里大于平均值的数据，可以有两种方法：

• 方法1，使用前面窗口函数案例来实现

• 方法2，使用关联子查询

这次的题目和知识点比较难，大家可能会需要花几个小时理解和尝试，加油！

今日头条张一鸣：做CEO要避免理性的自负

硬核！16000 字 Redis 面试知识点总结，建议收藏！

Python里最难的Asyncio，这里有一份非常适合小白的教程