MySQL 8.0窗口函数实战:排名统计与移动聚合查询优化

MySQL窗口函数的核心语法结构

MySQL 8.0引入窗口函数(Window Functions)后,排名、累计聚合、移动平均等复杂统计查询不再依赖自连接和用户变量hack。窗口函数的语法结构为:函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列窗口帧),其中PARTITION BY和窗口帧子句可选。理解窗口帧(Window Frame)是掌握窗口函数的关键,它定义了当前行参与计算的数据范围。

排名函数ROW_NUMBER/RANK/DENSE_RANK的差异化应用

三种排名函数在处理并列值时行为不同,选择错误的函数会导致业务逻辑偏差:

-- 按销售额排名,相同金额不同处理方式SELECT    seller_id,    sales_amount,    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num,    RANK() OVER (ORDER BY sales_amount DESC) AS rank_val,    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank_valFROM seller_sales;

假设销售额为[100,100,90,80],三种函数返回结果:ROW_NUMBER为[1,2,3,4](严格递增不处理并列);RANK为[1,1,3,4](并列后跳号);DENSE_RANK为[1,1,2,3](并列不跳号)。

业务场景选型:取Top N卖家时用DENSE_RANK(并列卖家都应入选);分页查询用ROW_NUMBER(保证每页行数固定);竞赛排名用RANK(符合竞赛排名惯例)。

分组排名取每组Top N:

-- 每个品类销量前3的商品SELECT * FROM (    SELECT        category,        product_name,        sales,        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn    FROM products) rankedWHERE rn <= 3;

该查询使用PARTITION BY按品类分组,ORDER BY销量降序排列,ROW_NUMBER为每组内编号,外层WHERE过滤取前3名。相比传统GROUP_CONCAT+SUBSTRING_INDEX方案,窗口函数写法简洁且性能更优。

累计聚合与移动平均的窗口帧控制

窗口帧子句控制聚合范围,默认帧范围为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(从首行到当前行的累计值)。自定义帧范围实现移动平均:

-- 7日移动平均销售额SELECT    sale_date,    daily_sales,    AVG(daily_sales) OVER (        ORDER BY sale_date        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW    ) AS moving_avg_7dFROM daily_sales_summary;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW定义了包含当前行在内的7行窗口。与RANGE帧不同,ROWS帧按物理行偏移计数,适合时间序列场景;RANGE帧按逻辑值偏移,适合处理重复排序值。

累计求和与分组累计:

-- 按月分组的累计销售额SELECT    sale_month,    seller_id,    monthly_sales,    SUM(monthly_sales) OVER (        PARTITION BY seller_id        ORDER BY sale_month        ROWS UNBOUNDED PRECEDING    ) AS cumulative_salesFROM monthly_sales;

PARTITION BY seller_id将窗口限定在单个卖家范围内,SUM对每个卖家独立累计。ROWS UNBOUNDED PRECEDING等价于默认帧范围,从首行累加到当前行。

窗口函数性能优化策略

窗口函数的执行计划特征:MySQL对每个窗口函数声明执行一次排序(Sort)操作,多个窗口函数可能触发多次排序。优化器会合并排序键相同的窗口函数,但排序键不同的窗口函数无法合并。

性能优化要点:

减少窗口函数数量:将排序键相同的窗口函数合并到同一个OVER子句中,避免重复排序:

-- 错误写法:两次排序SELECT    SUM(sales) OVER (ORDER BY sale_date) AS cum_sales,    AVG(sales) OVER (ORDER BY sale_date ROWS 6 PRECEDING) AS moving_avgFROM t;-- 优化:两个窗口帧不同,无法合并,但可以改写为单次扫描-- MySQL 8.0.28+已自动优化同排序键窗口合并

覆盖索引加速排序:窗口函数的ORDER BY列如果建立了索引,MySQL可以利用索引有序性避免显式排序。对高频窗口查询创建覆盖索引:

ALTER TABLE daily_sales_summary ADD INDEX idx_sale_date (sale_date, daily_sales);

分区裁剪:PARTITION BY列如果与查询的WHERE条件匹配,MySQL可以跳过无关分区。但窗口函数的分区裁剪不如GROUP BY高效,大量分区时仍建议在子查询中先过滤再计算窗口函数。

替代方案对比:当窗口函数计算量过大(如百万级行的累计聚合),考虑使用物化视图或应用层计算。对于实时性要求不高的统计报表,定时将窗口函数结果写入汇总表,查询汇总表即可,避免每次在线计算。

LAG/LEAD函数实现环比增长计算

LAG和LEAD函数分别访问前N行和后N行的值,是计算环比、同比的核心工具:

-- 月度环比增长率SELECT    sale_month,    monthly_sales,    LAG(monthly_sales, 1) OVER (ORDER BY sale_month) AS prev_month,    ROUND(        (monthly_sales - LAG(monthly_sales, 1) OVER (ORDER BY sale_month))        / LAG(monthly_sales, 1) OVER (ORDER BY sale_month) * 100,        2    ) AS growth_rate_pctFROM monthly_sales;

