PostgreSQL 高级查询：窗口函数、CTE 与递归查询

敖正炀
4 阅读55分钟

本文是 PostgreSQL 深度内核与工程实战系列的第 8 篇。前文《PostgreSQL 查询优化与执行计划深度》已系统拆解了优化器的代价模型、扫描方式与 Join 策略的决策逻辑。本文将深入剖析窗口函数、CTE（公共表表达式）、递归 CTE 与物化视图的底层实现机制与工程应用，结合执行计划解读与 MySQL 8.x 对比，揭示 PostgreSQL 在处理复杂分析查询时的核心竞争力。

衔接前文

前文《PostgreSQL 查询优化与执行计划深度》已经掌握了如何通过 EXPLAIN ANALYZE 解读一条 SQL 的执行效率，理解了顺序扫描、索引扫描、各种 Join 算法的代价模型。但面对复杂的分析需求，如“每个部门的工资排名”、“近 7 天销售额的移动平均”、“一个产品的完整物料清单”，传统 SQL 往往力不从心——要么需要多轮自连接，要么需要游标或存储过程，要么性能极差。PostgreSQL 的窗口函数、CTE（公共表表达式）、递归 CTE 和物化视图正是为解决这些高级分析场景而设计的利器。本文将深入这些特性的底层执行逻辑与工程应用，并结合 MySQL 8.x 的差异对比，揭示 PG 在复杂数据处理上的独特优势。

总结性引言

在数据驱动的业务场景中，SQL 早已不再是单纯的数据存取语言，而是一种强大的分析工具。PostgreSQL 的窗口函数使得排名、累计、移动平均等分析操作变得优雅且高效——它不需要 GROUP BY 那样压缩行，而是在每一行上“开窗”计算；CTE 让复杂查询分解为清晰的模块，递归 CTE 更是将树形结构和图数据的遍历纳入 SQL 范畴；物化视图则为报表和数据快照提供了持久化的查询加速方案。本文将逐一拆解这些高级查询特性的内部实现：从 ROW_NUMBER() 的排序算法到递归 CTE 的 WorkTable 迭代，从 CTE 物化与非物化的代价权衡到并发刷新物化视图的锁机制，为你构建一张完整的 PG 高级查询能力地图。

核心要点

  • 窗口函数：ROW_NUMBER / RANK / DENSE_RANK 的排名差异（唯一编号、跳号、不跳号）、LAG / LEAD 的偏移指针原理、聚合函数配合窗口的累计计算（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。
  • CTE：物化与非物化的行为差异、PG 12+ 的 MATERIALIZED / NOT MATERIALIZED 显式控制、多 CTE 链式依赖的执行顺序。
  • 递归 CTE：WITH RECURSIVE 的迭代执行流程（初始查询 → UNION 递归部分 → WorkTable 暂存 → 终止条件）、树形与图数据遍历、CYCLE 子句（PG 14+）防无限递归。
  • 物化视图：普通视图 vs 物化视图的本质（保存 SQL vs 保存结果集）、全量重建 vs 并发刷新机制、适用场景与刷新频率权衡。
  • 与 MySQL 8.x 差异：CTE 默认物化 vs 内联优化、MATERIALIZED 控制、并发刷新支持、CYCLE 子句。

文章组织架构图

flowchart LR
    A["<b>PostgreSQL 高级查询</b><br/>窗口函数 · CTE · 递归 CTE · 物化视图"] --> B["<b>1. 窗口函数</b><br/>排名、偏移与累计计算"]
    A --> C["<b>2. CTE</b><br/>物化与非物化的代价权衡"]
    A --> D["<b>3. 递归 CTE</b><br/>树形结构与图数据的 SQL 遍历"]
    A --> E["<b>4. 物化视图</b><br/>查询结果的持久化与并发刷新"]
    A --> F["<b>5. 与 MySQL 8.x 的差异对比</b>"]
    A --> G["<b>6. 面试高频专题</b>"]

    B --> B1["PARTITION BY + ORDER BY 窗口帧"]
    B --> B2["ROW_NUMBER / RANK / DENSE_RANK 算法"]
    B --> B3["LAG/LEAD 偏移访问"]
    B --> B4["聚合函数 + 窗口累计计算"]
    B --> B5["WindowAgg 执行计划"]

    C --> C1["PG 12 前默认物化"]
    C --> C2["PG 12+ 默认非物化"]
    C --> C3["MATERIALIZED / NOT MATERIALIZED"]
    C --> C4["CTE Scan vs 内联"]
    C --> C5["链式 CTE 执行顺序"]

    D --> D1["初始查询 → 递归部分 → 终止条件"]
    D --> D2["WorkTable 迭代原理"]
    D --> D3["树形结构遍历（组织架构）"]
    D --> D4["图数据遍历 + CYCLE 防环"]
    D --> D5["max_recursion_depth"]

    E --> E1["普通视图 vs 物化视图"]
    E --> E2["REFRESH 全量重建锁行为"]
    E --> E3["REFRESH CONCURRENTLY 增量刷新"]
    E --> E4["唯一索引与内部临时表机制"]
    E --> E5["适用场景与刷新频率"]

    F --> F1["CTE 物化策略差异"]
    F --> F2["CYCLE 子句支持"]
    F --> F3["并发刷新物化视图"]
    F --> F4["窗口帧完整度"]

    G --> G1["12+ 面试题"]
    G --> G2["系统设计题"]

架构图分层说明

  • 总览说明：全文 6 个模块从窗口函数出发，逐步深入 CTE、递归 CTE、物化视图，再通过 MySQL 对比和面试题完成闭环。每个模块内部拆解为 3~5 个核心子主题，覆盖语法、执行原理、计划解读和工程权衡。

  • 逐模块说明：

    • 模块 1 建立窗口函数的基础认知，强调与 GROUP BY 的本质区别，并从排名、偏移、累计三个维度深入底层算法与 WindowAgg 节点。
    • 模块 2 深入 CTE 的物化与非物化行为，对比 PG 12 前后的变化，并以 EXPLAIN 解读物化代价。
    • 模块 3 揭示递归 CTE 的迭代执行原理，以 WorkTable 为核心讲解内存结构，并给出树形/图数据实战。
    • 模块 4 讲解物化视图的持久化与并发刷新，重点剖析 REFRESH CONCURRENTLY 的 MVCC 与唯一索引依赖。
    • 模块 5 进行数据库实现的对比分析，突出 PG 的设计优势。
    • 模块 6 以面试题形式巩固知识，覆盖内部原理、性能调优和系统设计。

  • 关键结论：PostgreSQL 的高级查询特性（窗口函数、CTE、递归 CTE、物化视图）是其区别于 MySQL 的核心竞争力之一。理解这些特性的执行逻辑和代价权衡，是解决复杂分析需求和进行高性能数据架构设计的关键。

1. 窗口函数：排名、偏移与累计计算

1.1 窗口函数与 GROUP BY 的本质区别

在分析数据时，GROUP BY 将多行聚合成一行，丢失了原始行的细节。而窗口函数在保留每一行原始数据的基础上，为每一行计算一个基于其“窗口”（一组相关行）的聚合值或排名值。窗口函数通过 OVER() 子句定义窗口，不压缩行数。

-- GROUP BY: 每个部门一行，丢失员工明细
SELECT dept_id, AVG(salary) 
FROM employee 
GROUP BY dept_id;

-- 窗口函数: 每行员工保留，同时看到部门平均工资
SELECT name, dept_id, salary, 
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary
FROM employee;

执行时，PostgreSQL 会对全表扫描（或索引扫描），然后根据 PARTITION BY 将数据分为多个独立的分区，在每个分区内进行窗口计算。这与 GROUP BY 的哈希聚合或排序聚合有本质区别。

1.2 窗口的核心语法组件

一个完整的窗口定义包含三个部分：

  • PARTITION BY：将数据切分成逻辑上独立的分区。每个分区内的窗口计算互不影响，类似于 GROUP BY 的分组，但不折叠行。
  • ORDER BY：指定每个分区内行的排序顺序，影响排名函数（ROW_NUMBER 等）和窗口帧（frame）的边界。
  • frame_clause：定义窗口帧——即当前行参与计算的行的范围。例如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示从分区第一行到当前行的所有行。

窗口帧（Window Frame）的内部实现

PostgreSQL 执行窗口函数时，会对每个分区排序后，逐行扫描。对于每一行，根据 frame_clause 动态确定帧的起始行和结束行，然后对该帧内的行执行聚合或偏移计算。这个过程中，窗口帧本质上是一个滑动窗口，其边界可能随当前行的移动而变化。

例如：

