MySQL 8.0窗口函数实战指南：复杂报表查询的高效写法

窗口函数解决什么查询问题

MySQL 8.0引入的窗口函数（Window Functions）是复杂报表查询的利器。传统SQL做排名、累计求和、环比增长等分析，需要写嵌套子查询或用户变量hack，代码可读性差、执行效率低。窗口函数用声明式语法一次性完成这些计算，查询优化器可以生成更优的执行计划。

SQL查询优化中，窗口函数的核心价值在于：一次表扫描完成多维度聚合，避免重复JOIN和子查询。对数据库高可用架构中的OLAP场景，窗口函数是降低查询复杂度的标准工具。

窗口函数基础语法与执行逻辑

窗口函数语法结构：

函数名() OVER (
  [PARTITION BY 分区表达式]
  [ORDER BY 排序表达式 [ASC|DESC]]
  [frame_clause 帧范围定义]
)

执行逻辑：
1. FROM/JOIN确定数据集
2. WHERE过滤行
3. GROUP BY聚合（如果有）
4. 窗口函数基于聚合后的结果计算
5. HAVING过滤聚合结果
6. SELECT输出

理解这个顺序很重要——窗口函数在GROUP BY之后、SELECT之前执行，所以可以引用聚合列，但不能在WHERE/HAVING中引用窗口函数结果（需要用CTE或子查询包裹）。

排名函数实战：销售报表的多维度排名

最常见的窗口函数场景是排名。三种排名函数的区别：

-- ROW_NUMBER: 连续不重复排名（1,2,3,4）
-- RANK: 同值同排名，跳号（1,1,3,4）
-- DENSE_RANK: 同值同排名，不跳号（1,1,2,3）

SELECT 
    region,
    salesperson,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS row_num,
    RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rank_num
FROM sales_summary
WHERE year = 2026;

取每个区域Top 3销售员：

-- MySQL 8.0+ CTE写法
WITH ranked AS (
    SELECT 
        region,
        salesperson,
        revenue,
        DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk
    FROM sales_summary
    WHERE year = 2026
)
SELECT * FROM ranked WHERE rnk <= 3;

比传统写法（GROUP_CONCAT + SUBSTRING_INDEX hack）清晰10倍，执行计划也更高效——只需要一次排序+分区扫描。

累计聚合：财务报表中的Running Total

累计求和在财务、库存报表中频繁出现。窗口函数的帧定义（Frame Clause）控制聚合范围：

-- 月度收入累计求和
SELECT 
    month,
    monthly_revenue,
    SUM(monthly_revenue) OVER (
        ORDER BY month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    -- 同比增长率
    LAG(monthly_revenue, 12) OVER (ORDER BY month) AS revenue_last_year,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue, 12) OVER (ORDER BY month)) 
        / LAG(monthly_revenue, 12) OVER (ORDER BY month) * 100, 2
    ) AS yoy_growth_rate
FROM monthly_revenue_report
ORDER BY month;

帧范围关键字：
– UNBOUNDED PRECEDING：从分区第一行开始
– CURRENT ROW：到当前行
– N PRECEDING：当前行前N行
– N FOLLOWING：当前行后N行

滑动窗口计算移动平均：

