PostgreSQL CTE实战：从嵌套地狱到优雅查询

前阵子接手一个统计报表的改动打开那条查询的时候我愣了好几秒——六层嵌套子查询叠在一起抬头看字段低头找括号来回对了三遍才敢确认逻辑。其实这种“嵌套地狱”在 PostgreSQL 里不少见尤其是业务报表、权限过滤、层级汇总这类多步骤逻辑一旦用子查询硬拼代码量一上来谁也读不动。后来我把整条 SQL 用 CTE公用表表达式重写了一遍从“一团乱麻”变成几个带名字的步骤复杂查询才算真正有了人能看懂的写法。这篇文章就重点聊聊 PostgreSQL 里 CTE 的实战玩法不只有基础语法还有多步骤拆解、递归树形查询、在 UPDATE/DELETE 里用 CTE 做二次处理以及最容易被忽视的物化性能陷阱。如果你平时写 SQL 经常担心逻辑太长、怕别人看不懂或者拿递归查询没辙这篇文章应该能帮上忙。1. 嵌套地狱与临时表困局我为什么把 CTE 当作首选工具1.1 子查询嵌套可读性的灾难现场先看一段很典型的“反面教材”式 SQL它的业务意图其实很简单找出每个区域销售额排名前 3 的商品分类但代码写出来却让人头皮发麻。SELECT region, category, total_amount FROM ( SELECT region, category, SUM(amount) AS total_amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rn FROM orders GROUP BY region, category ) ranked WHERE rn 3 ORDER BY region, rn;这段代码还算是两层嵌套逻辑勉强能看。一旦需求变成“先按区域聚合再按月份算占比还要和上个月比较”子查询就会一层套一层最后变成括号套括号的“俄罗斯套娃”。问题在于子查询没有名字读者必须自己根据 SELECT 列表反推“这层到底在算什么”。而 CTE 恰好解决了这个点——它给中间结果起了名字每一次计算都像一个带标题的步骤。CTE 的全称是 Common Table Expression翻译过来是公用表表达式。核心机制不复杂用WITH关键字定义一个临时结果集这个结果集可以像一个“命名视图”那样被后续查询引用。你可以把它理解成“写 SQL 时的函数拆分”把大问题切成几个小函数每个函数负责一件事最终组合起来。1.2 CTE、子查询、临时表与视图到底怎么选很多人会问既然有子查询也有临时表TEMPORARY TABLE和视图VIEW为什么还要学 CTE这四者确实都能承载中间结果但适用场景完全不同。我整理了一张对比表基本代表了我日常选型时的判断逻辑。对比维度CTE子查询临时表视图作用域单条 SQL单条 SQL当前会话全局持久化可读性高有名字低嵌套深中需两步操作高是否落盘通常不落盘不落盘可落盘不落盘每次现算复用方式同一 SQL 内多次引用无法复用多次查询复用跨会话复用适用场景复杂查询拆步骤简单过滤逻辑需要反复调试、量大固定业务口径长期复用性能可控性12 需关注物化优化器内联需要自己管理生命周期优化器决定临时表最大的问题是生命周期太漫长要手动DROP Table否则连接释放前一直占着资源视图适合固定口径的复用但灵活性差经常要为不同条件建好几个视图。而 CTE 介于两者之间它适合“只在这一次查询里成立”的中间逻辑——既不用清理资源又能让结构清晰这也是我把它当作首选工具的原因。2. CTE 基础语法从命名结果集到多步骤拆解2.1 最简形态与逗号连接的多个 CTECTE 的基础语法很简单一条WITH子句可以同时定义多个 CTE用逗号分隔。只要记住一个原则后面的 CTE 可以直接引用前面的 CTE但前面的不能引用后面的这和编程语言里“先声明后使用”的规则一致。WITH category_summary AS ( SELECT category, SUM(amount) AS total_amount FROM orders WHERE order_date 2025-01-01 AND order_date 2026-01-01 GROUP BY category ), grand_total AS ( SELECT SUM(total_amount) AS global_total FROM category_summary ) SELECT cs.category, cs.total_amount, ROUND(cs.total_amount / gt.global_total * 100, 2) AS pct_of_total FROM category_summary cs CROSS JOIN grand_total gt ORDER BY pct_of_total DESC;这个例子里有两个 CTEcategory_summary先做品类汇总grand_total再基于前者的输出算总金额。主查询只需要把两者拼起来就能得到每个品类的销售额占比。如果不用 CTEcategory_summary和grand_total都会变成子查询重复出现代码长度直接翻倍。CTE 还支持给结果集设置列名相当于给中间结果重新命名列在处理表达式计算时特别好用WITH monthly_amount(year_num, mon, amount) AS ( SELECT EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date), SUM(amount) FROM orders GROUP BY 1, 2 ) SELECT * FROM monthly_amount;2.2 CTE 不是临时表一次编写、多次引用的真实语义初学者最容易犯的一个错误是把 CTE 当成“临时表变量”以为它执行一次之后就会到处复用。严格来说CTE 只是一个查询块的名字在 PostgreSQL 12 之前默认行为是物化后面章节详细讲但在 12 之后优化器会尝试把 CTE 像子查询一样内联展开。所以同一个 CTE 在主查询里被引用两次并不意味着集团队只扫描一次底层表性能表现取决于优化器怎么选。这个理解非常重要它直接影响你怎么写复杂查询。比如说你写了一个 CTE 叫valid_orders在主查询里 join 了两次你本意是省掉一次全表扫描但优化器如果选择内联就会变成两份独立执行计划。遇到这种情况要么依赖显式的MATERIALIZED关键词要么考虑在 CTE 里先做充分的过滤和聚合把数据量压到足够小这样即使多执行几次成本也可控。2.3 初级坑位CTE 里的 ORDER BY 与列名冲突写过一阵子 CTE 后你会发现两个高频小坑。第一个坑在 CTE 内部写ORDER BY基本没用除非你同时加了LIMIT。原因很简单CTE 是一个可以参与后续 join、过滤的中间结果保持无序状态对优化器更友好。比如SELECT * FROM (SELECT * FROM t ORDER BY id) x这种徒劳的排序在 CTE 里同样会被无视。如果你真的要排序后取前 N 条必须带LIMIT写法类似SELECT * FROM t ORDER BY id LIMIT 10。第二个坑多个 CTE 中如果出现同名列后面对它们的引用会变得非常混乱。建议复杂查询里给每个 CTE 的列名都带上语义前缀比如区域聚合用region_total月度聚合用month_total避免 join 之后到处是amount这种歧义列。这不算语法错误但在排错时真正的痛点——你根本不知道这个amount是哪个层级算出来的。3. 实战拆解一个多步骤报表查询如何用 CTE 分层落地3.1 业务需求区域分类双维度的分层分析纸上谈兵没意思直接看一个我实际处理过的业务场景。假设某业务系统的订单表结构如下记录了每一次销售的区域、商品分类、成交金额和日期CREATE TABLE orders ( order_id bigint PRIMARY KEY, region text, category text, amount numeric(10, 2), order_date date );业务方提的需求是这样按照 2025 年全量数据输出每个区域内销售额排名前 3 的品类同时列出每个品类的月度趋势方便看“头部品类是不是每个月都稳定”。如果直接一条 SQL 写出来会更乱。我用 CTE 把这个需求拆成了三层区域分类汇总、月度趋势计算、排名拼接。3.2 第一层区域分类汇总与月度聚合第一层只做两件事按区域分类汇总总销售额按分类月份聚合月度金额。因为后续排名要用“区域分类”维度月度趋势要用“分类月份”维度分开写能让每一步的语义都清清楚楚。WITH region_category AS ( SELECT region, category, SUM(amount) AS total_amount FROM orders WHERE order_date 2025-01-01 AND order_date 2026-01-01 GROUP BY region, category ), category_monthly AS ( SELECT category, date_trunc(month, order_date) AS month, SUM(amount) AS month_amount FROM orders WHERE order_date 2025-01-01 AND order_date 2026-01-01 GROUP BY category, date_trunc(month, order_date) ) SELECT * FROM region_category;这里有个细节date_trunc(month, order_date)可以把日期归一化到当月第一天是月度聚合的常用写法比to_char(order_date, YYYY-MM)处理起来更自然后面做环比时也可以保持日期类型排序。3.3 第二层窗口函数先排名再用主查询拼结果排名逻辑没有放在第一层而是用了一个独立的 CTEranked。原因在于窗口函数ROW_NUMBER()和分组聚合的先后顺序是有讲究的必须先完成区域分类聚合才能在此基础上按区域分组排名。把排名单独拆出来既符合计算顺序也让“谁是前 3 名”这个逻辑一目了然。WITH region_category AS ( SELECT region, category, SUM(amount) AS total_amount FROM orders WHERE order_date 2025-01-01 AND order_date 2026-01-01 GROUP BY region, category ), ranked AS ( SELECT region, category, total_amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) AS rn FROM region_category ) SELECT region, category, total_amount FROM ranked WHERE rn 3 ORDER BY region, rn;实际业务里这还不够因为业务方还要看每个头部品类的月度趋势。这时候主查询可以直接 join 第二步的category_monthly用category做关联得到的结果按分类、月份排序正好满足“头部品类月度稳定性分析”的要求。最终 SQL 看起来像一条按步骤书写的数据处理流水线每一层干了什么都写在 CTE 名字里维护的人不需要猜。3.4 再延伸一步用 CTE 做同比环比计算大多数报表最后都会走到“同比环比”这一步。用 CTE 写环比其实非常自然关键是先用月度聚合 CTE 把数据压到“月份一行”再用窗口函数LAG取上个月的金额WITH monthly AS ( SELECT category, date_trunc(month, order_date) AS month, SUM(amount) AS month_amount FROM orders GROUP BY category, date_trunc(month, order_date) ), monthly_with_prev AS ( SELECT category, month, month_amount, LAG(month_amount) OVER (PARTITION BY category ORDER BY month) AS prev_amount FROM monthly ) SELECT category, month, month_amount, ROUND( CASE WHEN prev_amount 0 THEN (month_amount - prev_amount) / prev_amount * 100 ELSE NULL END, 2 ) AS mom_rate FROM monthly_with_prev ORDER BY category, month;这里体现了 CTE 的核心价值分层转化。第一层把明细变成月度汇总第二层负责为每一行补上“上个月的值”第三层才是最终的指标计算。每一个转换步骤都能单独调试出了错也知道去哪一层查。4. 树形数据与递归 CTE复杂逻辑的最后一块拼图4.1 递归 CTE 的语法结构锚点加递归项普通 CTE 解决“多步骤”递归 CTE 解决“不知道多少层”的问题。最典型的场景就是组织架构、分类树、BOM 清单这类树形数据。递归 CTE 的写法固定为WITH RECURSIVE内部包含两个用UNION ALL连接的部分锚点成员非递归的起始查询负责找到树根或者第一层递归成员引用 CTE 自身基于当前结果继续下钻。WITH RECURSIVE org_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, ot.depth 1 FROM employees e JOIN org_tree ot ON e.manager_id ot.emp_id ) SELECT * FROM org_tree ORDER BY depth, emp_id;这个查询假设employees表里根节点是manager_id IS NULL的员工也就是 CEO 或总经理。每一轮递归会找到当前层级的直接下级depth字段告诉你这个人在第几层。PostgreSQL 会迭代执行直到没有任何新行产生。4.2 组织架构中的两个真实需求查管理链、算团队规模第一个需求给我某个员工 ID向上找到完整的管理链。这个场景特别适合递归 CTE而且递归方向是“自底向上”。WITH RECURSIVE emp_path AS ( SELECT emp_id, emp_name, manager_id, emp_name AS path FROM employees WHERE emp_id 42 UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, ep.path || - || e.emp_name FROM employees e JOIN emp_path ep ON e.emp_id ep.manager_id ) SELECT * FROM emp_path;注意这里递归成员里JOIN emp_path ep ON e.emp_id ep.manager_id含义是“当前员工的经理作为下一步节点”方向完全反过来了。实际使用中你只要保证没有环路这个查询就能稳定输出一条从员工一直到顶层的路径字符串。第二个需求算某个经理下面总共带了多少人包括嵌套的所有层级。这个必须用递归 CTE 先展开全树再分组计数WITH RECURSIVE team AS ( SELECT emp_id, emp_name, manager_id, emp_id AS root_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, t.root_id FROM employees e JOIN team t ON e.manager_id t.emp_id ) SELECT root_id, COUNT(*) - 1 AS team_size FROM team GROUP BY root_id;第一种递归里depth作为辅助列第二种递归里root_id把整棵树的成员都标记到根节点名下。理解递归 CTE 的关键是用一次迭代的视角去看每一次递归只推进一层绝不能指望一条 SQL 能处理“任意深度的迷宫”。4.3 递归 CTE 的边界和防坑递归 CTE 最容易翻车的是三种情况数据有环比如 A 的经理是 BB 的经理又是 A。这种情况递归会无限循环直到 PostgreSQL 报错。解法是在递归时加深度上限比如WHERE ot.depth 10或者用 PG14 之后提供的CYCLE子句检测环路。语法上递归引用不能出现在子查询内部。比如在递归成员里写FROM (SELECT ... FROM org_tree ...)会直接报错。递归引用只能作为JOIN的目标表出现。层级深、数据量大时递归成员每次迭代都会扫描底层表所以在manager_id上要建索引否则性能会非常难看。PostgreSQL 14 开始递归 CTE 还支持SEARCH和CYCLE子句能控制遍历顺序并自动做环检测。如果你的数据库版本比较新遇到树遍历需求可以直接查文档会比手动维护路径字段省事很多。5. 容易被忽视的价值在 UPDATE / DELETE 中执行 WITH5.1 用 CTE 加窗口函数清理重复数据CTE 不仅能出现在 SELECT 前面还能出现在 UPDATE、DELETE、INSERT 前面。这意味着一整条数据修改语句可以拥有“先定位目标、再做删改”的能力。比如清理订单表里的重复记录保留每个订单号最早创建的一条。传统写法可能要临时表两步走用 CTE 加ROW_NUMBER()一步完成WITH duplicates AS ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY order_no ORDER BY created_at ) AS rn FROM orders_refund ) DELETE FROM orders_refund t USING duplicates d WHERE t.id d.id AND d.rn 1;这里把窗口函数放在 CTE 里主查询DELETE ... USING直接关联 CTE 结果一次性把所有重复记录删除。相比“先 SELECT 出来看一眼再手动 DELETE”这种方式可重复执行、可审计脚本化处理数据时非常好用。5.2 数据归档DELETE...RETURNING 配合 CTE 做二次处理另一个高频场景是数据归档。假设要清理一年前的过期记录同时保留被删数据的关键字段到另一个日志表。用 CTE 加RETURNING可以一次完成WITH deleted AS ( DELETE FROM orders WHERE order_date 2024-01-01 RETURNING order_id, order_no, amount, deleted_at ) INSERT INTO orders_archive(order_id, order_no, amount, deleted_at) SELECT order_id, order_no, amount, deleted_at FROM deleted;这个写法的精妙之处在于RETURNING把被删除的行“留在内存里”后续 INSERT 能直接引用省掉了一次临时表传递。数据修改类 CTE 适合做这种“删除后转存”的两段式操作我在几个数据处理脚本里用过比先查再删再插的写法稳定得多。5.3 理解数据修改类 CTE 的快照语义有一点必须强调PostgreSQL 中同一个 CTE 里的多个数据修改语句以及主查询都基于同一个快照执行。也就是说CTE 内部看不到彼此刚刚修改的数据它们更像是“并行计划”而非“顺序步骤”。因此不要写“先 insert 再 update 刚 insert 的数据”这类依赖执行顺序的逻辑需求能拆开就拆开。官方文档也建议不要依赖这些语句的执行顺序来保证业务正确性。如果确实需要“先删后插”优先考虑用RETURNING作为数据通路而不是在多个数据修改语句之间做实时状态读取。6. 性能视角CTE 不是银弹物化陷阱要分清6.1 PostgreSQL 12 前后的默认行为差异PostgreSQL 12 是一个分水岭。在这之前的版本CTE 默认会被物化也就是先把 CTE 结果计算出来存到临时内存或者临时文件里后续查询直接读取这个结果。好处是 CTE 只算一次多次引用很划算坏处是过滤条件无法下推即使你只取其中一条记录它也会先把全部结果算出来。PostgreSQL 12 开始优化器默认尝试把 CTE 内联成普通子查询让外层条件直接下推到底层表扫描这样通常能大幅减少计算量。但这也带来了新问题如果同一个 CTE 在主查询里被引用了很多次内联之后相当于底层表被扫描多次。-- 12 版本想强制物化就明确说出来 WITH orders_summary AS MATERIALIZED ( SELECT region, COUNT(*) AS cnt, SUM(amount) AS total FROM orders GROUP BY region ) SELECT ... FROM orders_summary;-- 12 版本想让优化器尽量内联就明确写 NOT MATERIALIZED WITH small_set AS NOT MATERIALIZED ( SELECT id FROM orders WHERE status PAID ) SELECT ... FROM small_set;6.2 什么时候该手动指定物化策略我自己的经验是分情况处理CTE 体积大、只在主查询里被引用一次默认让它内联就好管太多反而可能破坏执行计划CTE 体积经过聚合已经很小、但被引用多次显式MATERIALIZED通常更稳相当于把“计算一次”的意图写清楚CTE 内部是复杂窗口函数或多表 join且后续多次引用物化的收益可能大于过滤下推的收益值得用EXPLAIN对比验证。6.3 用 EXPLAIN ANALYZE 观察真实差异判断 CTE 是否被物化最直接的办法是看执行计划EXPLAIN (ANALYZE, BUFFERS) WITH region_sum AS ( SELECT region, SUM(amount) AS total FROM orders GROUP BY region ) SELECT * FROM region_sum r JOIN region_sum r2 ON r.region r2.region;如果执行计划里出现CTE region_sum和CTE Scan on region_sum说明它被物化了如果出现两次独立的HashAggregate或Seq Scan on orders说明它被内联展开底层表扫描了多次。EXPLAIN ANALYZE给出的实际时间和 buffer 数值才能帮你判断哪种策略在当前数据量下更快。6.4 实测下来我发现的几个规律在我本地几百万行的订单表上做粗略测试结果符合大多数文档描述单一引用场景下内联版本比强制物化快 20% 到 40%因为过滤条件能提前压掉大量数据而多次引用同一聚合结果时物化版本通常更稳尤其当 CTE 内部结果已经很小的时候物化的开销几乎可以忽略不计却避免了重复聚合。还有两个不常被提到但挺重要的点递归 CTE 永远是物化的它是迭代执行不可能内联CTE 里如果带了数据修改语句比如 DELETE、INSERT、UPDATE同样不会内联因为这类语句会产生副作用优化器必须保持它们的执行边界。所以不要盲信“CTE 会自动物化所以很慢”这种旧版本经验也不要盲信“新版 CTE 一定很快”。说到底CTE 首先是为可读性服务的性能问题应该用执行计划判断而不是靠感觉。最后再分享一个我个人用 CTE 的小习惯日常查询超过三步比如“明细汇总后又要算占比又要算环比”我一定会拆成 CTE涉及树形层级一定会用WITH RECURSIVE数据清洗一定会用RETURNING来做二次转储。这个习惯说不上多高级但它救过我好几次——三个月后回头改 bug能一眼看懂自己当时写的是什么是真的事半功倍。