🧲相关文章
[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 开窗函数