MySQL 8.0窗口函数实战与查询优化技巧详解

MySQL 8.0窗口函数解决了哪些查询难题

MySQL 8.0引入窗口函数（Window Functions），解决了传统SQL中排名、累计求和、前后行比较等复杂分析查询需要依赖子查询、临时表或应用层计算的问题。窗口函数在保留原始行的基础上，对每组数据执行聚合计算，输出结果与输入行一一对应，不像GROUP BY会折叠行。

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

函数名() OVER (
    [PARTITION BY 分组列]
    [ORDER BY 排序列]
    [frame_clause 帧范围]
)

排名函数对比与使用场景

MySQL 8.0提供三种排名函数，行为差异决定适用场景：

-- ROW_NUMBER：严格连续编号，相同值不同排名
-- RANK：相同值同排名，后续跳号（1,1,3）
-- DENSE_RANK：相同值同排名，后续不跳号（1,1,2）

SELECT
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_val
FROM employees;

实际应用场景：

取每个部门薪资前三名：

SELECT * FROM (
    SELECT
        e.*,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees e
) ranked
WHERE rnk <= 3;

去重取最新记录（替代GROUP BY + 自连接）：

SELECT * FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
    FROM user_actions t
) latest
WHERE rn = 1;

聚合窗口函数与累计计算

聚合函数（SUM、AVG、COUNT、MAX、MIN）配合OVER子句实现累计统计：

-- 累计订单金额
SELECT
    order_date,
    daily_amount,
    SUM(daily_amount) OVER (ORDER BY order_date) AS cumulative_amount
FROM (
    SELECT
        DATE(order_time) AS order_date,
        SUM(amount) AS daily_amount
    FROM orders
    GROUP BY DATE(order_time)
) daily;

移动平均计算（指定帧范围）：

-- 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_orders;

分组占比计算：

-- 各部门薪资占公司总薪资比例
SELECT
    department_id,
    SUM(salary) AS dept_total,
    SUM(salary) / SUM(SUM(salary)) OVER () AS pct_of_company
FROM employees
GROUP BY department_id;

LEAD/LAG偏移函数与同比环比分析

LEAD和LAG函数访问前后行的值，无需自连接：

-- 月度环比增长
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / LAG(revenue, 1) OVER (ORDER BY month) * 100, 2
    ) AS mom_pct
FROM monthly_revenue;

-- 年度同比增长（LAG偏移12行）
SELECT
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
    ROUND(
        (revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / LAG(revenue, 12) OVER (ORDER BY month) * 100, 2
    ) AS yoy_pct
FROM monthly_revenue;

首日末日值对比：

SELECT
    user_id,
    FIRST_VALUE(balance) OVER (PARTITION BY user_id ORDER BY tx_date) AS first_balance,
    LAST_VALUE(balance) OVER (
        PARTITION BY user_id ORDER BY tx_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_balance
FROM user_balances;

窗口函数性能优化要点

窗口函数的性能取决于排序和分组操作。MySQL执行窗口函数时，如果PARTITION BY和ORDER BY的列有索引覆盖，可以避免filesort：

-- 为窗口函数创建覆盖索引
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary DESC);

-- 验证执行计划是否使用了索引排序
EXPLAIN SELECT
    *,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees;
-- 期望看到 Using index for group/filesort

避免在窗口函数的ORDER BY中使用表达式或函数，这会阻止索引使用：

-- 差：表达式阻止索引
RANK() OVER (ORDER BY YEAR(create_time), MONTH(create_time))

-- 好：直接排序走索引
RANK() OVER (ORDER BY create_time)

多窗口函数优化——如果多个窗口函数的PARTITION BY和ORDER BY相同，MySQL只做一次排序：

-- 一次排序，三个窗口函数共享
SELECT
    department_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS cumulative,
    AVG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_avg,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees;

大表场景下，如果帧范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（默认），MySQL可以利用排序顺序逐行累加，效率较高。但如果帧范围包含FOLLOWING，每行都需要重新扫描部分数据，性能显著下降，此时考虑限制帧范围或在应用层处理。

窗口函数的引入让MySQL在OLAP分析场景的能力大幅提升。合理使用窗口函数替代自连接和子查询，往往能获得更简洁且更高效的查询方案。关键在于理解不同排名函数的行为差异，掌握帧范围的定义方式，并配合索引优化排序操作。