《MySQL窗口函数完全指南：高效数据分析，排名/聚合/滑动计算一网打尽！》_mysql单个窗口函数如何进行对多列排序-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_64179111/article/details/147098944

为什么你需要掌握窗口函数？

“还在为复杂的分组排名、累计求和、前后对比疯狂写子查询？MySQL 8.0的窗口函数（Window Function）帮你告别繁琐代码，一条SQL搞定多维分析！本文从零到实战，带你解锁数据分析的利器！”

一、窗口函数的核心概念

1. 什么是窗口函数？

定义：窗口函数对数据的特定范围（窗口）进行实时计算，保留原始行数，适合动态分析。
核心优势：不合并数据行，直接生成排名、累计值、移动平均等结果。

2. 窗口函数能解决哪些问题？

排名问题：部门薪资TOP3、班级成绩排名。
动态计算：累计销售额、近3个月均值。
数据对比：环比/同比分析（如本月与上月销量差值）

二、窗口函数语法精讲

SELECT 
    窗口函数() OVER (
        PARTITION BY 分组列 
        ORDER BY 排序列 
        [ROWS/RANGE BETWEEN 范围]
    ) AS 别名 
FROM 表名;

关键子句解析：

PARTITION BY：按列分组（类似GROUP BY，但保留所有行）

ORDER BY：定义组内排序规则，影响排名和滑动窗口范围

ROWS/RANGE：

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：前2行到当前行

RANGE BETWEEN INTERVAL 1 DAY PRECEDING：按时间差值定义范围

三、5类常用窗口函数详解（附代码）

1. 排名函数

场景：成绩排名、部门薪资TOP3。

函数区别：

SELECT 
    name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,  -- 唯一序号：1,2,3
    RANK() OVER (ORDER BY score DESC) AS rank,           -- 并列跳号：1,1,3
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank -- 并列不跳号：1,1,2
FROM students;

输出示例：

name	score	row_num	rank	dense_rank
张三	100	1	1	1
李四	100	2	1	1
王五	90	3	3	2

2. 聚合函数

场景：计算累计值、移动平均。
代码示例：

-- 计算每个部门的累计薪资
SELECT 
    department, salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cum_salary
FROM employees;

3. 偏移函数

LAG()/LEAD()：取前/后N行的值

-- 比较本月与上月销量
SELECT 
    month, sales,
    LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales
FROM sales_table;

4. 分桶函数

NTILE(n)：将数据均分到n个桶，用于分位数分析

-- 将学生按成绩分为4个等级
SELECT 
    name, score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM students;

5. 头尾函数

FIRST_VALUE()/LAST_VALUE()：取窗口首尾值

四、实战案例：经典业务场景

案例1：部门薪资排名（RANK() + PARTITION BY）

SELECT 
    department, name, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

案例2：近3个月销售额均值（ROWS窗口）

SELECT 
    month, sales,
    AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM sales;

五、性能优化与避坑指南

索引优化：对PARTITION BY和ORDER BY的列建索引，加速计算

减少窗口大小：避免全表窗口（如UNBOUNDED PRECEDING），优先用ROWS限定范围

慎用嵌套窗口函数：拆分复杂操作为多步骤查询

版本要求：仅支持MySQL 8.0+，低版本需升级

六、总结：

常用窗口函数分类

类型	函数示例	用途
排名函数	`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`	分配唯一序号或排名（允许并列）
聚合函数	`SUM()`, `AVG()`, `COUNT()`	计算窗口内累计值、移动平均等
偏移函数	`LAG()`, `LEAD()`	访问前/后N行的值（如环比分析）
头尾函数	`FIRST_VALUE()`, `LAST_VALUE()`	取窗口首尾值
分桶函数	`NTILE(n)`	将数据均分到n个桶（如分位数分析