日常生活中,经常会遇到需要在每组内排名的问题,比如每个部门按业绩排名,找出每个部门排名前N的员工等,面对这类需求,就需要使用SQL的高级功能--窗口函数。
窗口函数,也叫联机分析处理函数(Online Anallytical Processing,OLAP),可以对数据库数据进行实时分析处理。
窗口函数的基本语法:
<窗口函数> over (PARTITION BY <用于分组的列名>
ORDER BY <用于排序的列名>)
窗口函数是对WHERE或者GROUP BY子句处理后的即通过进行操作,所以窗口函数原则上只能写在SELECT子句中。
![26ef139b732e0b6f913063a0a08608f3.png](https://i-blog.csdnimg.cn/blog_migrate/0a7d18608315792d24cad7b830438f29.jpeg)
题1:表class中记录了每个学生所在班级和对应的成绩,现在需要在每个班级内按成绩排名,如果两个学生分数相同,那么排名并列;
![12d47f5a96b9760daad693e3bbba552d.png](https://i-blog.csdnimg.cn/blog_migrate/9b65c8111bcb3bf96632f5819ee71359.png)
解题思路:
- 涉及到排名问题,可以使用窗口函数;
- 脑图中已有三种专用窗口函数的区别,根据题目要求的排名规则,使用dense_rank函数;
对“每个班级内按成绩排名”进行解读:
- 每个班级内:按班级分组,(PARTITION BY 班级)对班级分组;
- 按成绩排名:ORDER BY子句对分组后的结果排序,默认升序(ASC),本例中(ORDER BY 成绩 DESC)按成绩列排序,DESC表降序;
![c62f135be85629ad9302f99f876407d6.png](https://i-blog.csdnimg.cn/blog_migrate/d60832a4aa363d168a37ba04799e2cf1.png)
问题:GROUP BY子句有分组功能,ORDER BY子句有排序功能,为什么还要用窗口函数呢?
答:因为GROUP BY分组汇总后表的行数改变,一行只有一个类别,而PARTITION BY和dense_rank函数不会减少原表中的行数。
比如统计每个班级的人数:
![975a6940953e46746273b46a3fa5d89c.png](https://i-blog.csdnimg.cn/blog_migrate/95a4bbccf99d758c231b3d5c702880ce.png)
![8b4fe8ba05f255e6e40c364a55bf7d6c.png](https://i-blog.csdnimg.cn/blog_migrate/6f4179f350a8d2bd558a977d6e41592b.png)
题2:现有“成绩表”记录了每个学生各科成绩,查找每个学生成绩最高的2个科目;
方法1:关联子查询;
按课程号分组取成绩最大值所在行的数据。
![c91f582f659ce76527a905e6aeed1a14.png](https://i-blog.csdnimg.cn/blog_migrate/841283947962a28dd055165fa06478b4.png)
使用分组(GROUP BY)和汇总函数得到每个组里成绩最大值,但是无法得到成绩最大值所在行的数据。
![ef435fed98e9d2b1e5b7587f3fae3e20.png](https://i-blog.csdnimg.cn/blog_migrate/b7caee3448fa6c10caa09f8686cd291b.png)
使用关联子查询来实现:
![1d7941c9c9c6a0424866d83f4b5728e9.png](https://i-blog.csdnimg.cn/blog_migrate/be480a6145ac9d8aec7ab687179a7b15.png)
方法2:窗口函数;
解题思路:
- 当问题中出现“每个”的时候,就要想到分组了。问题中查看每个学生最高成绩,即为按学生(学号)来分组;
- 将表按照学号分组后,成绩降序排列,排在最前面的2个就是“成绩最高的两个科目”;
- 分组后排序,不能减少原表的行数,使用窗口函数;
- 使用哪个专用窗口函数?为了不受并列的影响,使用row_number函数;
解题步骤:
步骤1:按学号分组(PARTITION BY 学号),成绩降序排序(ORDER BY 成绩 DESC);
![a5dd8d3a7dd6067ff5373679c8b6078c.png](https://i-blog.csdnimg.cn/blog_migrate/1ffb13ddb4e8b430b9ab469edeb173ef.png)
步骤2:进一步得到每个学生成绩最好的2个科目,即提取出“ranking”值小于等于2的数据,只需在步骤1的SQL语句中加入条件筛选WHERE就可以了;
![ec98406cefb0d29d14decfc6456ecefd.png](https://i-blog.csdnimg.cn/blog_migrate/eda60d1177e10234b8912c6e5bdf8e7d.jpeg)
原因:搞错了SQL的运行顺序,SQL运行顺序和书写顺序不一样!!!
SQL运行顺序:
1 先运行子查询;
2 每个查询语句中的运行顺序:1 -> 2 -> 3;
![ddcfffd51ed3514ad7748d02dcccfc8d.png](https://i-blog.csdnimg.cn/blog_migrate/4a39a361d31e67ee0a6ff3edf1ab6249.jpeg)
题中运行到“WHERE ranking<=2”时,SELECT子句还没有被执行,"ranking"列还没有出现,所以报错,怎么解决这个问题呢?
使用子查询,把第一步得到的查询结果作为一个新表。
![af5dc9558a94fbce13f2f0e6f556a583.png](https://i-blog.csdnimg.cn/blog_migrate/825fd6732815b574172238603e443e1f.png)
举一反三:
将WHERE子句中的条件修改为N,可以变为topN问题的万能模板,下次遇到直接嵌套就好。
-- topN问题SQL万能模板
SELECT *
FROM(
SELECT *,
row_number() over (PARTITION BY <要分组的列名>
ORDER BY <要排序的列名> DESC) AS ranking
FROM 表名) AS a
WHERE ranking<=N;
题3:累积求和问题;
表salary中记录了员工发放的薪水,包含雇员编号、薪水、起始日期和结束日期,其中当前员工指结束日期="9999-01-01"的员工。
业务问题:按照雇员编号升序排列,查找前N个当前员工薪水的累计和。
![b7fc3a11e9a10accd7ee55eaf437f507.png](https://i-blog.csdnimg.cn/blog_migrate/a5a545de6af45ffad49f07f40c63647d.png)
解题步骤:
步骤1:先筛选出当前员工(结束日期="9999-01-01")的薪水;
![860afb84120bde20fd324e417a1ffaba.png](https://i-blog.csdnimg.cn/blog_migrate/0cd650cb8082d6b61f68183d0193b31e.png)
步骤2:累计薪水(第一行的累计薪水为雇员编号01的薪水,第二行为雇员编号01和02薪水之和,以此类推);
![3667bbb995dd8569cb6ed080f72cbb22.png](https://i-blog.csdnimg.cn/blog_migrate/bf37b1b8a846fb59487144ddd2d77f8f.png)
对于“累计”问题,要想到用聚合函数作为窗口函数。
比如累计求和,用SUM();
SUM(列名) over (ORDER BY <用于排序的列名>)
累计平均,用AVG();
AVG(列名) over (ORDER BY <用于排序的列名>)
所以,可得出“累计求和”问题的万能模板是:
SELECT 列1, 列2,
SUM(列名) over (ORDER BY <用于排序的列名>) AS 累计值的别名
FROM 表名;
举例说明窗口函数的移动平均。
![073e4b84cf7345c5396dc9bd8ce3bbe3.png](https://i-blog.csdnimg.cn/blog_migrate/63cbbbee862889d4cb5b933575591b0d.jpeg)
想要计算当前行与前n行(共n+1)行的平均时,只要调整rows ... preceding中间的数字即可,通过preceding关键字调整作用范围,在以下场景中非常适用:在公司业绩名单排名中,可以通过移动平均,直观地查看到与相邻名次业绩的平均、求和等统计数据。