LAG(monthly_sales, 1)获取前一行的月销售额,计算当月与前月的增长比例。当LAG访问超出窗口范围时返回NULL,业务层需做NULL处理,可用COALESCE(LAG(monthly_sales, 1), monthly_sales)提供默认值。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql80-chuang-kou-han-shu-shi-zhan-pai-ming-tong-ji-yu-yi/

窗口函数解决了什么问题

MySQL 8.0之前，分组排名、累计求和、移动平均这类需求需要用子查询、用户变量或应用层计算实现，代码复杂且执行效率低。窗口函数（Window Functions）在SQL层面直接完成这类计算，语法简洁且执行计划友好。

窗口函数与GROUP BY的本质区别：GROUP BY将多行合并为一行输出，窗口函数保留每一行，同时附上聚合计算结果。

ROW_NUMBER排名与去重查询

ROW_NUMBER为每行分配唯一序号，常用于分组取Top N和去重：

-- 每个部门薪资最高的3名员工
SELECT * FROM (
    SELECT
        dept_id,
        emp_name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;

ROW_NUMBER、RANK、DENSE_RANK的区别：

1. ROW_NUMBER：严格递增，不处理并列

2. RANK：并列同名，跳号（1,2,2,4）

3. DENSE_RANK：并列同名，不跳号（1,2,2,3）

去重场景：保留每组最新一条记录

DELETE t1 FROM user_actions t1
JOIN (
    SELECT id,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY action_time DESC
        ) AS rn
    FROM user_actions
) t2 ON t1.id = t2.id AND t2.rn > 1;

累计聚合与移动窗口计算

SUM和AVG配合窗口定义实现累计和移动计算：

-- 每日销售额累计
SELECT
    order_date,
    daily_amount,
    SUM(daily_amount) OVER (
        ORDER BY order_date
        ROWS UNBOUNDED PRECEDING
    ) AS cumulative_amount
FROM daily_sales;

-- 7日移动平均
SELECT
    order_date,
    daily_amount,
    AVG(daily_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

ROWS与RANGE的区别：ROWS按物理行偏移计算，RANGE按逻辑值范围计算。对于连续日期数据两者等价，但存在日期缺口时RANGE会跨越缺口计算，ROWS则严格按行位置。

LAG和LEAD实现环比同比

环比和同比计算需要访问前N行的值：

SELECT
    order_date,
    daily_amount,
    -- 环比：与前一天比较
    LAG(daily_amount, 1) OVER (ORDER BY order_date) AS prev_day,
    daily_amount - LAG(daily_amount, 1) OVER (ORDER BY order_date) AS day_diff,
    -- 同比：与上月同日比较
    LAG(daily_amount, 30) OVER (ORDER BY order_date) AS prev_month_same_day,
    ROUND(
        (daily_amount - LAG(daily_amount, 30) OVER (ORDER BY order_date))
        / LAG(daily_amount, 30) OVER (ORDER BY order_date) * 100, 2
    ) AS yoy_pct
FROM daily_sales;

LAG偏移量超出窗口范围时返回NULL，用COALESCE设置默认值：

COALESCE(LAG(daily_amount, 1) OVER (ORDER BY order_date), 0)

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

窗口函数的执行分三步：排序（Sort）、分区（Partition）、窗口计算（Frame Calculation）。EXPLAIN中显示为Window aggregate节点。

性能优化要点：

1. 减少分区数量：PARTITION BY的列选择性不宜过高，否则分区数量爆炸

2. 利用索引避免排序：ORDER BY子句与索引列一致时，MySQL可跳过排序步骤

-- 创建复合索引避免排序
CREATE INDEX idx_sales_date_dept ON daily_sales(dept_id, order_date);

-- 利用该索引的窗口查询
SELECT
    dept_id,
    order_date,
    daily_amount,
    SUM(daily_amount) OVER (
        PARTITION BY dept_id
        ORDER BY order_date
        ROWS UNBOUNDED PRECEDING
    ) AS cumulative
FROM daily_sales;

3. 避免在单个查询中使用多个不同排序的窗口函数，每个不同排序都会触发一次额外排序

-- 差：两个窗口排序不同，触发两次排序
SELECT
    ROW_NUMBER() OVER (ORDER BY salary DESC),
    ROW_NUMBER() OVER (ORDER BY hire_date)
FROM employees;

-- 好：拆分查询或调整业务逻辑统一排序

常见陷阱

1. 窗口函数不能直接在WHERE子句中过滤——必须嵌套一层子查询

2. 窗口函数结果不要用于JOIN条件，性能极差

3. 大数据量场景（千万行以上）的移动窗口计算内存消耗大，考虑分段查询或预聚合

4. NTILE函数用于等分数据桶，做分位数分析比NTILE更推荐用PERCENT_RANK或CUME_DIST