SUM(amount) OVER (ORDER BY sale_date 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

PostgreSQL 维护一个累计和变量，每处理一行，将当前行值累加，时间复杂度 O(N)，无需重复扫描帧内所有行。

1.3 排名函数族：ROW_NUMBER、RANK、DENSE_RANK、NTILE

排名函数根据 ORDER BY 指定的列对分区内行进行编号，但编号规则不同。

1.3.1 ROW_NUMBER() —— 唯一编号

为每一行分配一个唯一的顺序号，即使排序键值相同，编号也不同。内部实现：在分区排序后，直接维护一个递增计数器，每行加1，忽略键值重复。

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employee;

适用场景：删除重复记录时保留一行（例如保留每个部门工资最高的员工），或为报表生成行号。

1.3.2 RANK() —— 跳号排名

相同排序键值的行获得相同排名，下一个不同值跳过相应数量。例如两个并列第1，下一个是第3。实现：比较当前行与前一行的排序键值，若相等则保持 rank 不变，否则 rank 更新为 当前行号（从1开始的行号）。

1.3.3 DENSE_RANK() —— 不跳号排名

相同键值排名相同，下一个不同值紧接下一个整数，不跳号。实现：维护一个 dense_rank 计数器，仅当排序键值变化时才递增。

对比图：

flowchart LR
    subgraph 原始行
        A["(工资 10000)"]
        B["(工资 9000)"]
        C["(工资 9000)"]
        D["(工资 8000)"]
    end
    subgraph ROW_NUMBER
        A1["1"]
        B1["2"]
        C1["3"]
        D1["4"]
    end
    subgraph RANK
        A2["1"]
        B2["2"]
        C2["2"]
        D2["4"]
    end
    subgraph DENSE_RANK
        A3["1"]
        B3["2"]
        C3["2"]
        D3["3"]
    end
    A --> A1 & A2 & A3
    B --> B1 & B2 & B3
    C --> C1 & C2 & C3
    D --> D1 & D2 & D3

图表主旨概括：对比三种排名函数在相同排序键值（9000 重复）下的编号差异。

逐层/逐元素分解：左侧为四行按工资降序排列的数据；中间三列分别展示 ROW_NUMBER（连续唯一）、RANK（并列后跳号）、DENSE_RANK（并列后不跳号）的输出。

设计原理映射：ROW_NUMBER 基于行号计数器；RANK 基于“当前行号”与“重复计数”的差值；DENSE_RANK 基于独立于行号的紧凑序号。

工程联系与关键结论：选择哪种排名函数取决于业务是否需要跳号。排行榜多用 RANK 或 DENSE_RANK，而分页或去重用 ROW_NUMBER。

1.3.4 NTILE(n) —— 分桶

将分区尽可能平均地分配到 n 个桶中，每行得到一个桶编号。实现：先计算分区总行数 count，每个桶的基础大小为 count / n，余数部分分配给前 count % n 个桶，然后扫描时根据行号分配桶号。常用于四分位数、十分位数分析。

1.4 偏移函数：LAG 与 LEAD

LAG(column, offset, default) 访问当前行之前第 offset 行的值；LEAD 向后访问。底层实现：在分区排序后，PostgreSQL 将排序结果存储在内存或临时文件中，然后通过指针偏移直接定位到目标行的物理位置。由于已经排序，相邻行的偏移可以通过索引数组快速访问，复杂度 O(1)。

典型应用：环比/同比分析。

SELECT sale_date, amount,
       LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_day_amount,
       amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS delta
FROM sales;

注意：ORDER BY 是偏移函数的必要前提——没有排序则“前后行”无意义。如果 PARTITION BY 存在，偏移只在分区内进行。

1.5 聚合函数配合窗口的累计计算

聚合函数（SUM, AVG, COUNT, MAX, MIN）也可用于窗口，通过 frame_clause 定义累计范围。

SELECT emp_id, salary, 
       SUM(salary) OVER (ORDER BY emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employee;

内部优化：对于 ROWS UNBOUNDED PRECEDING 这种从开头到当前行的累计，PostgreSQL 使用一个累加器变量，每行 O(1) 更新，而不是每次重新扫描窗口帧。但若窗口帧为 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING（滑动平均），则需要维护一个固定大小的队列。

1.6 WindowAgg 执行计划解读

使用 EXPLAIN 可以看到窗口函数对应的节点 WindowAgg。例如：

EXPLAIN (ANALYZE, BUFFERS) 
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) 
FROM employee;

输出片段：

WindowAgg  (cost=123.45..234.56 rows=1000 width=...) (actual time=0.5..5.2 rows=1000 loops=1)
   Partition Key: department
   Order By: salary DESC
   ->  Sort  (cost=123.45..145.67 rows=1000 width=...) (actual time=0.4..1.2 rows=1000 loops=1)
         Sort Key: department, salary DESC
         ->  Seq Scan on employee  (cost=0..100.00 rows=1000 ...)

解读：

  • WindowAgg 节点负责执行窗口函数计算，它从子节点（通常是 Sort 或 Index Scan）获取已按 PARTITION BY + ORDER BY 排序的数据。
  • 如果没有索引提供天然顺序，优化器会插入显式 Sort 节点，代价较高。
  • 索引优化：如果在 (department, salary DESC) 上创建复合索引，可以省去 Sort，执行计划变为 Index Scan 直接输出有序数据给 WindowAgg，大幅提升性能。

1.7 适用场景总结

场景窗口函数方案
每个分组内的 Top-N（如每个部门工资前三）ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) + 外层 WHERE rn <= 3
累计和/移动平均SUM(amount) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING)
环比增长LAG(amount) OVER(ORDER BY date)
去重保留最新记录（如每个用户最新订单）ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) + 取 rn=1
四分位数/百分位数NTILE(4) OVER(ORDER BY score)

2. CTE（公共表表达式）：物化与非物化的代价权衡

CTE（Common Table Expression）使用 WITH 子句定义一个临时的命名结果集，可在后续主查询中多次引用。它最大的价值是将复杂查询拆解为清晰的逻辑步骤。

2.1 CTE 的基本语法

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employee e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

2.2 PG 12 前后的物化行为演变

物化：将 CTE 查询的结果集存储在内存或临时文件中，后续引用直接读取存储的结果。

  • PG 12 之前：CTE 总是被物化。优化器将 CTE 视为一个优化屏障（optimization fence），不会将 CTE 中的条件推入或与外层合并。这可以避免重复执行，但可能错失索引扫描或提前过滤的机会。
  • PG 12+：默认行为改为非物化，即优化器尝试将 CTE 内联到主查询中，像子查询一样统一优化。这允许条件下推、Join 重排序等优化，但若 CTE 被多次引用，会导致重复计算。

2.3 MATERIALIZED / NOT MATERIALIZED 显式控制

PG 12+ 提供了显式控制关键字：

  • MATERIALIZED：强制物化 CTE，适合 CTE 被多次引用且计算开销大，或者想避免优化器错误决定。
  • NOT MATERIALIZED：强制内联，适合 CTE 只被引用一次且希望与外层合并优化。
-- 强制物化
WITH cte AS MATERIALIZED (SELECT ...) SELECT ...;

-- 强制非物化
WITH cte AS NOT MATERIALIZED (SELECT ...) SELECT ...;

2.4 物化 vs 非物化的执行计划对比

示例：CTE 被引用两次。

WITH expensive_cte AS (
    SELECT user_id, sum(amount) total 
    FROM orders 
    GROUP BY user_id
)
SELECT * FROM expensive_cte e1 
JOIN expensive_cte e2 ON e1.user_id = e2.user_id;
  • 默认（非物化）：expensive_cte 会被计算两次，执行计划中看到两个独立的 Aggregate 节点。
  • 若指定 MATERIALIZED：执行计划中会出现 CTE Scan 节点，底层有一个 Materialize 节点将 CTE 结果缓存，第二个引用直接扫描缓存。

EXPLAIN (默认非物化)：

Nested Loop
  ->  Subquery Scan on e1  (actual time=... rows=...)
        ->  HashAggregate on orders (group by user_id)
  ->  Subquery Scan on e2
        ->  HashAggregate on orders (group by user_id)  # 重复计算

EXPLAIN (MATERIALIZED)：

CTE Scan on expensive_cte e1
   CTE expensive_cte
     ->  HashAggregate on orders
           ->  Seq Scan on orders
   Materialize  (实际是隐式的临时存储)
CTE Scan on expensive_cte e2

图表：CTE 物化与非物化行为对比序列图

sequenceDiagram
    participant Q as 主查询
    participant Optimizer as 优化器
    participant Exec as 执行器
    participant Temp as 临时存储

    rect rgb(240,240,255)
        Note over Q,Temp: "物化 (MATERIALIZED)"
        Q->>Optimizer: "WITH cte AS MATERIALIZED (SELECT ...)"
        Optimizer->>Exec: "执行 CTE 查询"
        Exec->>Temp: "将结果写入临时表/内存"
        Exec-->>Optimizer: "完成物化"
        Q->>Exec: "第一次引用 cte"
        Exec->>Temp: "扫描临时存储"
        Q->>Exec: "第二次引用 cte"
        Exec->>Temp: "再次扫描临时存储（无重复计算）"
    end

    rect rgb(255,240,240)
        Note over Q,Temp: "非物化 (NOT MATERIALIZED)"
        Q->>Optimizer: "WITH cte AS NOT MATERIALIZED (SELECT ...)"
        Optimizer->>Optimizer: "将 CTE 内联到主查询"
        Q->>Exec: "执行主查询"
        Exec->>Exec: "遇到第一次引用，执行 CTE 子查询"
        Exec->>Exec: "遇到第二次引用，再次执行 CTE 子查询"
    end

图表主旨概括：对比物化与非物化模式下执行器处理 CTE 引用的流程差异。

逐层/逐元素分解：上半部分展示物化路径——先独立执行 CTE 并存储结果，后续引用均读取存储；下半部分展示非物化路径——CTE 内联后，每次引用都重新执行子查询。

设计原理映射：物化是空间换时间（避免重复计算但增加存储 I/O），非物化是时间换优化机会（允许全局优化但可能重复计算）。

工程联系与关键结论：CTE 被多次引用时使用 MATERIALIZED；只引用一次且需要索引或条件下推时使用 NOT MATERIALIZED。PG 12+ 默认非物化更灵活，但开发者需理解代价。

2.5 多 CTE 链式依赖的执行顺序

CTE 可以依赖前面定义的 CTE，PostgreSQL 会按照依赖关系拓扑排序执行，将每个 CTE 物化（或内联）后再供后续引用。例如：

WITH 
cte1 AS (SELECT user_id, sum(amount) FROM orders GROUP BY user_id),
cte2 AS (SELECT user_id, total FROM cte1 WHERE total > 100),
cte3 AS (SELECT COUNT(*) FROM cte2)
SELECT * FROM cte3;

执行时：先计算 cte1，然后基于其结果计算 cte2，最后计算 cte3。依赖链中的每个 CTE 如果是 NOT MATERIALIZED 且只被引用一次，可能会被完全内联成一个大查询，但链式较复杂时优化器可能仍选择物化中间结果。

