MySQL基础之开窗函数

1. 聚合函数和开窗函数的区别

普通聚合函数（如 SUM(), AVG()）是纵向聚合：一组数据聚合成一行然后拿到结果；

开窗函数是SQL中的一种横向分析函数，它不改变行数，而是为每一行计算一个基于该行所在分区中其他行的数据结果。

例如：“统计每位员工的工资在其所在部门中的排名”。关键词就是“每”，要求每一行数据都要展示，而且是基于其所在部门的排名。

也就是说：

• 每位员工都要保留在结果中（不能像普通聚合那样合并成一行）；
• 排名是相对于同一部门的其他员工；
• 需要考虑排序（工资越高，排名越靠前）；

2. 开窗函数基本组成

函数名(字段参数) OVER ([PARTITION BY 分组字段] # 分区子句，开窗函数将在不同的分区内分别执行[ORDER BY 排序字段] # 排序子句[ROWS BETWEEN start AND end] # 窗口子句，通常用来作为滑动窗口使用
)

对于上面的窗口子句：

• start的值一般为 1.unbounded preceding，边界是分区中的第一行 2.expr preceding，边界是当前行向上数expr行
• end的值一般为 1. unbounded following，边界是分区中最后一行 2.expr following，边界是当前行向下数expr行
• start和end也可以是current row，边界是当前行
• 默认为ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

比如：

# 窗口范围是分区中的第一行到当前行
rows unbounded preceding 
# 窗口范围是当前行、当前行的前一行、当前行的后一行，一共三行记录。
rows between 1 preceding and 1 following

边界图如果所示：

3. 聚合开窗函数

基础数据：

需求：统计每位员工从入职到现在，所在部门中总共发了多少钱工资。

SELECT *, 
sum(salary) over(PARTITION by part,username ORDER BY hire_date rows BETWEEN unbounded preceding and current row) as 'total_salary'
from employees

结果如下：

4. 排名开窗函数

统计每位员工在自己部门中的工资排名

4.1 RANK

特点：1. 相同值的行有相同排名 2. 跳过排名空行(看下面排名从1跳到了3)

SELECT *, RANK() over(PARTITION by part ORDER BY salary desc) as 'salary_rank'
from employees

4.2 DENSE_RANK

特点：1. 相同值的行有相同的排名 2. 不跳过排名空位(看下面排名1后面是2)

SELECT *, DENSE_RANK() over(PARTITION by part ORDER BY salary desc) as 'salary_rank'
from employees

4.3 ROW_NUMBER

特点：按原表数据的顺序，为每一行设置一个递增的数字

SELECT *, ROW_NUMBER() over(PARTITION by part ORDER BY salary desc) as 'salary_rank'
from employees

5. 取值开窗函数

5.1 FIRST_VALUE

需求：查看每位员工所在部门中工资最高的人的工资是多少（即第一名的工资）

SELECT *, FIRST_VALUE(salary) over(PARTITION by part ORDER BY salary desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as 'top_salary'
from employees

5.2 LAST_VALUE

需求：查看每位员工所在部门中工资低的人的工资是多少（即最后第一名的工资）

SELECT *, LAST_VALUE(salary) over(PARTITION by part ORDER BY salary desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as 'last_salary'
from employees

5.3 NTH_VALUE

需求：查看每位员工所在部门中工资排名第二的人的工资是多少

SELECT *, NTH_VALUE(salary, 2) over(PARTITION by part ORDER BY salary desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as 'nth_salary'
from employees

5.4 LAG

需求：查看每位员工所在部门中工资与上一个人的差值

SELECT *, salary - IFNULL(LAG(salary, 1) OVER (PARTITION BY part ORDER BY salary ASC), salary) AS diff_salary
FROM employees

5.5 LEAD

需求：判断每个员工工资是否高于下一位

SELECT *,CASEWHEN salary > LEAD(salary) OVER (PARTITION BY part) THEN '高于下一位'ELSE '不高于'END AS compare_salary
FROM employees