当前位置：首页 > news >正文

Mysql杂志（九）

news 2025/9/5 15:23:44

窗口函数

窗口函数（Window Function）是MySQL 8.0+引入的一种高级分析函数，它能在不减少原始数据行数的情况下，对数据的特定"窗口"（子集）进行计算，并将结果作为新列添加到每一行。这个我相信大家基本上都没有使用过，因为太高级了不是所有的开发都看的明白的，主包公司就是尽量写简单的sql，不然其他同事维护起来就根本看不懂啦。

这个窗口函数的特点就是不会聚合数据，也就是不会像分组一样查询的行数减少。有点就是比自链接或者自查询的性能会更好。

函数名([参数]) OVER ([PARTITION BY <分区列>][ORDER BY <排序列> [ASC|DESC]][frame_clause]
)

这个就是窗口函数的语法结构，看起来好像不难哦，函数名这个就不难理解，就是各种函数嘛，我们先看看下面的表格。

OVER()	标识窗口函数，区别于普通聚合函数	必须	`SUM(salary) OVER()`
PARTITION BY	将数据分成多个分区/组，类似GROUP BY但不聚合	可选	`PARTITION BY department`
ORDER BY	指定分区内数据的排序方式，影响累计计算和排名	可选	`ORDER BY hire_date`
frame_clause	定义窗口的精确范围（默认行为因是否有ORDER BY而异）	可选	`ROWS BETWEEN 3 PRECEDING AND CURRENT ROW`

前几个其实很好理解，主要是这个frame_clause可能会不是很明白，主包先重点说清楚这个。

frame_clause（范围）

{ROWS | RANGE} BETWEEN 起点 AND 终点

边界词

关键词	含义
`UNBOUNDED PRECEDING`	分区/窗口的第一行
`n PRECEDING`	当前行前n行（ROWS）或值小n（RANGE）
`CURRENT ROW`	当前行
`n FOLLOWING`	当前行后n行（ROWS）或值大n（RANGE）
`UNBOUNDED FOLLOWING`	分区/窗口的最后一行

ROWS默认行为

场景	默认框架	说明
无ORDER BY	`RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	当前行的前所有行和当前行的后所有行（全行）
有ORDER BY	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	当前行的前所有行

这个ROWS关键词的意思就是按照物理的行数来确定范围，也就是我们查询出来的行数据是怎么排列的就根据这个排列去计算。

CREATE TABLE sales_data (id INT, sale_date DATE, amount DECIMAL(10,2));
INSERT INTO sales_data VALUES
(1, '2023-01-01', 100),  -- 第1行
(2, '2023-01-02', 150),  -- 第2行
(3, '2023-01-02', 200),  -- 第3行
(4, '2023-01-04', 50),   -- 第4行
(5, '2023-01-05', 120);  -- 第5行

SELECT id,sale_date,amount,SUM(amount) OVER(ORDER BY sale_date #可以加也可以不加ROWS BETWEEN 1 PRECEDING AND CURRENT ROW。#意思就是当前行和当前行的前一行) AS rows_sum
FROM sales_data;

d	sale_date	amount	rows_sum	说明
1	2023-01-01	100.00	100.00	当前行1，前一行null，所以就是当前行的amount加上null
2	2023-01-02	150.00	250.00	当前行2，前一行1，所以就是当前行的amount加上100
3	2023-01-02	200.00	350.00	当前行3，前一行2，所以就是当前行的amount加上150
4	2023-01-04	50.00	250.00	当前行4，前一行3，所以就是当前行的amount加上200
5	2023-01-05	120.00	170.00	当前行5，前一行4，所以就是当前行的amount加上50

从上面这里大家应该就是可以看明白啦吧，这个排序不一定要加的，看具体的业务需求，如果是全范围的话就直接应该OVER()就可以啦，是不是很好理解。

RANGE默认行为

这个在MySQL窗口函数中，RANGE框架必须与ORDER BY一起使用，这是SQL标准的规定，所以他的默认行为就是当前行以及当前行的前所有行，另外这个他是以字段的数值为基础的，和上面不同的是ROWS是以物理行为基础的。

SELECT id,sale_date,amount,SUM(amount) OVER(ORDER BY sale_dateRANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW#当前销售时间的前一天以及当前时间) AS range_sum
FROM sales_data;

id	sale_date	amount	range_sum	说明
1	2023-01-01	100.00	100.00	当前时间为1月1，那1月1的amount加上前一天的null
2	2023-01-02	150.00	450.00	当前时间为1月2，那1月2的amount加上前一天的100
3	2023-01-02	200.00	450.00	当前时间为1月2，那1月2的amount加上前一天的100
4	2023-01-04	50.00	50.00	当前时间为1月4，那14的amount加上前一天的null
5	2023-01-05	120.00	170.00	当前时间为1月5，那1月5的amount加上前一天的50

还是以上面的为例，我们看看RANGE的结果有什么不同，可以看到计算的行数就不一定了，可以是没有也可以是很多，因为只和列的数值有关系了，那我马上要使用排序也很好理解了，因为只有排序后才能再到前驱和后驱的数据，当然不止这一中控制计算时间的，计算其他的也可以的比如说：

SELECT id,sale_date,amount,SUM(amount) OVER(ORDER BY amountRANGE BETWEEN 50 PRECEDING AND CURRENT ROW#当前售价-50>=的售价<=当前售价) AS range_sum
FROM sales_data;

这个就是计算范围：[当前amount - 50, 当前amount]（即50-100元，假如当前是100元），还可以这样写：

SELECT id,sale_date,amount,SUM(amount) OVER(ORDER BY amountRANGE BETWEEN amount*0.1 PRECEDING AND CURRENT ROW#当前售价-（当前售价*0.1）>=的售价<=当前售价) AS range_sum
FROM sales_data;

这个就是计算范围：[当前amount - （amount*0.1）, 当前amount]（即90-100元，假如当前是100元）所以玩法是很多的。

区别

特性	ROWS框架	RANGE框架
计算基准	基于物理行位置	基于列值逻辑范围
相同值处理	每行独立处理	相同ORDER BY值视为同一"点"
边界确定	固定行数	动态值范围
NULL处理	NULL行被视为普通行	所有NULL被视为同一分组
典型用途	固定行数的移动计算	动态时间/数值窗口计算
性能	通常更快	可能较慢(需计算值范围)