2.6 适用场景

  • 复杂查询分解：多层嵌套子查询可改写为 CTE，提升可读性和维护性。
  • 重复引用相同中间结果：避免多次执行同一子查询（配合物化）。
  • 递归查询（见下节）。

3. 递归 CTE：树形结构与图数据的 SQL 遍历

WITH RECURSIVE 是 SQL 标准中定义的递归查询能力，PostgreSQL 实现了完整的递归 CTE，可用于遍历树形结构（组织架构、分类树）和图数据（社交网络、路径查找）。

3.1 WITH RECURSIVE 的执行流程

递归 CTE 的语法：

WITH RECURSIVE cte_name (column_list) AS (
    -- 初始查询（非递归部分）
    SELECT ... 
    UNION [ALL] 
    -- 递归部分（引用 cte_name 自身）
    SELECT ... FROM cte_name JOIN other_table ...
)
SELECT * FROM cte_name;

执行内部原理：

  1. 初始化：执行非递归部分，将结果集存入 WorkTable（工作表，一个内部临时存储）。
  2. 迭代：
    • 将 WorkTable 作为递归部分的输入，执行递归查询，产生新的结果集作为 IntermediateTable。
    • 如果使用 UNION（隐式 DISTINCT），PostgreSQL 会去重；如果使用 UNION ALL，保留所有行（包括重复）。
    • 将 IntermediateTable 复制到 WorkTable（替换），同时将中间结果追加到最终结果集。
  3. 终止条件：当递归部分返回 0 行时，迭代结束。
  4. 输出：合并所有迭代结果（非递归部分 + 各轮递归结果）。

内部数据结构：PostgreSQL 使用两个 Tuplestore（临时元组存储）来交换 WorkTable 和中间结果，迭代轮数受 max_recursion_depth 限制（默认 200）。

递归 CTE 迭代执行流程图：

flowchart TD
    Start([开始递归 CTE 执行]) --> Init[执行初始非递归查询]
    Init --> StoreWork["将结果存入 WorkTable<br/>并加入最终结果集"]
    StoreWork --> CheckEmpty{WorkTable 为空?}
    CheckEmpty -- 是 --> End([结束, 返回最终结果])
    CheckEmpty -- 否 --> RecursiveStep["执行递归部分查询<br/>使用当前 WorkTable 作为输入"]
    RecursiveStep --> Intermediate["得到 IntermediateTable"]
    Intermediate --> IsEmpty{IntermediateTable 为空?}
    IsEmpty -- 是 --> End
    IsEmpty -- 否 --> UnionAppend["如果是 UNION 则去重<br/>追加到最终结果集"]
    UnionAppend --> ReplaceWork["IntermediateTable 成为新的 WorkTable"]
    ReplaceWork --> CheckEmpty

图表主旨概括：描述递归 CTE 的迭代执行流程，突出 WorkTable 作为动态输入/输出存储的核心作用。

逐层/逐元素分解：从初始查询生成首个 WorkTable；循环判断 WorkTable 非空时执行递归部分，产生 IntermediateTable；若非空则去重后替换 WorkTable，继续下一轮；直至递归部分返回空行终止。

设计原理映射：该流程类似于广度优先遍历（BFS），每一轮迭代处理当前层节点，输出新的子节点作为下一轮输入。WorkTable 即“当前层节点集合”。

工程联系与关键结论：递归 CTE 通过 WorkTable 交换实现迭代，性能取决于递归深度和每轮的数据量。UNION 会引入去重排序开销，UNION ALL 更快但需要手动防止环路。

3.2 树形结构遍历实战：组织架构层级

employee 表包含 id, name, manager_id（上级 ID）。查询某个经理的所有下属（含间接下属）：

WITH RECURSIVE subordinates AS (
    -- 初始查询：直接下属（经理的直接下属）
    SELECT id, name, manager_id, 1 AS level
    FROM employee
    WHERE manager_id = 1   -- 假设经理 id=1

    UNION ALL

    -- 递归部分：下属的下属
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employee e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level, id;

EXPLAIN ANALYZE 输出示例：

CTE Scan on subordinates  (actual time=0.01..0.05 rows=10 loops=1)
   CTE subordinates
     Recursive Union  (actual time=0.00..0.04 rows=10 loops=1)
       ->  Index Scan using idx_manager_id on employee  (actual time=0.01..0.01 rows=2)
             Index Cond: (manager_id = 1)
       ->  Hash Join  (actual time=0.01..0.01 rows=4 loops=2)
             Hash Cond: (e.manager_id = s.id)
             ->  Seq Scan on employee e  (actual time=0.00..0.00 rows=10 loops=2)
             ->  Hash  (actual time=0.00..0.00 rows=2 loops=2)
                   ->  WorkTable Scan on subordinates s  (actual time=0.00..0.00 rows=2 loops=2)

解读：Recursive Union 是递归 CTE 的根节点；非递归部分为 Index Scan；递归部分每轮执行 Hash Join，其中 WorkTable Scan 表示扫描当前 WorkTable。

3.3 图数据遍历与环路检测

在无环图中，简单的递归 CTE 即可。但在有环图中（如社交网络互相关注），需要防止无限递归。PG 14+ 引入了 CYCLE 子句。

WITH RECURSIVE friendship_path AS (
    SELECT user_id, friend_id, ARRAY[user_id] AS path, false AS is_cycle
    FROM friendships
    WHERE user_id = 1
    
    UNION ALL
    
    SELECT f.user_id, f.friend_id, fp.path || f.user_id,
           f.user_id = ANY(fp.path)  -- 自定义循环检测
    FROM friendships f
    JOIN friendship_path fp ON f.user_id = fp.friend_id
    WHERE NOT fp.is_cycle
)
CYCLE user_id, friend_id SET is_cycle USING path  -- PG 14+ 简洁写法
SELECT * FROM friendship_path;

PG 14+ CYCLE 子句原理：优化器会自动在递归 CTE 的 WorkTable 中增加一个标志列，并维护一个“已访问行”的哈希集合，当检测到即将重复时停止扩展该分支。

3.4 性能考量与限制

  • 递归深度限制：max_recursion_depth（默认 200）防止失控递归。深度较大时可以 SET max_recursion_depth = 1000; 但注意内存和栈压力。
  • 索引需求：递归部分的连接条件（如 e.manager_id = s.id）的列上必须有索引，否则每轮迭代都会全表扫描，性能灾难。
  • WorkTable 膨胀：如果递归部分产生大量中间行且使用 UNION（去重），PG 会对每轮结果排序去重，开销巨大。通常对于树遍历使用 UNION ALL 避免去重。
  • 与循环存储过程对比：递归 CTE 声明式、可优化、可并行的潜力，但复杂业务逻辑（如需要全局状态或变量）仍适合 PL/pgSQL 循环。

3.5 适用场景

  • 组织架构查询：某个主管的所有下属（含层级）。
  • BOM（物料清单）展开：产品的原材料组成树。
  • 路径查找：两点之间是否存在通路、最短路径（配合 CYCLE 限制）。
  • 评论回复树：获取某条帖子的所有评论及嵌套关系。

4. 物化视图：查询结果的持久化与并发刷新

4.1 普通视图 vs 物化视图

  • 普通视图：本质是一个保存的 SQL 查询规则，每次查询时都会重新执行定义中的 SQL。不占用磁盘存储（除了元数据），总是看到最新基表数据，但性能可能差（尤其复杂聚合）。
  • 物化视图：将查询结果物理存储为一张表，查询时直接读取表中的数据，性能极快。但数据是陈旧快照，需要手动刷新。
-- 普通视图
CREATE VIEW order_summary AS
SELECT date_trunc('month', order_date) AS month, sum(amount) FROM orders GROUP BY month;

-- 物化视图
CREATE MATERIALIZED VIEW order_summary_mv AS
SELECT date_trunc('month', order_date) AS month, sum(amount) FROM orders GROUP BY month;

4.2 REFRESH MATERIALIZED VIEW 全量重建

REFRESH MATERIALIZED VIEW order_summary_mv;

该命令会排他锁物化视图：在刷新期间，所有尝试读取该视图的查询都会被阻塞，直到刷新完成。对于大型物化视图，这可能导致长时间不可用。

4.3 REFRESH MATERIALIZED VIEW CONCURRENTLY 并发刷新

PG 9.4 引入的并发刷新机制，不阻塞查询，但需要物化视图上有一个唯一索引（通常是基于主键或唯一列）。

-- 必须先创建唯一索引
CREATE UNIQUE INDEX idx_order_summary_mv_month ON order_summary_mv (month);

-- 并发刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary_mv;

并发刷新内部原理：

  1. 创建一个临时表，执行物化视图的定义查询，将新数据写入临时表。
  2. 基于唯一索引，对当前物化视图和临时表进行 差集计算（需要插入、更新、删除哪些行）。
  3. 使用 INSERT ... ON CONFLICT 和 DELETE 更新物化视图，每一行级别的操作都遵循 MVCC 规则，不锁全表。
  4. 在事务提交前，查询依然看到旧版本数据。提交后新数据可见。

并发刷新原理序列图：

sequenceDiagram
    participant U as 用户/应用
    participant MV as 物化视图(已有数据)
    participant Temp as 临时表
    participant Idx as 唯一索引

    U->>MV: REFRESH CONCURRENTLY
    MV->>Temp: CREATE TEMP TABLE 并执行视图查询<br/>填充最新数据
    MV->>Idx: 基于唯一索引，逐行比较 MV 与 Temp
    alt 行在 Temp 中但不在 MV 中
        MV->>MV: INSERT
    else 行在 MV 中但不在 Temp 中
        MV->>MV: DELETE
    else 行在两个表中但数据不同
        MV->>MV: UPDATE
    end
    MV->>MV: COMMIT (原子切换)
    Note over U,MV: 整个过程中，其他查询可并发读取 MV 旧数据

图表主旨概括：展示 REFRESH CONCURRENTLY 如何通过临时表和唯一索引逐行更新物化视图，而不阻塞读。

逐层/逐元素分解：用户发起刷新；系统创建临时表并填充最新查询结果；基于唯一索引逐行判断差异，执行 INSERT/DELETE/UPDATE；最后提交事务，原子地使新数据可见。

设计原理映射：该机制类似于 增量刷新 或 Merge 操作，用额外的计算和 I/O 代价换取读并发性。唯一索引是定位行的关键，否则无法安全并发。

工程联系与关键结论：并发刷新避免了长时间排他锁，但需要物化视图上有唯一索引，且刷新开销高于全量重建（因为要计算差异）。适合读多写少、允许秒级到分钟级延迟的分析场景。

4.4 适用场景与刷新频率权衡

场景方案刷新频率
每日销售报表物化视图每日一次（凌晨刷新）
实时仪表盘（低延迟要求）普通视图或实时查询不适用物化视图
数据仓库 ETL 中间表物化视图每小时或每批次
复杂聚合的加速（大量用户重复查询相同统计）物化视图定期刷新 + 并发刷新

注意：物化视图会占用磁盘空间，且每次全量重建会触发大量写 I/O，可能加剧表膨胀（详见系列第 6 篇 MVCC 与 VACUUM）。并发刷新虽然不阻塞读，但也会产生许多死元组，需要定期 VACUUM。

5. 与 MySQL 8.x 的差异对比

MySQL 8.0 引入了 CTE（非递归和递归）和窗口函数，但在许多细节上与 PostgreSQL 存在差异。

5.1 CTE 物化策略差异

  • PostgreSQL 12+：默认非物化（内联），允许优化器将 CTE 视为普通子查询统一优化。可以通过 MATERIALIZED / NOT MATERIALIZED 显式控制。
  • MySQL 8.x：CTE 总是物化到一个临时表，无法内联。优化器不能将 CTE 中的条件推入到主查询或与主查询合并。这导致某些查询性能较差，例如 CTE 中有 WHERE 条件，本可以在外层进一步过滤，但 MySQL 会先完全物化 CTE 结果再过滤。

示例：CTE 包含大量数据，但外层只取少量行。

WITH cte AS (SELECT * FROM huge_table) 
SELECT * FROM cte WHERE id = 1;
  • PG（默认非物化）：优化器会将 id=1 条件下推到 CTE 内部，huge_table 直接索引扫描，效率高。
  • MySQL：先物化整个 huge_table 到临时表（可能走全表扫描），再过滤，效率低。

5.2 显式控制物化

  • PG：MATERIALIZED / NOT MATERIALIZED 关键字。
  • MySQL：无对应语法，只能通过 /*+ SET_VAR(cte_max_recursion_depth=...) */ hint 间接影响递归深度，但无法改变物化行为。

5.3 递归 CTE 的环路检测

  • PG 14+：CYCLE 子句原生支持，自动检测环路。
  • MySQL：不支持 CYCLE，需要手工在递归部分通过 WHERE NOT EXISTS 或应用层维护访问路径来防止无限递归，写法复杂且效率低。

5.4 物化视图并发刷新

  • PG：REFRESH MATERIALIZED VIEW CONCURRENTLY 支持并发刷新（需唯一索引）。
  • MySQL：不支持物化视图（只有普通视图）。要实现类似功能需借助第三方工具或手动创建表+定时任务。

5.5 窗口函数的帧（frame）支持

  • PG：完整支持 ROWS, RANGE, GROUPS 三种模式，GROUPS 自 PG 11 引入。
  • MySQL 8.x：支持 ROWS 和 RANGE，但不支持 GROUPS。某些边界定义（如 RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW）可能行为有细微差异。

5.6 总结对比表

特性PostgreSQL 16.xMySQL 8.x
CTE 默认物化非物化（内联）物化（临时表）
显式控制物化MATERIALIZED / NOT MATERIALIZED无
递归 CTE 环路检测CYCLE 子句 (PG14+)手工实现
物化视图并发刷新支持 CONCURRENTLY不支持
窗口函数帧模式ROWS / RANGE / GROUPSROWS / RANGE
查询优化器对 CTE 的优化可条件下推、Join 重排序受限（CTE 作为黑盒）

关键结论：PostgreSQL 在 CTE 处理上更灵活高效，物化视图并发刷新是数据仓库场景的核心优势，窗口函数标准支持也更完整。

6. 面试高频专题

6.1 窗口函数与 GROUP BY 有什么本质区别？各自适用什么场景？

一句话回答：窗口函数在保留原始行细节的同时，为每一行计算一个基于窗口行的聚合值；GROUP BY 将多行压缩为一行，丢失细节。

详细解释：

  • 内部实现与执行计划对比

    准备测试表：

    CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    region TEXT,
    amount NUMERIC(10,2),
    sale_date DATE
);
INSERT INTO sales (region, amount, sale_date)
VALUES ('East', 100, '2025-01-01'),
       ('East', 200, '2025-01-02'),
       ('West', 150, '2025-01-01'),
       ('West', 250, '2025-01-02');

    GROUP BY 查询：

    EXPLAIN (ANALYZE, BUFFERS)
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

    执行计划输出（简化）：

    HashAggregate  (cost=1.05..1.07 rows=2 width=40) (actual time=0.023..0.026 rows=2 loops=1)
   Group Key: region
   Buffers: shared hit=1
   ->  Seq Scan on sales  (cost=0.00..1.04 rows=4 width=36) (actual time=0.008..0.010 rows=4 loops=1)
    • HashAggregate 节点对每个 region 计算 SUM，输出行数 = 分组数（2行）。
    • 原始行被压缩，无法同时看到每笔销售明细。

    窗口函数查询：

    EXPLAIN (ANALYZE, BUFFERS)
