【MySQL8新特性】窗口函数

---

提出问题

1. 如何得到各个部门工资排名前N名员工列表？
2. 查找各部门每人工资栈部门总工资的百分比？
3. …

窗口函数概念

MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于查询中对数据进行分组，不同的是，分组操作会把分组结果聚合成一条记录，而窗口函数将结果置于每一条记录中

它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数

简单来说，窗口函数就是对于查询的每一行，都使用与该行相关的行进行计算

窗口函数使用场景

在需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好

窗口函数的分类

• 静态窗口函数，窗口大小固定，不会因为记录的不同而不同
• 动态窗口函数，窗口大小会随着记录的不同而变化，这种动态变化的窗口叫滑动窗口

窗口函数总体上可以分为：

• 序号函数
  • ROW_NUMBER()：顺序排序
  • RANK()：并列排序，会跳过重复的序号
  • DENSE_RANK()：并列排序，不会跳过重复的序号
  • PERCENT_RANK()
• 分布函数
  • PERCENT_RANK()
  • CUME_DIST()
• 前后函数
  • LAG(expr,n)
  • LEAD(expr,n)
• 首尾函数
  • FIRST_VALUE(expr)
  • LAST_VALUE(expr)
• 其他函数
  • NTH_VALUE(expr,n)
  • NTILE(n)

窗口函数的使用

窗口函数的语法

window_function_name(expression)
OVER (
	[partition_definition]
	[order_definition]
	[frame_definition]
)

• 窗口函数的一个概念是当前行，当前行属于某个窗口，由OVER关键字用来指定函数执行的窗口范围
  • 如果后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算
  • 如果后面括号内容不为空，则由三个参数来设置窗口
    • PARTITION BY子句
    • ORDER BY子句
    • FRAME子句
• PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行
• ORDER BY子句：按照指定字段进行排序，窗口函数将按照排序后的记录进行编号。可以和PARTITION BY配合使用，也可以单独使用
• FRAME子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用

实例

序号函数

准备数据

CREATE TABLE IF NOT EXISTS numbers(
num INT
);

INSERT INTO numbers
VALUES (1),(1),(2),(3),(5),(5),(5),(6),(9),(7),(7),(8);

ROW_NUMBER()

SELECT num, ROW_NUMBER() OVER(ORDER BY num)
FROM numbers;

RANK()

SELECT num, RANK() OVER(ORDER BY num)
FROM numbers;

DENSE_RANK()

SELECT num, DENSE_RANK() OVER(ORDER BY num)
FROM numbers;

PERCENT_RANK()

SELECT num, PERCENT_RANK() OVER(ORDER BY num)
FROM numbers;

前后函数

准备数据

TRUNCATE TABLE numbers;

INSERT INTO numbers
VALUES (1),(2),(3),(5),(6),(9),(7),(8);

SELECT * FROM numbers;

LAG(expr,n)

SELECT num, LAG(num,2) OVER(ORDER BY num)
FROM numbers;

LEAD(expr,n)

SELECT num, LEAD(num,2) OVER(ORDER BY num)
FROM numbers;

首尾函数

准备数据

CREATE TABLE IF NOT EXISTS table1(
f1 CHAR(1),
f2 INT
);

DESC table1;

INSERT INTO table1
VALUES ('b',4),('a',1),('c',10),('a',2),('b',3);

SELECT * FROM table1;

FIRST_VALUE(expr)

SELECT 
	f1,
	f2,
	FIRST_VALUE(f2) OVER(PARTITION BY f1)
FROM table1;

SELECT 
	f1,
	f2,
	FIRST_VALUE(f2) OVER(PARTITION BY f1 ORDER BY f2)
FROM table1;

LAST_VALUE()

SELECT 
	f1,
	f2,
	LAST_VALUE(f2) OVER(PARTITION BY f1 ORDER BY f2)
FROM table1;

其他函数

准备数据

CREATE TABLE IF NOT EXISTS numbers(
num INT
);

INSERT INTO numbers
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT * FROM numbers;

NTH_VALUE(expr,n)

SELECT 
	num,
	NTH_VALUE(num,7) OVER()
FROM numbers;

SELECT 
	num,
	NTH_VALUE(num,11) OVER()
FROM numbers;

SELECT 
	num,
	NTH_VALUE(num,0) OVER()
FROM numbers;

NTILE(n)

SELECT 
	num,
	NTILE(6) OVER()
FROM numbers;

小结

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用