定义

窗口函数,又叫分析函数,是用来在保留明细行的同时, 对当前行所在的一组行 执行聚合, 排序, 排名, 前后访问等运算的一类函数

与sum avg count等聚合函数的区别是

  • 普通聚合: 遇到group by, 会把一组多行折叠成一行, 行数减少
  • 窗口函数: 不改变行数, 只是在每一行上添加一个附加值 (改行在其窗口中的聚合或排序结果)

所有窗口函数的调用都形如

<窗口函数>(<参数>) 
OVER (
  [PARTITION BY 分区列,...]   -- 按哪些列把数据分成若干分区（可选）
  [ORDER BY 排序列,…]        -- 在分区内按什么顺序（可选，但许多函数需要）
  [ROWS|RANGE …]             -- 窗口范围：当前行前后覆盖多少行（可选）
)

常见的窗口函数

函数 功能说明
ROW_NUMBER() OVER (…) 在分区内给每一行按指定顺序编号，序号不并列，从 1 开始
RANK() OVER (…) 在分区内给每一行排名，遇到并列会跳号（如并列两名并列第 1，下一个是第 3）
DENSE_RANK() OVER (…) 在分区内给每一行排名，遇到并列不跳号（如并列两名并列第 1，下一个仍是第 2）
NTILE(n) OVER (…) 将分区内行平均分成 n 份，给每行分配一个桶号（1 到 n）
LAG(col, k) OVER (…) 取当前行前第 k 行的 col 值（可用于环比、差分）
LEAD(col, k) OVER (…) 取当前行后第 k 行的 col 值
FIRST_VALUE(col) OVER (…) 返回窗口中按照排序后第一行的 col 值
LAST_VALUE(col) OVER (…) 返回窗口中按照排序后最后一行的 col 值
SUM(col) OVER (…) 在窗口中对 col 求和
AVG(col) OVER (…) 在窗口中对 col 求平均
COUNT(col) OVER (…) 在窗口中对 col 求计数

测试

准备一下演示用到的数据

ROW_NUMBER()

select
	id, name, departmentId, salary,
	row_number() over (
		partition by departmentId
		order by salary desc
	) as row_num
from employees;

结果, 分区内按工资降序给行编号

RANK()

select
	id, name, departmentId, salary,
	rank() over (
		partition by departmentId
		order by salary desc
	) as rnk
from employees;

并列时跳号

DENSE_RANK()

并列时不跳号

对比一下

NTILE(n)

将分区内行平均分成n份

select
	id, name, departmentId, salary,
	ntile(3) over (
		partition by departmentId
		order by salary desc
	) as tile
from employees;

LAG(col, 1)

拿到前一行的列值

select
	id, name, departmentId, salary,
	lag(salary,1) over (
		partition by departmentId
		order by salary desc
	) as tile
from employees;

LEAD(col, 1)

拿到后一行的列值

SUM AVG COUNT

分区进行聚合操作, 保留明细行, 不会减少行数

FIRST_VALUE

分区内按照排序拿到第一行的数据

select
	id, name, departmentId, salary,
	first_value(salary) over (
		partition by departmentId
		order by salary desc
	) as top_salary
from employees;

LAST_VALUE

分区内拿到最后一行的数据

select
	id, name, departmentId, salary,
	last_value(salary) over (
		partition by departmentId
		order by salary desc
		rows between unbounded preceding and unbounded following
	) as last_salary
from employees;

ps:MySQL 默认把窗口范围当作 UNBOUNDED PRECEDING ... CURRENT ROW, 即从分区开头到当前行

写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 的时候,窗口变成从分区开头到分区末尾