SELECT region, sale_date, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

    执行计划输出：

    WindowAgg  (cost=1.05..1.07 rows=4 width=52) (actual time=0.018..0.022 rows=4 loops=1)
   Partition Key: region
   Buffers: shared hit=1
   ->  Sort  (cost=1.05..1.05 rows=4 width=40) (actual time=0.015..0.016 rows=4 loops=1)
         Sort Key: region
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=1
         ->  Seq Scan on sales  (cost=0.00..1.04 rows=4 width=40) (actual time=0.005..0.007 rows=4 loops=1)
    • WindowAgg 节点在排序后的数据上为每一行计算分区聚合值，输出行数 = 原始行数（4行）。
    • 同时保留 sale_date、amount 等明细。

  • 适用场景：

    • GROUP BY：需要汇总统计（如报表、仪表盘卡片）。
    • 窗口函数：需要明细与统计同框（如员工列表带部门平均工资）、排名、累计、移动平均、同比环比。

多角度追问：

  1. 窗口函数可以替代所有 GROUP BY 场景吗？为什么不能？

    • 不能。窗口函数不压缩行，若业务仅需部门总工资，不需要员工列表，使用 GROUP BY 输出更少行，效率更高且语义清晰。

  2. 执行计划中 WindowAgg 和 HashAggregate 的代价模型有何不同？

    • HashAggregate 只需扫描一次数据并构建哈希表，内存友好；WindowAgg 需要排序（除非索引已排序），排序可能产生磁盘 I/O。但 WindowAgg 避免了自连接。

  3. 如果要在分组统计的同时保留每个分组的前三条明细，用窗口函数怎么写？用 GROUP BY + 子查询怎么写？性能差异？

    • 窗口函数方案（推荐）： 
      WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) rn
    FROM sales
)
SELECT * FROM ranked WHERE rn <= 3;
    • GROUP BY + 子查询（低效）： 
      SELECT s1.*
FROM sales s1
JOIN LATERAL (
    SELECT COUNT(*) FROM sales s2
    WHERE s2.region = s1.region AND s2.amount > s1.amount
) AS t(cnt) ON true
WHERE cnt < 3;
      窗口函数只需一次排序 + 过滤，子查询方案需要对每行执行相关子查询，复杂度 O(N^2)。

加分回答：

  • 窗口函数支持 FILTER 子句（PG 9.4+）可实现条件聚合：SUM(amount) FILTER (WHERE sale_date > '2025-01-01') OVER (PARTITION BY region)。
  • 窗口函数与 DISTINCT 结合时，DISTINCT 在窗口计算之后执行，可能导致意外结果，需谨慎。

6.2 ROW_NUMBER()、RANK()、DENSE_RANK() 的区别是什么？如何选择？

一句话回答：ROW_NUMBER() 为每一行分配唯一连续编号；RANK() 相同值同排名，后续跳号；DENSE_RANK() 相同值同排名，后续不跳号。

详细解释：

代码示例与结果对比：

