个人理解:窗口函数,类似站在上帝视角找出表格中影藏的有用信息,而我们可以很方便地将其用在子查询中(当做新增字段),并用在外表当做过滤条件(一般是用在WHERE中)。
1. 理论
概念:窗口函数又称为OLAP函数(PLAP
= Oline Analytical Processing)实时分析处理函数,用于实现排序、生成序号等一般的聚合函数无法实现的高级操作。
不需要了解这么多理论,只需要知道题目中有排序相关问题就可以使用窗口函数了。
基本型:
聚合函数/(专用)窗口函数 OVER([PARTITION BY column_name ] [ORDER BY column_name])
也就是说,聚合函数也可以作为窗口函数
窗口函数包括两个部分:
-
聚合函数
或者(专用)窗口函数
,决定了窗口函数是聚合窗口函数还是排序窗口函数
聚合函数:COUNT()、MAX()/MIN()、AVG()、SUM()
(专用)窗口函数:RANK()、DENSE_RANK()、ROW_NUMBER()
-
OVER() 函数
的中的参数,若使用PARTITION BY
,表示窗口函数进行分组,没有则表示整张表所有行为一整个分区;若使用ORDER BY
,表示进行排序+累计计算
其中,PARTITION BY
类似 GROUP BY,后面接分区对象(分区≠分组,分区不进行去重);这里的ORDER BY
与全局 ORDER BY 类似,后面接排序对象,区别是这里表示组内排序,而且对聚合窗口函数来说,它还表示“累计”的意思,即其计算区间为首行至当前行。(后面会详说)
两点tips:
- 聚合函数也可以作为窗口函数
- 对这个
OVER函数
的理解至关重要
2. 用法
直接上例子。
(1)聚合窗口函数-SUM()
以不同部门的员工工资为例。
因为是聚合函数,其收OVER()中两个参数的共同影响,因此有四种可能:
(2)聚合窗口函数-COUNT()
注意到,对于聚合函数来说,其最简形式就是:聚合函数(col_name) OVER()
。注意,聚合函数窗口函数是有传参的!
其他聚合函数就不再赘述,一样的用法。
(3)窗口函数-ROW_NUMBER()
以不同班级的学生分数为例
由于窗口函数必须包含 ORDER BY 进行排序,因此只有两种情况
因此,对于窗口函数来说,其最简形式就是:窗口函数() OVER(ORDER BY col_name)
。注意窗口函数中没有传参的。
在这里,还必须对三个排序窗口函数的用法进行区分:
你学会了吗?
3. 经典题目
- 表格描述
Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name , 工资 Salary 和部门编号 DepartmentId 。
Department 表包含公司所有部门的信息。
- 题目描述
编写一个SQL查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:
- 题解
SELECT m.Name Department, m.Name Employee, m.Salary
FROM
(
SELECT e*, d.Name,
RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) rk_salary
FROM Employee e
JOIN Department d
ON e.DepartmentId = d.Id
) m
WHERE rk_salary <= 3
更多
这里只对最常见聚合函数和三种排序窗口函数进行了介绍,还有更多窗口函数有机会再更新。