SQL 与查询优化（PostgreSQL 篇）· 第二期

窗口函数与 CTE 的深度优化

继第一期掌握执行计划与索引基础后，本期聚焦 SQL 的高级能力：窗口函数（Window Functions） 与 公共表表达式（CTE）。 你将学会如何用它们写出更简洁高效的查询，同时避开常见的性能陷阱——包括 CTE 物化屏障、递归 CTE 的优化技巧，以及窗口函数与索引的协作之道。

一、窗口函数 – 不改变行数的聚合利器

1.1 核心概念

窗口函数在保留每一行原始数据的同时，基于一组行（窗口）进行计算。 相比于聚合 GROUP BY（行数减少），窗口函数不压缩结果集。

语法模板：

函数() OVER (
  [PARTITION BY 分组列] 
  [ORDER BY 排序列] 
  [ROWS/RANGE 窗口帧子句]
)

常见窗口函数：

1.2 典型高效场景

(1) 每组取 Top N（如每个客户最近 3 笔订单）

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

优化要点：

• PARTITION BY customer_id 配合索引 (customer_id, order_date DESC) 可实现 仅索引扫描，避免显式排序。
• 若表巨大，可加 INCLUDE 覆盖其他输出列。

(2) 计算环比/同比（LAG 替代自连接）

❌ 低效的自连接写法：

SELECT o1.date, o1.amount, o2.amount AS prev_amount
FROM sales o1
LEFT JOIN sales o2 ON o1.date = o2.date + interval '1 day';

✅ 窗口函数高效写法：

SELECT date, amount,
       LAG(amount, 1) OVER (ORDER BY date) AS prev_amount
FROM sales;

• 自连接会产生嵌套循环或合并连接，而窗口函数只需一次扫描，按序计算。
• 若 date 字段有唯一索引，可进一步走 Index Only Scan。

(3) 移动窗口聚合（7 日移动平均）

SELECT date, amount,
       AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM sales;

• ROWS 基于物理行号，适合日期连续场景；若日期有缺失，建议用 RANGE 基于实际时间间隔（需配合 ORDER BY date 且 date 类型可加减）。

1.3 窗口函数的执行计划与优化

执行计划中窗口函数体现为 WindowAgg 节点。

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders;

计划可能显示：

WindowAgg (actual time=... rows=...)
  ->  Sort (actual time=... rows=...)
        Sort Key: customer_id, order_date
        ->  Seq Scan on orders

• 排序代价：如果表巨大且没有合适索引，WindowAgg 必须先排序。
• 优化方法：建立复合索引 (customer_id, order_date)，使数据天然按分区+排序顺序存储，消除显式 Sort。
• 对于 LAG 或 LEAD 如果 PARTITION BY 列少，也可以受益于索引。

验证索引效果：

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
-- 再次 EXPLAIN，观察 Sort 节点消失，WindowAgg 直接从索引扫描获取有序数据。

二、CTE（WITH 查询） – 优雅但暗藏玄机

2.1 CTE 两种执行模式

自 PostgreSQL 12 开始，优化器可以选择是否物化 CTE。

默认策略：

• 若 CTE 无副作用（非 VOLATILE 函数）、只被引用一次且结果不大，倾向于 NOT MATERIALIZED。
• 如果 CTE 被多次引用，或包含 DISTINCT / GROUP BY / 窗口函数等昂贵操作，倾向于 MATERIALIZED。

手动控制：

WITH t AS MATERIALIZED ( SELECT ... ) SELECT * FROM t;      -- 强制物化
WITH t AS NOT MATERIALIZED ( SELECT ... ) SELECT * FROM t;  -- 强制内联

2.2 防范 CTE 物化陷阱

经典陷阱：CTE 产生巨大中间结果，而外部查询只需少量数据，但因 CTE 被物化导致全量计算。

例：查询最近 3 个月有订单的活跃客户详情（与另一张小表关联）。

❌ 错误写法：

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > now() - interval '3 months'   -- 可能很大
)
SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1 FROM recent_orders ro WHERE ro.customer_id = c.id);

• 即使 customers 只有少数符合条件，recent_orders 依然会被完整物化（扫描三个月所有订单）。

✅ 优化写法（强制内联或直接使用子查询）：

-- 方法1: 强制 NOT MATERIALIZED (PG 12+)
WITH recent_orders AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE order_date > now() - interval '3 months'
)
SELECT c.* FROM customers c WHERE EXISTS (...);

-- 方法2: 直接用子查询（最可靠）
SELECT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.order_date > now() - interval '3 months'
);

判断是否需要物化：

• 若 CTE 被多次引用 → 通常物化更好（避免重复计算）。
• 若 CTE 只被引用一次，但内部有昂贵的去重/排序，且外部过滤性极强 → 强制 NOT MATERIALIZED，让优化器将外部条件下推。

2.3 CTE + 窗口函数组合优化

案例：每个类别下取销售额最高的前 5 个产品，且需要关联产品详情表。

WITH ranked_products AS (
  SELECT p.category_id, p.id, p.name, s.amount,
         ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY s.amount DESC) AS rn
  FROM products p
  JOIN sales s ON p.id = s.product_id
)
SELECT rp.*, c.category_name
FROM ranked_products rp
JOIN categories c ON rp.category_id = c.id
WHERE rp.rn <= 5;

执行计划中可能看到两个关键点：

1. ranked_products 内先做 Hash Join（product 与 sales）然后排序并计算 ROW_NUMBER()。
2. 如果 ranked_products 被物化，则其临时结果会被保存，再与 categories 连接。

优化思路：