WITH score_table AS (
    SELECT * FROM (VALUES ('A', 100), ('B', 90), ('C', 90), ('D', 80)) AS t(name, score)
)
SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
       RANK()       OVER (ORDER BY score DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM score_table;

输出：

name | score | row_num | rank | dense_rank
-----|-------|---------|------|-----------
A    | 100   | 1       | 1    | 1
B    | 90    | 2       | 2    | 2
C    | 90    | 3       | 2    | 2
D    | 80    | 4       | 4    | 3

内部算法差异（源码级描述）：

  • ROW_NUMBER()：维护一个简单的 current_row 计数器，每行加1，不依赖排序键比较。
  • RANK()：在 WindowAgg 执行过程中，缓存前一行的排序键（如 score），若相同则 rank 不变，否则 rank = current_row_index。
  • DENSE_RANK()：维护独立 dense_rank 变量，仅当前行排序键与上一行不同时，dense_rank++。

选择指南：

  • 唯一行号（分页、去重） → ROW_NUMBER
  • 竞赛排名（允许跳号，如“第1名，第2名，第2名，第4名”） → RANK
  • 紧凑排名（不跳号，如“第1名，第2名，第2名，第3名”） → DENSE_RANK

多角度追问：

  1. 对于相同的排序键值，三种函数的执行效率是否有差异？

    • ROW_NUMBER 最轻量，因为无需比较排序键。RANK 和 DENSE_RANK 需要存储前一行的排序键值并比较，额外内存开销可忽略不计（几字节每行）。

  2. 窗口排序中使用多列 ORDER BY 时，三种函数的行为如何？

    • 三者均基于所有排序列的复合值比较。例如 ORDER BY region, score DESC，只有当 region 和 score 都相同时才视为“相同值”进行并列。

  3. 如果要在每个分区内实现“并列第一算第一，第二仍是第二”，应选哪个函数？

    • 需求实际上是“不跳号”，应选 DENSE_RANK。例如部门内绩效排名：两人并列第1，下一个是第2。

加分回答：

  • PG 中 RANK() 和 DENSE_RANK() 在窗口帧（frame）定义中也可使用，但排名会基于帧内的行重新计算，非常用用法。
  • 性能微基准测试（百万行）：ROW_NUMBER约比 RANK 快 3-5%，差异不大，优先考虑语义正确性。

6.3 PG 12 后 CTE 的物化行为发生了什么变化？MATERIALIZED 和 NOT MATERIALIZED 分别适用什么场景？

一句话回答：PG 12 前 CTE 总是物化（优化屏障）；PG 12+ 默认非物化（内联），允许优化器全局优化；MATERIALIZED 强制物化避免重复计算，NOT MATERIALIZED 强制内联利用索引条件下推。

详细解释（含执行计划对比）：

测试数据：

CREATE TABLE orders (id SERIAL, user_id INT, amount NUMERIC, order_date DATE);
INSERT INTO orders SELECT generate_series(1,10000), random()*100, random()*1000, '2025-01-01'::date + (random()*100)::int;
CREATE INDEX idx_user_id ON orders(user_id);

查询需求：查找下单总额超过 5000 的用户，并关联其订单明细。

CTE 默认行为（PG 12+）：

WITH high_value_users AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 5000
)
SELECT o.*
FROM orders o
JOIN high_value_users h ON o.user_id = h.user_id;

默认（非物化）执行计划（简化）：

Nested Loop
  ->  HashAggregate (GROUP BY user_id HAVING sum > 5000)
        ->  Seq Scan on orders
  ->  Index Scan using idx_user_id on orders o (Index Cond: (user_id = h.user_id))
  • high_value_users 并未物化为临时表，而是被内联为一个子查询，与 orders 连接时，优化器可以将 HAVING 条件提前过滤。

显式物化（MATERIALIZED）：

WITH high_value_users AS MATERIALIZED (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 5000
)
SELECT o.*
FROM orders o
JOIN high_value_users h ON o.user_id = h.user_id;

执行计划会多出一个 CTE Scan 节点：

CTE Scan on high_value_users h
   CTE high_value_users
     ->  HashAggregate ...
   ->  Index Scan on orders o
  • CTE 结果存储在 Tuplestore 中，后续引用（本例只有一次）仍会读取临时存储，但避免了重复计算聚合（若引用两次，节省时间）。

NOT MATERIALIZED 强制内联（与默认相同，但更显式）：

WITH high_value_users AS NOT MATERIALIZED ( ... )

适用场景总结：

  • MATERIALIZED：CTE 被 多次引用 且计算代价高；或者 CTE 中存在易变函数（如 random()），希望只计算一次。
  • NOT MATERIALIZED：CTE 只引用一次，且希望优化器进行条件下推、连接重排序等优化。

多角度追问：

  1. 如何通过 EXPLAIN 判断一个 CTE 是否被物化？

    • 物化时出现 CTE Scan 节点，且 CTE 定义部分在计划树顶部单独显示；非物化时看不到 CTE Scan，CTE 的逻辑被直接内联到主查询中。

  2. 在 PG 12+ 中，如果一个 CTE 既被多次引用，又有复杂计算，不指定 MATERIALIZED 会有什么后果？

    • 默认非物化会导致 CTE 子查询 重复执行 多次。例如： 
      WITH c AS (SELECT * FROM huge_table)
SELECT * FROM c JOIN c AS c2 ON c.id = c2.id;
      会两次扫描 huge_table，性能灾难。需显式 MATERIALIZED。

  3. 链式 CTE（cte2 依赖 cte1）在默认非物化下如何执行？

    • 优化器会尝试将整个链式内联，按依赖顺序展开为一个复杂查询。但如果链路过深或存在多次引用，优化器可能自动退化物化中间结果（通过决策代价模型）。

加分回答：

  • PG 14+ 支持 WITH 中的 SEARCH 和 CYCLE 子句，用于递归 CTE 的深度优先/广度优先搜索及环路检测，与 MATERIALIZED 无关。
  • 使用 EXPLAIN (VERBOSE, COSTS) 可以看到 Materialize 节点出现在 CTE 物化时。

6.4 递归 CTE 的执行流程是怎样的？如何防止无限递归？

一句话回答：递归 CTE 先执行初始查询存入 WorkTable，然后迭代执行递归部分，每次以上一轮输出作为输入，直到递归部分返回空行；通过 UNION 的去重特性或 CYCLE 子句防止环路无限递归。

详细解释（配代码与执行计划）：

示例：组织架构查询（无环）

WITH RECURSIVE emp_tree AS (
    -- 初始查询：根节点 (CEO)
    SELECT id, name, manager_id, 1 AS level
    FROM employee
    WHERE manager_id IS NULL
  UNION ALL
    -- 递归部分：下属
    SELECT e.id, e.name, e.manager_id, et.level + 1
    FROM employee e
    JOIN emp_tree et ON e.manager_id = et.id
)
SELECT * FROM emp_tree;

迭代过程详细表：

迭代次数WorkTable 内容（上轮输出）递归查询输出（本轮新增）
1 (初始)[CEO](无, 仅初始存入最终结果)
2[CEO][直接下属 A, B]
3[直接下属 A, B][A的下属, B的下属]
.........
N某层员工0 行 → 终止

EXPLAIN ANALYZE 解读：

CTE Scan on emp_tree  (actual time=0.01..0.08 rows=100 loops=1)
   CTE emp_tree
     Recursive Union  (actual time=0.00..0.06 rows=100 loops=1)
       ->  Index Scan using idx_manager_id on employee  (actual time=0.01..0.01 rows=1)  # 初始
       ->  Hash Join  (actual time=0.01..0.01 rows=30 loops=3)   # 递归部分，loops=3表示迭代轮数
             Hash Cond: (e.manager_id = et.id)
             ->  Seq Scan on employee e  (actual time=0.00..0.00 rows=100)
             ->  Hash  (actual time=0.00..0.00 rows=10)
                   ->  WorkTable Scan on emp_tree et  (actual time=0.00..0.00 rows=10 loops=3)
  • WorkTable Scan 代表每一轮读取上一轮的结果集。
  • loops=3 表示递归执行了3轮，说明树深度为3。

防止无限递归的方法：

  1. 使用 UNION（自动去重）：如果图中存在环路，UNION 会因去重而阻止同一行被无限次加入新结果。但去重需要排序/哈希，开销大。

  2. 显式路径检测（兼容旧版本）：

    WITH RECURSIVE graph_path AS (
    SELECT node_id, parent_id, ARRAY[node_id] AS path
    FROM graph WHERE node_id = 1
    UNION ALL
    SELECT g.node_id, g.parent_id, gp.path || g.node_id
    FROM graph g
    JOIN graph_path gp ON g.parent_id = gp.node_id
    WHERE NOT g.node_id = ANY(gp.path)   -- 关键：若节点已在路径中，跳过
)
SELECT * FROM graph_path;

  3. PG 14+ CYCLE 子句（推荐）：

    WITH RECURSIVE graph_path AS (
    SELECT node_id, parent_id, 1 AS depth
    FROM graph WHERE node_id = 1
    UNION ALL
    SELECT g.node_id, g.parent_id, gp.depth + 1
    FROM graph g
    JOIN graph_path gp ON g.parent_id = gp.node_id
)
CYCLE node_id SET is_cycle USING path
SELECT * FROM graph_path WHERE NOT is_cycle;
    • CYCLE 子句自动维护一个哈希集合，记录 (node_id) 是否已访问，一旦重复则设置 is_cycle 为 true，并在该迭代中不继续扩展。

多角度追问：

  1. UNION 和 UNION ALL 在递归 CTE 中的使用差异？为什么去重能防环？

    • UNION 会对每轮结果集去重，若图有环，同一节点在后续轮次会再次产生，但去重后不会作为新行加入中间表，从而终止环路扩展。代价是去重操作本身（排序/哈希）。UNION ALL 更快但必须手工防环。

  2. 如果递归 CTE 中不加环路检测，PG 有什么保护机制？

    • 参数 max_recursion_depth 限制最大迭代次数（默认 200）。若环路导致无限递归，会在达到深度上限时报错：ERROR: recursive query "cte" exceeded max_recursion_depth。

  3. 如何调优一个深度很大的递归查询（如 1000 层组织架构）？

    • 调大 max_recursion_depth：SET max_recursion_depth = 2000;。
    • 确保递归连接条件的列有索引（如 employee(manager_id)），避免每轮全表扫描。
    • 考虑使用 UNION ALL 且手工限制深度（WHERE level < 1000），避免去重开销。

加分回答：

  • PG 递归 CTE 内部使用 Tuplestore 存储 WorkTable，当内存不足时会溢出到磁盘（temp_file）。
  • 递归 CTE 中可以使用 RETURNING 子句吗？不可以，递归部分必须是 SELECT。
  • 对比树形查询的其他方法（如 ltree 扩展、闭包表），递归 CTE 灵活性最高，但大数据量下性能可能不如预处理好的闭包表。

6.5 物化视图与普通视图的区别是什么？什么时候应该使用物化视图？

