[Database] MySQL 8.x Window / Partition Function (窗口/分区函数)

🧲相关文章

[1] MySQL 系统表解析以及各项指标查询
[2] MySQL 5.7+ JSON 字段的使用的处理
[3] MySQL经典练习50题

简介

MySQL 8.0+版本开始支持窗口函数 官方文档 在之前的版本中已存在的大部分聚合函数，在MySQL 8 中也可以作为窗口函数来使用

方法 / 步骤

Partition Function (分区函数)

Window Function (窗口函数)

函数分为两部分，一部分是函数名称，窗口函数的数量比较少，总共才11个窗口函数+聚合函数（所有的聚合函数都可以用作窗口函数）。根据函数的性质，有的需要写参数，有的不需要写参数。

• 窗口函数相关支持

函数名称	描述
CUME_DIST()	累积分配值
DENSE_RANK()	当前行在其分区中的排名，稠密排序
FIRST_VALUE()	指定区间范围内的第一行的值
LAG()	取排在当前行之前的值
LAST_VALUE()	指定区间范围内的最后一行的值
LEAD()	取排在当前行之后的值
NTH_VALUE()	指定区间范围内第N行的值
NTILE()	将数据分到 N 个桶，当前行所在的桶号
PERCENT_RANK()	排名值的百分比
RANK()	当前行在其分区中的排名，稀疏排序
ROW_NUMBER()	分区内当前行的行号

TopN相关函数
ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2

# 窗口函数语法 
func_name(<parameter>) 
OVER([PARTITION BY <part_by_condition>] 
[ORDER BY <order_by_list> ASC|DESC])

窗口函数与分组聚合函数比较相似，都是通过指定字段将数据分成多份，区别在于：

• SQL 标准允许将所有聚合函数用作窗口函数，用OVER 关键字区分开窗函数和聚合函数。
• 聚合函数每组只返回一个值，开窗函数每组可返回多个值。

在这11个窗口函数中，实际工作中用的最多的当属ROW_NUMBER()、RANK()、DENSE_RANK()这三个排序函数了。下面我们通过一个简单的数据集学习一下这三个开窗函数。

CREATE TABLE sales_log
( 
sale_date date COMMENT '销售时间', 
name char(2) COMMENT '销售员姓名', 
sales int COMMENT '销售额度'
) ENGINE=InnoDB COMMENT='销售记录';; INSERT INTO sales_log VALUES
('2021/1/1', '丁一', 100), 
('2021/2/1', '丁一', 310), 
('2021/2/1', '李四', 200), 
('2021/3/1', '李四', 210), 
('2021/2/1', '刘猛', 300), 
('2021/3/1', '刘猛', 310), 
('2021/1/1', '王二', 150), 
('2021/2/1', '王二', 180), 
('2021/3/1', '王二', 190), 
('2021/1/1', '张三', 250), 
('2021/2/1', '张三', 280), 
('2021/3/1', '张三', 290);
# 数据查询 
SELECT * FROM sales_log; 

# 对每月销售员业绩从好到坏进行排名
SELECT sale_date,name,sales, ROW_NUMBER() OVER(PARTITION BY sale_date ORDER BY sales DESC) as sales_order 
FROM sales_log;# 查询每月业绩最好的销售员
SELECT * FROM
( SELECT sale_date,name,sales, ROW_NUMBER() OVER(PARTITION BY sale_date ORDER BY sales DESC) as sales_order 
FROM sales_log ) AS t
WHERE sales_order = 1;

• 初始化数据

# 首先创建虚拟的用户登record_user_login录表，并插入数据 
create table record_user_login
( 
user_id bigint(20)  COMMENT '用户ID', 
login_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间'
); # 初始化数据 
insert into record_user_login values
(1,'2025-11-25 13:21:12'), 
(1,'2025-11-24 13:15:22'), 
(1,'2025-11-24 10:30:15'), 
(1,'2025-11-24 09:18:27'), 
(1,'2025-11-23 07:43:54'), 
(1,'2025-11-10 09:48:36'), 
(1,'2025-11-09 03:30:22'), 
(1,'2025-11-01 15:28:29'), 
(1,'2025-10-31 09:37:45'), 
(2,'2025-11-25 13:54:40'), 
(2,'2025-11-24 13:22:32'), 
(2,'2025-11-23 10:55:52'), 
(2,'2025-11-22 06:30:09'), 
(2,'2025-11-21 08:33:15'), 
(2,'2025-11-20 05:38:18'), 
(2,'2025-11-19 09:21:42'), 
(2,'2025-11-02 00:19:38'), 
(2,'2025-11-01 09:03:11'), 
(2,'2025-10-31 07:44:55'), 
(2,'2025-10-30 08:56:33'), 
(2,'2025-10-29 09:30:28'); # 查看数据 
SELECT * FROM record_user_login;

参考资料 & 致谢

[1] MySQL 8.0 新特性
[2] MySQL 开窗函数