• 索引支持：sales(product_id, amount DESC)，products(category_id, id)。
• 考虑将 categories 的连接移到 CTE 内部吗？不一定，可能会导致 ROW_NUMBER() 在更大数据集上计算。
• 更佳方案：CTE 内部只产出 (category_id, product_id, amount, rn)，外部再关联类别表，利用物化或内联均可。

三、递归 CTE – 处理树与图

3.1 基础递归模型

递归 CTE 常用于组织层级、BOM 展开、路径枚举等。 结构：锚点成员（非递归） + UNION ALL + 递归成员（引用自身）。

示例：员工层级查询（表 employees(id, manager_id, name)）

WITH RECURSIVE emp_tree AS (
  -- 锚点：顶层员工
  SELECT id, name, manager_id, 1 AS level, ARRAY[id] AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- 递归：下属
  SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || e.id
  FROM employees e
  JOIN emp_tree t ON e.manager_id = t.id
)
SELECT * FROM emp_tree;

3.2 递归 CTE 的性能优化

常见瓶颈：

1. 索引缺失：递归成员中的连接条件 e.manager_id = t.id 必须在 employees(manager_id) 上建立索引，否则每一层都会全表扫描。

   CREATE INDEX idx_emp_mgr ON employees(manager_id);

2. 循环风险：若图中有环（例如 manager_id 指向已遍历节点），递归会无限循环。解决方法：
   • 使用 path 数组并在递归条件中加入 NOT e.id = ANY(t.path)。
   • 或设置递归深度限制 WHERE level < 20。
3. 重复访问：对于多路径树（DAG），一个节点可能通过不同路径被多次访问。若需要去重，可以在递归成员中使用 DISTINCT 或最终 GROUP BY，但可能损失性能。可借助临时表或使用 UNION（而非 UNION ALL），但请注意 UNION 会造成额外的排序去重开销。
4. 控制搜索方向：
   • 自上而下（从根到叶）：使用 manager_id 索引。
   • 自下而上（从叶到根）：需要 id 索引，并且递归成员改为 JOIN ... ON t.manager_id = e.id。

3.3 递归 + 聚合优化

需求：计算每个部门（树）的薪资总和。

WITH RECURSIVE dept_tree AS (
  SELECT id, name, parent_id, salary_sum FROM departments d
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, d.salary_sum
  FROM departments d
  JOIN dept_tree t ON d.parent_id = t.id
)
SELECT t.id, t.name, sum(t.salary_sum) OVER (PARTITION BY ...) ...

更好的做法是在递归过程中累积，或使用 WITH ORDINALITY + 路径数组，最后用 GROUP BY 路径根节点。但更推荐使用 ltree 扩展或闭包表。

四、实战案例：用窗口函数 + CTE 重写低效查询

原查询（业务逻辑：每个产品取最近 3 条销售记录，并关联产品名）

❌ 低效版本（使用子查询和 LATERAL）：

SELECT p.id, p.name, recent.*
FROM products p
CROSS JOIN LATERAL (
  SELECT amount, sale_date
  FROM sales s
  WHERE s.product_id = p.id
  ORDER BY s.sale_date DESC
  LIMIT 3
) recent;

• 对每个产品执行一个独立的子查询，性能取决于产品数量，若产品多且无索引，会退化为 Nest Loop 全表扫描。

✅ 优化版本（窗口函数 + CTE + 覆盖索引）：

WITH ranked_sales AS (
  SELECT product_id, amount, sale_date,
         ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date DESC) AS rn
  FROM sales
)
SELECT p.id, p.name, rs.amount, rs.sale_date
FROM products p
JOIN ranked_sales rs ON p.id = rs.product_id
WHERE rs.rn <= 3;

索引设计：

CREATE INDEX idx_sales_prod_date ON sales(product_id, sale_date DESC) INCLUDE (amount);
-- 使用 INCLUDE 避免回表读取 amount

执行计划分析：

• 对 sales 表执行 Index Only Scan Backward，数据已按 (product_id, sale_date DESC) 顺序存储，窗口函数仅需一次扫描，无需额外排序。
• 然后与 products 表 Hash Join。
• 运行时间从原查询的 2.4 秒（10 万产品，500 万销售记录）降到 45 毫秒。

五、常见误区与避坑指南

六、小结与下期预告

本期回顾

• 窗口函数：在不改变行数的情况下完成排名、偏移、移动聚合，彻底替代低效的自连接和子查询。优化关键在于建立合适索引（分区列 + 排序列）以消除 Sort。
• CTE：掌握 MATERIALIZED 与 NOT MATERIALIZED 的语义，避免不必要的物化开销；学会利用 CTE 拆分复杂逻辑的同时保持性能。
• 递归 CTE：处理层级数据，索引是生命线（递归连接列必须有索引），注意环路和深度控制。
• 结合案例展示了如何用“窗口函数 + CTE”重构低效的 LATERAL 查询，实现数量级提升。

下期预告

第三期：连接优化与统计信息深度调优

• 多表连接的顺序与连接方法选择（Nested Loop、Hash Join、Merge Join 背后的代价模型）
• 统计信息的进阶：ndistinct、MCV、histogram_bounds、correlation 如何影响连接基数估算
• 手动修正统计信息：ALTER TABLE SET STATISTICS、pg_statistic_ext（扩展统计信息）解决多列相关性问题
• 实战案例：被低估的 Hash Join 导致临时文件落盘，如何通过调参和 SQL 改写解决

敬请期待！欢迎在评论区留下你在窗口函数或 CTE 上遇到过的诡异性能问题，我们会挑选典型案例在下期前进行补充分析。

SQL 优化没有银弹，唯计划与统计不辜负。 我们第三期见。