如何在SQL中利用CTE公用表表达式优化复杂聚合逻辑

写CTE最核心的一条原则，可以说是“先过滤，再聚合”。很多开发者习惯把WHERE条件扔在主查询里，结果CTE内部先来一遍全表扫描，再算聚合——明明99.9%的数据都不需要，却白白浪费资源。正确的做法，是把那些高选择性条件（比如created_at >= '2026-01-01'）直接写进CTE的SELECT子句中，配合好复合索引，只挑必要的列。链式CTE要分阶段处理，PostgreSQL默认会物化，得小心；MySQL 8.0链式相对安全，但还是要看谓词下推是否到位。

CTE里该不该先过滤再聚合？

必须提前过滤。很多慢查询的根源恰恰是把WHERE放在主查询里——CTE内部先扫全表，再算聚合，哪怕最终结果只有几百行。用EXPLAIN ANALYZE一看，Seq Scan on orders扫了上百万行，这就是典型的翻车现场。

• 把高选择性条件（比如status = 'completed'、created_at >= '2026-01-01'）直接写进CTE的SELECT子句中，不要等到主查询才过滤
• 确保过滤字段上有索引，而且复合索引的字段顺序要跟后续GROUP BY和ORDER BY保持一致
• 别在CTE里用SELECT *，只拿真正需要的列，减少内存和排序开销——这个细节经常被忽略

多个聚合步骤怎么链式写才不掉坑？

用逗号分隔多个CTE，每个负责一个原子操作：过滤 → 聚合 → 衍生计算 → 关联补全。别指望一个CTE同时干所有事，容易出事。

典型的翻车点：GROUP BY列不一致报错（PostgreSQL特别严格），MySQL在宽松模式下虽然不报错但结果可能悄悄跑偏。

• 每个CTE的输出列名必须显式定义，别依赖SELECT *去推导
• 第二个CTE引用第一个时，列名要完全匹配，大小写也要一致——尤其是在PostgreSQL里
• 如果需要跨CTE做比较（比如部门均薪 vs 公司均薪），用CROSS JOIN连接单行结果集，避免漏连接条件触发笛卡尔积

PostgreSQL 中 CTE 真的“不物化”吗？

默认是物化的。PostgreSQL把每个CTE当成临时表来处理，即使只引用一次，也可能多扫一遍基表——这对窗口函数加多层聚合来说相当危险。

现象：执行计划里出现两次Seq Scan on orders，或者I/O等待飙升。

• PG 12+可以加MATERIALIZED或NOT MATERIALIZED提示，但后者只是建议，不保证内联
• 更稳妥的做法：当CTE只被引用一次且没有递归时，直接改写成子查询，让优化器有机会内联
• 如果确实需要复用而且数据量超过1万行，优先考虑用临时表#temp_table，别指望CTE缓存

MySQL 8.0 的 CTE 能放心链式调用吗？

可以，而且比PG更轻量。MySQL 8.0默认不强制物化CTE（除非递归或显式声明WITH RECURSIVE），链式结构基本等价于语法糖。

但别因此忽略谓词下推——索引能否生效，仍然取决于CTE内部是否提前过滤。

• 链式CTE中，每一步的WHERE和GROUP BY都可能触发索引下推，前提是字段上有合适的索引
• 用UNION ALL拼接多个CTE分支时，各分支输出列数、类型、顺序必须严格一致，否则报错
• 避免在CTE里写ORDER BY（除非配合OFFSET/FETCH），MySQL会直接报错

CTE不是性能银弹，它最大的价值是把“逻辑步骤”显式暴露出来；而真正的性能拐点，往往藏在过滤位置、索引设计和数据库对物化的实际处理方式里。