-- 3个月移动平均收入
SELECT 
    month,
    monthly_revenue,
    ROUND(AVG(monthly_revenue) OVER (
        ORDER BY month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3m,
    -- 7日移动平均（适用于日粒度数据）
    ROUND(AVG(daily_amount) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_7d
FROM revenue_data;

LEAD/LAG函数：环比与同比计算

LEAD和LAG是时间序列分析的基石函数。LEAD取后续行值，LAG取前行值。

-- 日报表：环比增长 + 前日对比
SELECT 
    date,
    daily_sales,
    LAG(daily_sales, 1) OVER (ORDER BY date) AS prev_day_sales,
    ROUND(
        (daily_sales - LAG(daily_sales, 1) OVER (ORDER BY date)) 
        / LAG(daily_sales, 1) OVER (ORDER BY date) * 100, 2
    ) AS day_over_day_growth,
    -- 前一周同日对比
    LAG(daily_sales, 7) OVER (ORDER BY date) AS same_day_last_week,
    -- 下一天预测差值
    LEAD(daily_sales, 1) OVER (ORDER BY date) - daily_sales AS next_day_diff
FROM daily_sales_report
ORDER BY date DESC
LIMIT 30;

复杂场景：计算每个用户最近3次订单的平均金额

SELECT 
    user_id,
    order_id,
    order_amount,
    ROUND(AVG(order_amount) OVER (
        PARTITION BY user_id 
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS avg_last_3_orders
FROM orders
WHERE status = 'completed';

NTILE与分组分析：用户分层与分桶统计

NTILE函数将数据均匀分成N组，常用于用户分层、分位数统计：

-- 用户消费分层：将用户按消费金额均分5个等级
SELECT 
    user_id,
    total_spending,
    NTILE(5) OVER (ORDER BY total_spending DESC) AS spending_tier,
    CASE NTILE(5) OVER (ORDER BY total_spending DESC)
        WHEN 1 THEN '高消费'
        WHEN 2 THEN '中高消费'
        WHEN 3 THEN '中消费'
        WHEN 4 THEN '中低消费'
        WHEN 5 THEN '低消费'
    END AS tier_name
FROM (
    SELECT user_id, SUM(amount) AS total_spending
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY user_id
) t;

-- 分桶统计：每10%分位的收入分布
WITH percentiled AS (
    SELECT 
        NTILE(10) OVER (ORDER BY annual_revenue) AS decile,
        annual_revenue
    FROM company_revenue
)
SELECT 
    decile,
    MIN(annual_revenue) AS min_revenue,
    MAX(annual_revenue) AS max_revenue,
    AVG(annual_revenue) AS avg_revenue,
    COUNT(*) AS company_count
FROM percentiled
GROUP BY decile
ORDER BY decile;

性能调优：窗口函数的执行计划分析

窗口函数的执行计划与传统聚合不同，需要特别关注排序开销。

查看执行计划：

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

-- 重点关注：
-- 1. "Window aggregate" 节点是否有 "Sort" 子节点
-- 2. 如果PARTITION BY列已有索引，优化器可以跳过排序
-- 3. 多个窗口函数是否合并到一次扫描

索引策略：

-- 为窗口函数的PARTITION BY + ORDER BY创建复合索引
CREATE INDEX idx_sales_region_revenue 
ON sales_summary(region, revenue DESC);

-- 覆盖索引避免回表
CREATE INDEX idx_orders_user_date_amount 
ON orders(user_id, order_date, amount) 
INCLUDE (status);

多窗口合并：多个窗口函数如果PARTITION BY和ORDER BY相同，MySQL会合并到一次扫描执行：

-- 三个窗口共享相同的分区和排序，一次扫描完成
SELECT 
    product_id,
    sale_date,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num,
    LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_revenue,
    SUM(revenue) OVER (PARTITION BY product_id ORDER BY sale_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum
FROM product_sales;

如果窗口定义不同，MySQL会分别排序，产生多个Window Sort节点。把相同分区排序的窗口放在一起，减少排序次数。

数据迁移实战：窗口函数在分库分表中的应用

分库分表场景下，跨分片的窗口函数无法直接使用。解决方案是在中间件层（如ShardingSphere）或应用层聚合：

-- ShardingSphere 5.x支持部分窗口函数下推
-- 配置hint强制路由到单分片
/* ShardingSphere hint: dataSourceName=ds_0 */
SELECT 
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM t_order_0
WHERE user_id = 10001;

跨分片场景下，建议在应用层做二次聚合：各分片执行窗口函数后，结果汇总到中间层做跨分片排序和计算。数据迁移时利用窗口函数做增量校验，比全量对比效率高几个数量级。

MySQL 8.0窗口函数不是”锦上添花”的可选功能，而是OLAP查询的标准工具。传统写法中的用户变量hack、嵌套子查询、多次JOIN，在窗口函数面前都是技术债。掌握排名、累计聚合、时序对比、分桶统计这四类核心模式，足以覆盖90%的报表查询需求。