一句话回答：普通视图是保存的 SQL 查询，每次查询实时计算；物化视图将查询结果物理存储为表，查询快但数据可能过时。物化视图适用于读多写少、允许一定延迟、查询复杂且重复执行的场景。

详细解释：

代码示例：

-- 普通视图
CREATE VIEW order_stats_view AS
SELECT user_id, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY user_id, month;

-- 物化视图
CREATE MATERIALIZED VIEW order_stats_mv AS
SELECT user_id, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY user_id, month;

-- 查询性能对比
EXPLAIN ANALYZE SELECT * FROM order_stats_view WHERE user_id = 1;
-- 计划: 聚合 + 扫描 orders 表

EXPLAIN ANALYZE SELECT * FROM order_stats_mv WHERE user_id = 1;
-- 计划: Index Scan on order_stats_mv (如果创建了索引)

刷新机制：

  • 全量刷新：REFRESH MATERIALIZED VIEW order_stats_mv; — 会持有 ACCESS EXCLUSIVE 锁，阻塞读。
  • 并发刷新（需唯一索引）：REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv; — 不阻塞读。

适用场景：

场景使用物化视图？原因
每日销售日报报表✅ 是数据按天更新，可每日凌晨刷新
实时大盘（秒级延迟要求）❌ 否物化视图有延迟，用普通视图或流处理
ETL 中的中间聚合表✅ 是减少重复计算，加速下游查询
频繁执行的复杂统计分析（如用户画像）✅ 是预计算结果，查询毫秒级响应

多角度追问：

  1. 物化视图刷新期间，查询会看到什么数据？

    • 全量刷新：在 REFRESH 命令的事务提交前，查询仍看到旧数据；提交后瞬间切换至新数据，但切换过程中有短暂阻塞。
    • 并发刷新：在整个刷新过程中，查询始终看到旧数据（因为新数据通过增量 DML 提交，而且 MVCC 保证一致性）。

  2. 并发刷新（CONCURRENTLY）需要什么前置条件？为什么？

    • 必须存在一个唯一索引（可以是多列）。原因是 PG 需要唯一标识每一行，以计算与临时表的差异（哪些行需 INSERT/DELETE/UPDATE）。若无唯一索引，无法安全地逐行更新。

  3. 如果物化视图定义中包含窗口函数或递归 CTE，可以建唯一索引吗？

    • 可以，只要结果集中的某列或列组合具有唯一性（例如 (user_id, month)）。如果原查询本身不保证唯一，可能需要在外层包裹 DISTINCT 或聚合来制造唯一键。

加分回答：

  • PG 的物化视图不支持增量刷新（只能全量重建或基于差异的并发刷新）。如需真正的增量刷新（只应用基表的变化），可考虑第三方扩展 pg_ivm。
  • 物化视图也是有存储的表，会受 MVCC 影响：大量刷新会产生死元组，需定期 VACUUM。

6.6 LAG 和 LEAD 函数的内部是如何实现的？它们在什么场景下使用？

一句话回答：LAG/LEAD 基于已排序的分区，通过指针偏移直接访问当前行之前或之后的行，常用于计算环比、同比、会话时长等需要比较相邻行值的场景。

详细解释（含内部数据结构模拟）：

内部实现原理：

  • 在 WindowAgg 节点中，对每个分区排序后，将行的表达式值（或整行）存储在一个数组或内存表中。
  • 执行器维护当前行的索引 i。LAG(column, offset) 通过访问 array[i - offset] 获取值；LEAD 通过 array[i + offset]。
  • 若偏移超出边界，返回 default（或 NULL）。时间复杂度 O(1)，空间复杂度 O(分区大小) 用于存储窗口帧。

代码示例：环比增长

WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', sale_date)::DATE AS month, SUM(amount) AS total
    FROM sales
    GROUP BY month
)
SELECT month, total,
       LAG(total, 1) OVER (ORDER BY month) AS prev_month_total,
       (total - LAG(total, 1) OVER (ORDER BY month)) / LAG(total, 1) OVER (ORDER BY month) * 100 AS growth_rate
FROM monthly_sales
ORDER BY month;

执行计划：

WindowAgg  (actual time=0.05..0.10 rows=12 loops=1)
   ->  Sort  (actual time=0.04..0.05 rows=12 loops=1)
         Sort Key: month
         ->  Subquery Scan on monthly_sales  (actual time=0.02..0.03 rows=12)
  • WindowAgg 内部维护一个指针，无需额外排序（已经由 Sort 节点完成）。

适用场景：

  • 环比/同比（与上一周期比较）
  • 会话行为分析（如用户连续操作的时间差：LEAD(timestamp) - timestamp）
  • 计算当天与前一天的库存变化
  • 漏斗分析（步骤之间的转化时长）

多角度追问：

  1. ORDER BY 对于 LAG/LEAD 是否必须？如果不提供 ORDER BY 会怎样？

    • 必须提供 ORDER BY，否则“前后行”无定义。如果省略，PG 会报错 ERROR: window function "lag" requires an ORDER BY clause。

  2. LAG 和自连接相比，性能优势在哪？

    • 自连接（如 LEFT JOIN sales prev ON prev.rownum = curr.rownum - 1）需要两表扫描 + 连接（可能全表扫描 + 排序），复杂度 O(N log N) 至 O(N^2)；窗口函数一次排序 + 单次扫描，复杂度 O(N log N) 但常数远小。

  3. 如何使用 LAG 计算用户会话的停留时间（每条记录有进入时间，下一记录为离开时间）？

    • 假设 user_log 表有 user_id, action_time, action_type（enter/exit）： 
      SELECT user_id, action_time AS enter_time,
       LEAD(action_time) OVER (PARTITION BY user_id ORDER BY action_time) AS exit_time,
       EXTRACT(EPOCH FROM (LEAD(action_time) OVER (PARTITION BY user_id ORDER BY action_time) - action_time)) AS duration_sec
FROM user_log
WHERE action_type = 'enter';

加分回答：

  • PG 中 LAG 和 LEAD 支持 IGNORE NULLS 和 RESPECT NULLS 选项（PG 11+），可跳过 NULL 值进行偏移。
  • 对于超大偏移量（如 LAG(col, 1000000)），性能会下降，因为需要随机访问远距离的行，可能导致更多内存/磁盘读取。

6.7 如何通过 EXPLAIN 判断递归 CTE 的性能瓶颈？

一句话回答：检查 Recursive Union 节点下的递归部分是否有 Seq Scan on WorkTable 且为全表扫描，以及递归部分的 Join 算法是否高效（应使用索引或 Hash Join），关注 loops 值和每行实际时间。

详细解释（配计划示例）：

性能瓶颈常见模式：

  1. 递归部分全表扫描：

    ->  Nested Loop  (actual time=0.05..2.30 rows=500 loops=10)
      ->  WorkTable Scan on cte  (actual time=0.00..0.01 rows=50 loops=10)   -- WorkTable 较小，正常
      ->  Seq Scan on big_table  (actual time=0.01..0.04 rows=10 loops=500)    -- 糟糕：对 WorkTable 每行都全表扫描 big_table

    应改为在 big_table 的连接条件列上建立索引，使其变为 Index Scan。

  2. WorkTable 自身膨胀：

    WorkTable Scan on cte  (actual time=0.01..15.30 rows=100000 loops=10)

    如果每轮 WorkTable 包含大量行（例如广度优先遍历且分支因子大），应检查是否使用了 UNION 去重以避免指数级膨胀。

  3. loops 异常高：

    Recursive Union  (actual time=0.01..823.40 rows=10000 loops=1)
  ->  Index Scan ...
  ->  Hash Join  (actual time=0.10..0.50 rows=500 loops=5000)   -- loops=5000 说明递归深度很大

    可能递归深度超过预期，需检查终止条件或数据中存在长链。

调优步骤：

  • 使用 EXPLAIN (ANALYZE, BUFFERS) 查看实际的 loops 和 rows。
  • 确保递归部分连接条件上的列有索引。
  • 考虑限制递归深度：WHERE level < 100。
  • 若使用 UNION，尝试改为 UNION ALL + 手工路径检测，以减少去重开销。

多角度追问：

  1. 为什么递归 CTE 的 WorkTable Scan 有时会使用 Hash Join？如何强制使用索引？

    • 如果 WorkTable 较大，优化器可能选择 Hash Join（构建 WorkTable 哈希表）而非 Nest Loop + 索引。要强制索引，可设置 enable_hashjoin=off 或增加 random_page_cost。生产环境不建议全局禁用，可以考虑改写查询或创建更合适的索引。

  2. 如果递归 CTE 使用 UNION 去重，EXPLAIN 中会看到什么额外节点？

    • 会看到 Unique 节点（排序去重）或 HashAggregate（哈希去重）位于递归部分之上，例如： -> Unique (actual time=... rows=... loops=...)

  3. 如何评估递归深度上限（max_recursion_depth）是否合理？

    • 基准测试：将 max_recursion_depth 设置为预期深度的 2 倍，观察执行时间和临时文件大小。若超过默认 200 且业务确实需要更深递归，再调大。

加分回答：

  • EXPLAIN (ANALYZE, TIMING, BUFFERS) 可以显示递归 CTE 每轮迭代的磁盘块命中与读取次数，帮助判断 WorkTable 是否溢出到磁盘。
  • 使用 track_io_timing=on 可以查看递归 CTE 的 I/O 等待时间。

6.8 REFRESH MATERIALIZED VIEW CONCURRENTLY 的内部原理是什么？它需要什么前提条件？

一句话回答：并发刷新基于唯一索引对比新旧结果集的差异，然后逐行执行 INSERT/UPDATE/DELETE，全程不阻塞读取查询；前提是物化视图上存在唯一索引。

详细解释（内部步骤与事务流程）：

内部伪代码流程：

BEGIN;
-- 1. 创建临时表，存储最新查询结果
CREATE TEMP TABLE tmp_mv AS SELECT ...;  -- 物化视图定义

