MySQL 8.0窗口函数与CTE递归查询实战：复杂报表SQL优化从子查询到声明式改写

MySQL 8.0窗口函数如何替代传统子查询实现高效排名与分组聚合

MySQL 8.0引入窗口函数（Window Functions）之前，排名、分组Top-N、累计求和等场景依赖自连接、用户变量或子查询，SQL可读性差且执行计划难以优化。窗口函数通过声明式语法将”在结果集的某个窗口内计算”这一需求直接表达，优化器可以生成更高效的执行计划，避免中间结果集的多次物化。

窗口函数语法结构：函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列 frame_clause)。frame_clause定义窗口帧范围，默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

-- 部门内按薪资排名（传统变量写法 vs 窗口函数）
-- 传统写法：需要用户变量和子查询嵌套
SELECT * FROM (
  SELECT *, @rank := IF(@dept = dept_id, @rank + 1, 1) AS rank,
         @dept := dept_id FROM employees ORDER BY dept_id, salary DESC
) t WHERE rank <= 3;

-- 窗口函数写法
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
  FROM employees
) t WHERE rank <= 3;

常见窗口函数的实战场景与SQL模板

场景一：分组Top-N。每个商品分类取销量前3的商品：

SELECT * FROM (
  SELECT product_id, category_id, sales_count,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_count DESC) AS rn
  FROM product_sales
) ranked WHERE rn <= 3;

场景二：累计求和。银行账户的逐笔余额计算：

SELECT account_id, trans_date, amount,
       SUM(amount) OVER (PARTITION BY account_id ORDER BY trans_date 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
FROM transactions;

场景三：环比/同比计算。利用LAG函数取前一行数据做差：

SELECT month, revenue,
       revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_diff,
       revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_diff
FROM monthly_revenue;

场景四：移动平均。7日滑动平均营收：

SELECT date, daily_revenue,
       AVG(daily_revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_stats;

注意ROWS与RANGE的区别：ROWS按物理行偏移，RANGE按逻辑值偏移。处理重复值时RANGE更安全但性能更差，大多数场景用ROWS即可。

CTE递归查询：树形结构与层级数据的优雅处理

公用表表达式（Common Table Expression, CTE）配合递归可以优雅处理组织架构、菜单树、物料BOM等层级数据。递归CTE由锚点查询（Anchor Member）和递归查询（Recursive Member）通过UNION ALL连接：

-- 查询某员工的所有上级汇报链
WITH RECURSIVE reporting_chain AS (
    -- 锚点：起始员工
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE id = 1001
    
    UNION ALL
    
    -- 递归：逐级向上查找
    SELECT e.id, e.name, e.manager_id, rc.level + 1
    FROM employees e 
    INNER JOIN reporting_chain rc ON e.id = rc.manager_id
)
SELECT * FROM reporting_chain ORDER BY level;

物料BOM展开示例——计算产品的所有子组件及其需求数量：

WITH RECURSIVE bom_tree AS (
    -- 锚点：顶层产品
    SELECT parent_id, child_id, quantity, 
           CAST(child_id AS CHAR(200)) AS path,
           quantity AS total_qty
    FROM bom WHERE parent_id = 'PRODUCT-A'
    
    UNION ALL
    
    -- 递归：展开子组件
    SELECT b.parent_id, b.child_id, b.quantity,
           CONCAT(bt.path, '->', b.child_id) AS path,
           bt.total_qty * b.quantity AS total_qty
    FROM bom b INNER JOIN bom_tree bt ON b.parent_id = bt.child_id
)
SELECT * FROM bom_tree;

递归CTE的防护措施：MySQL默认递归深度上限为100（cte_max_recursion_depth），数据层级超限会报错。生产环境需根据实际业务深度调整，但务必设上限防止无限递归：

SET SESSION cte_max_recursion_depth = 500;

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

窗口函数的执行计划有其特殊性。EXPLAIN输出中，窗口函数阶段显示为”Window”操作，位于ORDER BY之后、HAVING之前。这意味着窗口函数在WHERE和GROUP BY之后执行，不能在WHERE子句中引用窗口函数的结果——必须用CTE或子查询包装。

性能优化三个要点：减少PARTITION BY的分区数——分区数越多，排序缓冲区占用越大，超过sort_buffer_size会写磁盘；合理选择帧范围——UNBOUNDED PRECEDING帧只需一次顺序扫描，而滑动窗口（ROWS BETWEEN N PRECEDING）需要维护滑动状态；避免不必要的排序——如果PARTITION BY的列已有索引，MySQL可以利用索引有序性跳过排序步骤。

-- 为窗口函数创建复合索引
ALTER TABLE product_sales ADD INDEX idx_category_sales (category_id, sales_count DESC);

-- EXPLAIN验证是否利用索引消除排序
EXPLAIN FORMAT=JSON
SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales_count DESC) AS rn
FROM product_sales;
-- 检查 "using_filesort": false 表示索引生效

从子查询到CTE+窗口函数的改写收益实测

以一个100万行订单表的分组Top-5查询为例：传统写法（关联子查询 + LIMIT）执行耗时4.2秒，扫描行数约500万次；CTE + ROW_NUMBER窗口函数写法执行耗时1.1秒，扫描行数约100万次。提速3.8倍，根因在于窗口函数只需对数据做一次排序分区，而关联子查询每行都要执行一次子查询。在数据量更大的场景下差距更显著——1000万行时子查询写法因临时表溢出磁盘导致耗时飙升至35秒以上，窗口函数写法稳定在5秒内。