Mysql窗口函数

最新推荐文章于 2025-11-24 15:00:59 发布

原创最新推荐文章于 2025-11-24 15:00:59 发布 · 1k 阅读

27 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #窗口函数

在 MySQL 8 中，窗口函数是一个强大的特性，可以让你对数据进行灵活的分析和计算，同时保留每一行的原始数据。下面我将为你详细梳理 MySQL 8 窗口函数的使用方法。

⛓️ 窗口函数核心语法

窗口函数的基本语法结构如下：

sql

<窗口函数> OVER (
    [PARTITION BY <列名>] 
    [ORDER BY <列名>]
    [ROWS BETWEEN <起始点> AND <结束点>]
)

主要组成部分解析：

语法部分	说明
窗口函数	执行的函数，例如 `ROW_NUMBER()`, `SUM()`, `LAG()` 等。
PARTITION BY	类似于 `GROUP BY`，将数据按指定字段分成不同的"窗口"或组，每个窗口的计算独立进行。此部分可选。
ORDER BY	定义窗口内数据的排序方式。此部分可选，但对于排名类函数通常是必需的。
ROWS BETWEEN	控制窗口的计算范围（行数范围）。此部分可选，如果省略，默认范围通常是从分区起始行到当前行。

📚 窗口函数分类与用途

MySQL 8 支持多种窗口函数，主要可以分为以下几类：

函数类别	代表函数	主要用途
排名函数	`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `NTILE(n)`	为行分配序号、排名或进行分桶。
聚合函数	`SUM()`, `AVG()`, `COUNT()`, `MAX()/MIN()`	在窗口内执行聚合计算，如累计求和、移动平均等。
偏移函数	`LAG()`, `LEAD()`, `FIRST_VALUE()`, `LAST_VALUE()`	获取当前行之前或之后的行中的值。

🧪 常用窗口函数详解与示例

1. 排名函数

它们在为数据行分配序号时，处理"并列"情况的方式不同：

ROW_NUMBER()：连续不重复的排序，即使值相同也会分配不同的序号（例如：1, 2, 3, 4）。
RANK()：跳跃排序。值相同时排名相同，但下一个排名会跳过重复的位数（例如：1, 1, 3）。
DENSE_RANK()：连续排序。值相同时排名相同，但下一个排名连续不跳跃（例如：1, 1, 2）。

示例：部门内员工薪资排名

sql

SELECT 
    department, 
    name, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

假设数据如下，查询结果将是：

department	name	salary	row_num	rank	dense_rank
技术部	张三	10000	1	1	1
技术部	李四	10000	2	1	1
技术部	王五	9000	3	3	2

NTILE(n)函数用于将分区内的数据平均切分成n个桶（如果不均等，则第一组会有更多的数据）。

sql

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

2. 聚合函数

聚合函数搭配窗口函数使用，可以实现累计、移动等复杂的聚合计算。

示例1：计算部门累计薪资

sql

SELECT 
    department, 
    name, 
    salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees;

示例2：计算股价的3日移动平均

sql

SELECT 
    date, 
    price,
    AVG(price) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM stock_prices;

这里 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了窗口范围是从当前行及前两行，共三行数据。

3. 偏移函数

这类函数非常适合进行环比、同比分析。

LAG(column, n)：获取当前行之前第n行的值。
LEAD(column, n)：获取当前行之后第n行的值。
FIRST_VALUE(column) / LAST_VALUE(column)：获取窗口内第一行/最后一行的值。使用 LAST_VALUE 时，通常需要指定完整的窗口框架 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 以避免默认框架导致的问题。

示例：计算销售额月度环比

sql

SELECT 
    year, 
    month,
    sales,
    sales - LAG(sales, 1) OVER (ORDER BY year, month) AS growth
FROM monthly_sales;

🛠️ 窗口函数实战场景

获取每个部门薪资最高的前3名员工

sql

SELECT * 
FROM (
    SELECT 
        dept, 
        name, 
        salary, 
        ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn 
    FROM employees
) t 
WHERE rn <= 3;

计算每个员工的薪资与部门平均薪资的差距

sql

SELECT 
    name, 
    dept, 
    salary,
    salary - AVG(salary) OVER (PARTITION BY dept) AS diff_from_avg
FROM employees;

计算累计销售额

sql

SELECT 
    sales_date, 
    amount,
    SUM(amount) OVER (ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

⚙️ 性能优化与注意事项

索引是性能关键：为 PARTITION BY 和 ORDER BY 子句中使用的字段建立索引可以显著提升窗口函数的执行效率。
避免冗余计算：如果多个窗口函数使用相同的窗口定义，可以利用 WINDOW 子句为其命名，实现复用。

sql
```
SELECT 
    val,
    ROW_NUMBER() OVER w AS 'row_number',
    RANK() OVER w AS 'rank'
FROM numbers
WINDOW w AS (ORDER BY val);
```
OVER() 不能用于 WHERE 子句：如果需要对窗口函数的结果进行过滤，必须使用子查询或公用表表达式（CTE）。
注意 LAST_VALUE 的陷阱：LAST_VALUE 的默认窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这通常会导致返回当前行而非最后一行。正确的用法是显式指定框架：

sql
```
SELECT DISTINCT 
    r1,
    LAST_VALUE(r2) OVER (
        PARTITION BY r1 
        ORDER BY r2 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS 'last_r2'
FROM t1;
```
MySQL版本要求：窗口功能仅在 MySQL 8.0 及以上版本被支持。

💎 窗口函数 vs. 聚合函数 + GROUP BY

理解它们之间的区别有助于你做出正确的选择：

对比点	窗口函数	聚合函数 + GROUP BY
返回行数	✅ 保留原始所有行	❌ 将多行聚合成一行
能否结合原始列	✅ 可以，每行数据都保留，并附加计算结果	❌ 不可以，SELECT中非聚合列必须出现在GROUP BY中
典型场景	排名、累计、移动平均、前后行对比	求和、计数、求平均等基础汇总统计