-- 2. 基于唯一索引，对比物化视图和临时表
-- 对于每一行（根据唯一索引键）：
--   若存在于临时表但不存在于物化视图 → INSERT
--   若存在于物化视图但不存在于临时表 → DELETE
--   若两者都存在但其他列不同 → UPDATE
-- 这一步使用 INSERT ... ON CONFLICT, DELETE using 等实现

-- 3. 提交事务，新数据原子可见
COMMIT;

前提条件：

  • 物化视图必须有一个 唯一索引（可以是多列组合）。该索引用于快速定位和比较行。
  • 物化视图不能具有 WITH NO DATA 状态（即必须先创建并填充数据）。

为什么必须唯一索引？

并发刷新需要知道新旧两行是否代表同一逻辑实体。没有唯一索引，无法区分“某行被删除”和“某行新增但键不同”，可能导致数据重复或丢失。

示例：

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
FROM sales
GROUP BY product_id, month;

CREATE UNIQUE INDEX ON sales_summary (product_id, month);

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

多角度追问：

  1. 如果物化视图没有唯一索引，调用 REFRESH CONCURRENTLY 会怎样？

    • PG 报错：ERROR: cannot refresh materialized view "sales_summary" concurrently because it has no unique index.

  2. 并发刷新会影响物化视图上的其他索引吗？是否有碎片问题？

    • 其他索引也会随 DML 更新，产生索引碎片。频繁并发刷新可能导致索引膨胀，建议定期 REINDEX INDEX CONCURRENTLY（PG 12+）。

  3. 并发刷新的事务隔离级别是什么？是否可能产生幻读？

    • 默认 READ COMMITTED。刷新过程中，如果基表数据发生变化，临时表基于刷新开始时的一致性快照构建，因此不会出现幻读——最终物化视图反映的是刷新开始时刻的数据状态。

加分回答：

  • 并发刷新并不是完全无锁：它会在短暂时刻持有物化视图的 ROW EXCLUSIVE 锁，但读操作（SELECT）仍可进行（需要 ACCESS SHARE 锁，两者不冲突）。
  • 对于超大物化视图，并发刷新可能因为需要计算差异而比全量重建耗时更长，且生成的 WAL 日志更多（因为逐行 DML）。需根据实际场景权衡。

6.9 PG 的 CTE 与 MySQL 8.x 的 CTE 在行为上有何差异？

一句话回答：PG 12+ 默认非物化（内联），允许更灵活的优化；MySQL CTE 总是物化，无法内联；PG 支持显式控制物化，MySQL 不支持；PG 递归 CTE 支持 CYCLE 子句防环，MySQL 需手工处理。

详细解释（含对比表与执行计划示例）：

对比测试（PostgreSQL 16 vs MySQL 8.0）：

在相同数据（100万行订单表）上执行：

WITH high_value AS (
    SELECT user_id, SUM(amount) as total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 10000
)
SELECT o.* FROM orders o JOIN high_value h ON o.user_id = h.user_id;
  • PostgreSQL（默认非物化）：优化器将 high_value 内联，把 HAVING SUM(amount) > 10000 与主查询合并，可能选择对 orders 使用索引扫描，性能高。
  • MySQL（总是物化）：先完全执行 CTE 子查询，将结果写入临时表（使用磁盘或内存），然后再与 orders 连接。若 high_value 结果集很大，临时表 I/O 代价高。

执行计划差异（概念）：

  • PG：Nested Loop 直接连接 orders 和子查询聚合，无临时表。
  • MySQL：Table scan on high_value (temp table) + Nested Loop。

PG 的 CYCLE 子句 vs MySQL 手工防环：

MySQL 需要：

WITH RECURSIVE cte AS (
    SELECT id, parent_id, CAST(id AS CHAR(1000)) AS path FROM tree WHERE id = 1
    UNION ALL
    SELECT t.id, t.parent_id, CONCAT(cte.path, ',', t.id)
    FROM tree t JOIN cte ON t.parent_id = cte.id
    WHERE FIND_IN_SET(t.id, cte.path) = 0   -- 手工防环
)
SELECT * FROM cte;
  • 手工防环效率低（字符串查找），且 path 长度有限。

多角度追问：

  1. 从 MySQL 迁移到 PG，原来依赖 CTE 物化的查询需要注意什么？

    • 原来在 MySQL 中因为 CTE 总是物化，可能会无意间依赖此行为避免重复计算。迁移到 PG 后，若 CTE 被多次引用，应显式加 MATERIALIZED 保持原语义和性能。

  2. PG 中 NOT MATERIALIZED 可能带来什么风险？

    • 如果 CTE 被多次引用且复杂，强制内联会导致重复执行，使查询变慢。应仔细评估引用次数。

  3. MySQL 8.x 的递归 CTE 如何模拟 CYCLE 功能？

    • 使用路径字符串检测（如上面示例）或维护一个额外的访问集表（但无法在递归 CTE 内修改表）。路径字符串方法有长度限制（默认 group_concat_max_len）且性能差。

加分回答：

  • MySQL 的 CTE 物化行为与 PG 12 之前的版本相似。PG 之所以改变默认行为，是因为优化器足够成熟，能处理大部分内联优化。
  • 某些 MySQL 版本中，递归 CTE 的递归部分不能引用外层表的列，限制更大。

6.10 如何在递归 CTE 中遍历一个可能有环的图结构？

一句话回答：使用 PG 14+ 的 CYCLE 子句显式声明环路检测列，或手工维护一个访问路径数组并在递归部分检查新节点是否已存在于路径中。

详细解释（代码实例）：

有环图数据：

CREATE TABLE graph (from_node INT, to_node INT);
INSERT INTO graph VALUES (1,2), (2,3), (3,1);  -- 形成环路 1→2→3→1

方法一：PG 14+ CYCLE 子句（推荐）

WITH RECURSIVE path AS (
    SELECT from_node, to_node, ARRAY[from_node] AS visited, false AS is_cycle
    FROM graph
    WHERE from_node = 1
    UNION ALL
    SELECT g.from_node, g.to_node, visited || g.from_node,
           g.from_node = ANY(visited)   -- 手动检测也可，但 CYCLE 更简洁
    FROM graph g
    JOIN path ON g.from_node = path.to_node
    WHERE NOT is_cycle
)
CYCLE from_node SET is_cycle USING path   -- 内核检测环路，自动停止扩展循环分支
SELECT * FROM path WHERE NOT is_cycle;

方法二：手工路径数组（兼容旧版本）

WITH RECURSIVE path AS (
    SELECT from_node, to_node, ARRAY[from_node] AS path, false AS cyclic
    FROM graph WHERE from_node = 1
    UNION ALL
    SELECT g.from_node, g.to_node, p.path || g.from_node,
           g.from_node = ANY(p.path)
    FROM graph g
    JOIN path p ON g.from_node = p.to_node
    WHERE NOT cyclic
)
SELECT * FROM path WHERE NOT cyclic;

关键点：

  • ANY(p.path) 检查新节点是否已在路径中，若在则标记 cyclic，并在下一轮不再扩展。
  • 对于大型图，使用数组的 ANY 扫描可能较慢，可考虑 intarray 扩展或哈希存储。

多角度追问：

  1. CYCLE 子句检测环路的底层数据结构是什么？性能如何？

    • 使用哈希集合存储 (cycle_key) 的组合值，内存消耗与已访问的行数成正比。对于千万级节点，内存可能很大，但比数组 ANY 的线性扫描快得多。

  2. 如果图结构特别大（千万节点），递归 CTE 遍历的极限在哪里？

    • 主要受限于 work_mem 和递归深度。每轮迭代的 WorkTable 可能非常大，导致磁盘溢出，性能急剧下降。对于超大规模图，建议使用图数据库（如 Apache AGE 或 Neo4j）。

  3. 如何利用递归 CTE 查找图中的所有环路？

    • 需要对每个节点作为起点进行递归，并检查路径中是否出现重复节点。但这种方法会非常昂贵（O(N!)），实际中很少用 SQL 做全环路挖掘。

加分回答：

  • CYCLE 子句还支持 CYCLE ... SET ... USING ... DEFAULT 可以自定义循环标志的列名和值。
  • 在 PG 14 之前，可以使用 WITH RECURSIVE 和 pg_loop 插件来检测环路，但不如原生 CYCLE 方便。

6.11 窗口函数的 frame_clause 中 ROWS 和 RANGE 有什么不同？

一句话回答：ROWS 基于物理行的偏移，RANGE 基于逻辑值的偏移（如 ORDER BY 列的值相差范围）。

详细解释（带示例）：

数据与查询：

CREATE TABLE stocks (ticker TEXT, price NUMERIC, trade_date DATE);
INSERT INTO stocks VALUES ('AAPL', 100, '2025-01-01'),
                          ('AAPL', 102, '2025-01-02'),
                          ('AAPL', 102, '2025-01-03'),
                          ('AAPL', 103, '2025-01-04');

