MySQL 中的窗口函数

窗口函数是 MySQL 8.0 引入的一个重要特性它可以在不改变数据行数的前提下对每一行数据进行聚合、排名、累积等计算。与GROUP BY不同窗口函数会保留所有原始行。一、窗口函数窗口函数的执行逻辑是在每一行数据上基于“窗口”一个定义好的数据范围进行计算然后把计算结果的附加列如排名、累计值等直接写到对应的行上不合并行。语法结构SELECT 列名, 窗口函数() OVER ( PARTITION BY 分区列 -- 分组 ORDER BY 排序列 -- 排序 ROWS/RANGE BETWEEN ... -- 窗口范围 ) AS 别名 FROM 表名;二、窗口函数的三大类别1. 排名函数函数说明ROW_NUMBER()按顺序编号不处理并列1,2,3,4...RANK()跳跃排名并列后跳过1,1,3,4...DENSE_RANK()连续排名并列后不跳过1,1,2,3...NTILE(n)将数据分成 n 组返回组号示例按成绩排名-- 创建示例表 CREATE TABLE students ( id INT, name VARCHAR(50), score INT, class VARCHAR(10) ); INSERT INTO students VALUES (1, Alice, 95, A), (2, Bob, 85, A), (3, Charlie, 95, A), (4, David, 78, B), (5, Eva, 92, B), (6, Frank, 85, B); -- 各排名函数的对比 SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank, NTILE(3) OVER (ORDER BY score DESC) AS ntile FROM students;结果-------------------------------------------------- | name | score | row_num | rank | dense_rank | ntile | -------------------------------------------------- | Alice | 95 | 1 | 1 | 1 | 1 | | Charlie | 95 | 2 | 1 | 1 | 1 | | Eva | 92 | 3 | 3 | 2 | 2 | | Bob | 85 | 4 | 4 | 3 | 2 | | Frank | 85 | 5 | 4 | 3 | 3 | | David | 78 | 6 | 6 | 4 | 3 | --------------------------------------------------区别ROW_NUMBER()1,2,3,4,5,6RANK()1,1,3,4,4,6DENSE_RANK()1,1,2,3,3,4NTILE(3)1,1,2,2,3,32. 聚合函数作为窗口函数函数说明SUM()窗口内的累计和AVG()窗口内的平均值COUNT()窗口内的行数MAX()/MIN()窗口内的最大值/最小值示例累计求和与移动平均SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS cumulative_sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3 FROM sales_data;3. 取值函数函数说明LAG()获取当前行之前的第 N 行LEAD()获取当前行之后的第 N 行FIRST_VALUE()窗口内的第一行LAST_VALUE()窗口内的最后一行示例计算环比增长率SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue, ROUND( (revenue - LAG(revenue, 1) OVER (ORDER BY month)) / LAG(revenue, 1) OVER (ORDER BY month) * 100, 2 ) AS growth_rate_percent FROM monthly_revenue;三、PARTITION BY分组计算PARTITION BY将数据分组窗口函数在每个分组内独立计算。SELECT class, name, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class FROM students;结果-------------------------------------- | class | name | score | rank_in_class | -------------------------------------- | A | Alice | 95 | 1 | | A | Charlie | 95 | 1 | | A | Bob | 85 | 3 | | B | Eva | 92 | 1 | | B | Frank | 85 | 2 | | B | David | 78 | 3 | --------------------------------------四、窗口范围Frame窗口范围决定计算时包含哪些行写法说明ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从开始到当前行默认累计ROWS BETWEEN 2 PRECEDING AND CURRENT ROW当前行及前 2 行ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING前 1 行 当前行 后 1 行ROWS UNBOUNDED PRECEDING从开始到当前行的简写RANGE BETWEEN ...基于值的范围而不是行数SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS cumulative, -- 默认从开始到当前 SUM(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_3_days, SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_all FROM sales_data;五、窗口函数 vs GROUP BY对比维度GROUP BY窗口函数OVER()行数变化合并行行数减少保持原行数结果位置单独的结果行附加在原行旁边子查询需求常需要子查询不需要适用场景汇总统计排名、累计、移动平均-- GROUP BY只能看到汇总结果 SELECT department, AVG(salary) FROM employees GROUP BY department; -- 窗口函数每行都保留同时显示平均工资 SELECT employee_id, name, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary FROM employees;