MySQL 8.0窗口函数实战：排名计算与累计聚合查询场景详解

MySQL 8.0引入窗口函数（Window Functions），在不使用子查询和自连接的情况下实现排名、累计聚合、偏移比较等复杂分析查询。窗口函数是SQL查询优化的重要工具，相比传统GROUP BY聚合，窗口函数保留原始行数据的同时附加计算结果。本文覆盖窗口函数语法、排名函数、累计聚合、偏移函数的实战应用。

窗口函数语法结构与OVER子句详解

窗口函数的基本语法：

函数名(...) OVER (
    [PARTITION BY 分区字段]
    [ORDER BY 排序字段]
    [frame_clause 窗口帧定义]
) AS 列别名

各子句作用：

• PARTITION BY：将结果集按字段分区，函数在每个分区内独立计算
• ORDER BY：分区内排序，影响排名类函数的计算顺序
• 窗口帧（Frame）：定义函数计算的行范围，默认为分区内从首行到当前行

窗口帧语法：

-- ROWS模式：按物理行偏移
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 首行到当前行
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING          -- 前两行到后两行

-- RANGE模式：按逻辑值范围
RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW  -- 前一天到当前行

使用示例表结构：

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    region VARCHAR(20) NOT NULL,
    product VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL
);

INSERT INTO sales (sale_date, region, product, amount, quantity) VALUES
('2026-01-01', '华东', '笔记本电脑', 7999.00, 1),
('2026-01-01', '华东', '平板电脑', 2999.00, 2),
('2026-01-02', '华东', '笔记本电脑', 7999.00, 1),
('2026-01-01', '华北', '笔记本电脑', 7999.00, 1),
('2026-01-02', '华北', '平板电脑', 2999.00, 3),
('2026-01-03', '华北', '手机', 3999.00, 2);

ROW_NUMBER、RANK、DENSE_RANK排名函数应用

三个排名函数的区别在于对相同值的处理方式：

-- 各区域销售额排名
SELECT 
    sale_date,
    region,
    product,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rank
FROM sales;

结果对比：

+------------+--------+----------+--------+---------+----------+------------+
| sale_date  | region | product  | amount | row_num | rank_val | dense_rank |
+------------+--------+----------+--------+---------+----------+------------+
| 2026-01-01 | 华东   | 笔记本   | 7999   | 1       | 1        | 1          |
| 2026-01-02 | 华东   | 笔记本   | 7999   | 2       | 1        | 1          |
| 2026-01-01 | 华东   | 平板     | 2999   | 3       | 3        | 2          |
+------------+--------+----------+--------+---------+----------+------------+

• ROW_NUMBER()：连续序号，相同值也分配不同编号（1,2,3）
• RANK()：相同值相同排名，跳过后续序号（1,1,3）
• DENSE_RANK()：相同值相同排名，不跳过序号（1,1,2）

取每个区域销售额TOP 2的记录：

SELECT * FROM (
    SELECT 
        sale_date, region, product, amount,
        ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
) ranked
WHERE rn <= 2;

使用NTILE函数将数据等分为N组，用于分位数分析：

-- 将销售额分为4个等级
SELECT 
    region, product, amount,
    NTILE(4) OVER (PARTITION BY region ORDER BY amount DESC) AS quartile
FROM sales;

累计聚合与滑动窗口计算

SUM、AVG、COUNT等聚合函数配合OVER子句实现累计计算：

-- 各区域累计销售额
SELECT 
    sale_date,
    region,
    amount,
    SUM(amount) OVER (
        PARTITION BY region 
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM sales;

滑动窗口计算近3天平均销售额：

SELECT 
    sale_date,
    region,
    amount,
    AVG(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS avg_3days
FROM sales;

计算销售额占比：

-- 每条记录占区域总销售额的比例
SELECT 
    sale_date,
    region,
    product,
    amount,
    amount / SUM(amount) OVER (PARTITION BY region) * 100 AS pct_of_region
FROM sales;

-- 每条记录占全局总销售额的比例
SELECT 
    sale_date,
    region,
    amount,
    amount / SUM(amount) OVER () * 100 AS pct_of_total
FROM sales;

无ORDER BY的OVER()表示窗口覆盖整个分区或结果集，用于计算总计和占比。

LAG与LEAD偏移函数在环比分析中的应用

LAG和LEAD函数访问当前行前后偏移行的数据，实现环比、同比计算：

-- 日环比增长
SELECT 
    sale_date,
    region,
    amount AS today_amount,
    LAG(amount, 1) OVER (PARTITION BY region ORDER BY sale_date) AS prev_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY region ORDER BY sale_date) AS diff,
    ROUND(
        (amount - LAG(amount, 1) OVER (PARTITION BY region ORDER BY sale_date)) 
        / LAG(amount, 1) OVER (PARTITION BY region ORDER BY sale_date) * 100, 
        2
    ) AS growth_rate
FROM sales;

LEAD函数预览下一行数据：

-- 预览下一天的销售额
SELECT 
    sale_date,
    amount,
    LEAD(amount, 1) OVER (PARTITION BY region ORDER BY sale_date) AS next_day_amount,
    LEAD(sale_date, 1) OVER (PARTITION BY region ORDER BY sale_date) AS next_sale_date
FROM sales;

FIRST_VALUE和LAST_VALUE获取窗口边界值：

-- 区域内首日和末日的销售额
SELECT 
    sale_date,
    region,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_date) AS first_amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY region 
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_amount
FROM sales;

LAST_VALUE默认窗口帧为UNBOUNDED PRECEDING AND CURRENT ROW，需要显式扩展到UNBOUNDED FOLLOWING才能获取分区内最后一行。

窗口函数性能优化与执行计划分析

窗口函数的执行计划分析：

EXPLAIN SELECT 
    region, sale_date, amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY sale_date)
FROM sales;

优化建议：

• 为PARTITION BY和ORDER BY字段创建复合索引，避免全表排序
• 减少PARTITION BY分区数量，分区越多内存消耗越大
• 避免在窗口函数中对表达式进行计算，先在CTE中预处理
• 大量数据场景下使用WHERE条件过滤后再应用窗口函数

索引优化示例：

-- 为窗口函数查询创建覆盖索引
CREATE INDEX idx_region_date_amount ON sales(region, sale_date, amount);

-- 使用CTE预处理减少窗口函数计算量
WITH daily_sales AS (
    SELECT region, sale_date, SUM(amount) AS daily_total
    FROM sales
    GROUP BY region, sale_date
)
SELECT 
    region, sale_date, daily_total,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY daily_total DESC) AS rn
FROM daily_sales;

窗口函数相比传统子查询写法，SQL更简洁且执行计划通常更优。MySQL优化器对窗口函数有专门的优化策略，包括缓存分区数据、合并排序等，在百万级数据量下性能表现稳定。