SELECT trade_date, price,
       AVG(price) OVER (ORDER BY trade_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS rows_avg,
       AVG(price) OVER (ORDER BY trade_date RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS range_avg
FROM stocks;

输出（以日期为逻辑值，但此处价格演示逻辑）：

trade_date | price | rows_avg | range_avg
2025-01-01 | 100   | 100      | 100
2025-01-02 | 102   | 101      | 101
2025-01-03 | 102   | 102      | 102
2025-01-04 | 103   | 102.5    | 102.5

（本例 RANGE 行为与 ROWS 相似，因为日期每天唯一。若日期重复，则不同。）

关键差异场景：当 ORDER BY 列有重复值时。 插入重复日期：

INSERT INTO stocks VALUES ('AAPL', 105, '2025-01-04');

现在有两个 2025-01-04。

  • ROWS BETWEEN 1 PRECEDING AND CURRENT ROW：仅取当前行和它紧邻的前一行物理行。
  • RANGE BETWEEN 1 PRECEDING AND CURRENT ROW：取当前行的 ORDER BY 列值（日期）减去 1 天到当前行的所有行。对于 2025-01-04，会包含所有 2025-01-03 和 2025-01-04 的行（如果 2025-01-04 有多行，全部包含）。

ROWS vs RANGE vs GROUPS 总结：

  • ROWS：物理偏移，严格按行号。
  • RANGE：逻辑偏移，包含所有排序值在区间内的行。
  • GROUPS：按排序值的“组”偏移（每个唯一值一组），PG 11 引入。

多角度追问：

  1. 在使用 RANGE 时，ORDER BY 列的数据类型有何限制？

    • 必须支持加减运算（例如数值、日期、时间戳）。对于文本类型，RANGE BETWEEN 'a' PRECEDING 无意义，会报错。

  2. ROWS UNBOUNDED PRECEDING 和 RANGE UNBOUNDED PRECEDING 何时结果不同？

    • 当排序列有重复值时，RANGE UNBOUNDED PRECEDING 会包含所有与当前行排序值相同的行（即使它们物理上在当前行之后？不，PRECEDING 仅向前，但相同值会全包含）。实际区别不大，因为 UNBOUNDED PRECEDING 总是从分区第一行开始，重复值可能导致窗口扩大，但最终结果相同（因为包含所有行）。更明显的差异体现在 BETWEEN n PRECEDING AND n FOLLOWING 中。

  3. 滑动窗口平均值（7 天移动平均）应该选 ROWS 还是 RANGE？

    • 选 RANGE。因为业务上按日期范围滑动（过去7天），而不是固定行数（若某天没有销售，ROWS 仍然会前进7行，可能跳过时间）。RANGE 确保时间窗口正确。

加分回答：

  • RANGE 不支持 CURRENT ROW 与数字偏移混合（如 RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING）在某些版本中有限制，最好使用 ROWS 或 GROUPS。
  • 默认的 frame_clause 是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

6.12 （系统设计题，深化版）电商平台数据分析系统

需求回顾：

  1. 每个商品类别的月度销售额排名（Top 10）。
  2. 用户近 30 天的下单金额移动平均。
  3. 组织架构中某个主管的所有下属（含间接下属）。

一句话回答（总体设计）：使用窗口函数实现月度品类排名和用户移动平均，使用递归 CTE 查询组织架构树，配合物化视图预聚合排名数据加速报表查询。

详细设计方案（含完整 SQL 与索引策略）：

1. 数据库表结构：

-- 订单表（分区按月，按需）
CREATE TABLE orders (
    order_id     BIGSERIAL PRIMARY KEY,
    user_id      INT NOT NULL,
    product_id   INT NOT NULL,
    amount       NUMERIC(12,2),
    order_date   DATE NOT NULL
);

-- 产品表
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    category_id  INT NOT NULL,
    product_name TEXT
);

-- 商品类别表
CREATE TABLE categories (
    category_id  INT PRIMARY KEY,
    name TEXT UNIQUE
);

-- 员工表（组织架构）
CREATE TABLE employee (
    emp_id      INT PRIMARY KEY,
    name        TEXT NOT NULL,
    manager_id  INT REFERENCES employee(emp_id)
);

2. 功能1：每个类别的月度销售额 Top 10

SQL 实现：

-- 原始查询
WITH monthly_category_sales AS (
    SELECT 
        c.name AS category,
        DATE_TRUNC('month', o.order_date)::DATE AS month,
        SUM(o.amount) AS total_sales
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    JOIN categories c ON p.category_id = c.category_id
    GROUP BY c.name, month
),
ranked_sales AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY month ORDER BY total_sales DESC) AS rank
    FROM monthly_category_sales
)
SELECT * FROM ranked_sales WHERE rank <= 10;

性能优化方案：创建物化视图 + 唯一索引，每日刷新。

CREATE MATERIALIZED VIEW mv_category_monthly_sales AS
SELECT 
    c.category_id,  -- 加入 category_id 以便索引
    DATE_TRUNC('month', o.order_date)::DATE AS month,
    SUM(o.amount) AS total_sales
FROM orders o
JOIN products p ON o.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_id, month;

CREATE UNIQUE INDEX ON mv_category_monthly_sales (month, category_id);
CREATE INDEX ON mv_category_monthly_sales (month, total_sales DESC);  -- 加速排名

-- 刷新任务（psql 或 cron）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_category_monthly_sales;

查询物化视图获取 Top 10：

SELECT category_id, month, total_sales,
       ROW_NUMBER() OVER (PARTITION BY month ORDER BY total_sales DESC) AS rank
FROM mv_category_monthly_sales
WHERE rank <= 10;

3. 功能2：用户近 30 天下单金额移动平均

SQL 实现（假设 orders 表有 user_id, order_date, amount）：

SELECT 
    user_id,
    order_date,
    amount,
    AVG(amount) OVER (PARTITION BY user_id 
                      ORDER BY order_date 
                      RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW) AS moving_avg_30d
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'  -- 限制时间范围，避免全表
ORDER BY user_id, order_date;

索引策略：

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date) INCLUDE (amount);
  • INCLUDE (amount) 可加速索引扫描（避免回表），但需 PG 11+。

执行计划验证：

EXPLAIN (ANALYZE, BUFFERS) 
SELECT ... (上述查询);
-- 应看到 Index Scan using idx_orders_user_date，且无额外 Sort。

4. 功能3：组织架构下属查询（递归 CTE）

SQL：

WITH RECURSIVE team AS (
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employee
    WHERE emp_id = ?   -- 给定主管 ID
    UNION ALL
    SELECT e.emp_id, e.name, e.manager_id, t.level + 1
    FROM employee e
    JOIN team t ON e.manager_id = t.emp_id
)
SELECT * FROM team;

索引：

CREATE INDEX idx_employee_manager ON employee(manager_id);

防深递归保护：

-- 限制层级深度
...
WHERE t.level < 20

5. 综合性能考量：

  • 对大表 orders 按月分区（声明式分区），移动平均查询可只扫描最近分区。
  • 使用 pg_cron 或 pg_timetable 定时刷新物化视图（每天凌晨）。
  • 设置合理的 work_mem 以容纳窗口函数的排序操作（对于千万级订单，可能需要 GB 级内存，可以调高但注意并发）。

多角度追问：

  1. 如果订单表每天新增百万级数据，如何设计增量更新物化视图？

    • 原生 PG 物化视图不支持增量更新。可改用 增量物化视图扩展（pg_ivm），或自己维护汇总表（通过触发器或逻辑复制）。业务上可改为按天分区，每日重建当天分区的物化视图。

  2. 移动平均查询中，使用 RANGE 基于日期和 ROWS 基于行数有什么不同？哪个更准确？

    • RANGE 基于日期值，更准确反映真实的“近30天”业务含义；ROWS 基于固定行数，若某天订单稀疏或密集，时间窗口会偏离。因此建议用 RANGE。

  3. 组织架构递归 CTE 如果深度很大（如 100 级），如何避免递归部分每轮全表扫描？

    • 确保 employee(manager_id) 有 B-Tree 索引，这样每轮递归能以 Index Scan 方式获取下属，复杂度 O(深度 * log N)。否则全表扫描会导致 O(深度 * N)。

加分回答：

  • 对于超大规模组织架构（百万员工），可以考虑使用 闭包表（Closure Table）预存储所有祖先-后代关系，查询为单表扫描，但维护成本高。
  • 移动平均查询中，如果 RANGE 使用 INTERVAL '29 days'，注意 PG 中 date 类型不支持直接与数字加减，需先转 timestamp。示例中的 order_date 建议使用 timestamptz 以获得精确间隔。

PostgreSQL 高级查询速查表

特性核心语法适用场景性能注意事项与 MySQL 差异
窗口函数func() OVER(PARTITION BY ... ORDER BY ... frame)排名、累计、环比、移动平均需排序，利用索引避免显式 Sort；WindowAgg 节点在排序后执行MySQL 不支持 GROUPS 帧
ROW_NUMBERROW_NUMBER() OVER(...)去重、分页编号、Top-N最快，无重复比较相同
RANK / DENSE_RANKRANK() OVER(...)排行榜（跳号/不跳号）需比较排序键，略慢于 ROW_NUMBER相同
LAG / LEADLAG(col, offset, default) OVER(...)环比、同比、前/后行值依赖 ORDER BY，偏移访问 O(1)相同
CTEWITH cte AS (SELECT ...) SELECT ...模块化复杂查询、重复引用中间结果PG12+ 默认非物化，多次引用建议 MATERIALIZED；否则重复计算MySQL 总是物化
递归 CTEWITH RECURSIVE cte AS (initial UNION ALL SELECT ... FROM cte ...)树形结构、图遍历、BOM必须索引递归连接列；注意 max_recursion_depth；CYCLE 防环MySQL 无 CYCLE，需手工防环
物化视图CREATE MATERIALIZED VIEW ... AS SELECT ...报表加速、预聚合REFRESH CONCURRENTLY 需唯一索引；全量重建锁表MySQL 不支持物化视图
并发刷新REFRESH MATERIALIZED VIEW CONCURRENTLY高并发读场景下的快照更新需唯一索引；差异计算开销大；写放大MySQL 不支持

延伸阅读

  • PostgreSQL 官方文档：Window Functions, CTE, Materialized Views
  • 《PostgreSQL: The Definitive Guide》第 10、11、14 章
  • PostgreSQL 源码：src/backend/executor/nodeWindowAgg.c（窗口函数执行器）、src/backend/executor/nodeCtescan.c（CTE 扫描）
  • 系列前文：第 5 篇《PostgreSQL 索引深度剖析》、第 6 篇《MVCC 与 VACUUM 机制》、第 7 篇《查询优化与执行计划深度》
avatar
文章
阅读
粉丝