MySQL（二）：开窗函数

开窗函数的本质是聚合运算，但比聚合函数更灵活；每条记录都会执行并返回一条结果记录，而聚合函数+group by的返回结果记录会减少。

书写格式：

聚合函数/专用窗口函数 over([partition by 分区字段] [order by 排序字段][滑动窗口])

备注：

1. 聚合函数用于求值，专用窗口函数用于排名，又叫序号函数；
2. partition by指定分区，order by指定排序，[滑动窗口]指定窗口范围；
3. 专用窗口函数条件下指定的滑动窗口不起作用；
4. 适用场景：专用窗口函数用于排名，聚合函数用于按特定分区累计加总、求移动均值等。

功能说明：

• 聚合函数+over()
  说明：无任何指定，默认计算所有行。

-- 查询所有员工的平均工资
select *,avg(sal) over() 平均工资 from emp;

• 聚合函数+over(partition by 分区字段)
  说明：仅指定分区，默认计算分区内的所有行。

-- 计算各部门的平均工资
select *,avg(sal) over(partition by deptno) 部门平均工资 from emp;

• 聚合函数+over(partition by 分区字段 order by 排序字段)
  说明：仅指定分区和排序，默认计算分区内的第一行到当前行。

-- 查询各部门按入职时间顺序的累计工资总和
select *,sum(sal) over(partition by deptno order by hiredate) 累计工资总和 from emp;

• 聚合函数+over(partition by 分区字段 order by 排序字段 滑动窗口)
  说明：指定分区、排序和滑动窗口，会计算分区中滑动窗口内的行。

-- 查询各部门员工工资按从高到低排序后的移动平均值
select *,avg(sal) over(partition by deptno order by sal desc rows between 1 preceding and 1 following) 移动平均值
from emp;

• 序号函数：rank()、dense_rank()、row_number()
  说明：同样可以指定分区partition by和排序order by
  rank：序号不连续有重复；
  dense_rank：序号连续有重复；
  row_number：序号连续无重复。

select *,row_number() over(partition by deptno order by sal desc) 排名1,
         dense_rank() over(partition by deptno order by sal desc) 排名2,
         rank() over(partition by deptno order by sal desc) 排名3
from emp;