窗口函数与 CTE 与递归

• 帧排除选项
• 与帧边界的关系
• SQL:2016 特性

EXCLUDE 子句控制帧内是否排除某些行。EXCLUDE CURRENT ROW 排除当前行；EXCLUDE TIES 排除与当前行在 ORDER BY 上"平局"（同值）的行，但保留当前行自己；EXCLUDE GROUP 排除当前行及其所有平局行（即整个 peer 组）；EXCLUDE NO OTHERS（默认）不排除任何行。这些选项用于精细控制聚合/窗口计算的帧内容，例如移动平均去抖时排除当前行或平局组。EXCLUDE 是 SQL:2016 特性，PostgreSQL 支持。

EXCLUDE 决定帧内待处理行的子集。EXCLUDE TIES 保留当前行但去掉同值 peer，EXCLUDE GROUP 去掉整个 peer 组，EXCLUDE CURRENT ROW 只去掉当前行。

• ROWS 帧按行号
• RANGE 帧按值范围
• 平局处理

ROWS 帧按物理行号定义边界，如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示从第一行到当前行（含当前行），只看行位置，与值无关。RANGE 帧按值定义边界，如 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示从第一行到与当前行同值的所有行（当前行及其平局行），即值相同的 peer 行都包含。RANGE 帧要求 ORDER BY 列，且平局行全部纳入。ROWS 更精确，RANGE 更符合"同值整体"语义。

ROWS 按行号，RANGE 按值/peer 组。RANGE 中当前行会包含所有同值行，因此累计和可能跳变。

• 四种排名函数
• 平局处理
• 排名间隔

四个排名函数行为：ROW_NUMBER 给每行唯一连续编号（即使排序键相同也按行序编号，无平局处理）；RANK 相同排序键值给相同排名，但排名有间隔（如 1,1,3）；DENSE_RANK 相同值给相同排名且无间隔（如 1,1,2）；NTILE(n) 把有序结果分成 n 个桶，为每行分配桶号（1..n）。RANK 与 DENSE_RANK 区别在平局后是否跳号，RANK 跳号、DENSE_RANK 不跳号。NTILE 用于分桶/分位数。

ROW_NUMBER 唯一编号，RANK 有间隔并列，DENSE_RANK 无间隔并列，NTILE 分桶。平局时 RANK 与 DENSE_RANK 不同。

• 窗口函数执行阶段
• 与 GROUP BY 的顺序
• 过滤顺序

SQL 逻辑执行顺序中，窗口函数在 GROUP BY 与聚合之后、ORDER BY 之前计算。因此若同时使用 GROUP BY 和窗口函数，先按 GROUP BY 分组聚合，再对聚合结果应用窗口函数。过滤顺序：WHERE 先过滤行，再 GROUP BY 分组、聚合，然后 HAVING 过滤组，之后才计算窗口函数。标准上：WHERE → GROUP BY → 聚合 → HAVING → 窗口函数 → ORDER BY。因此窗口函数的结果不能被 HAVING 引用（HAVING 在窗口函数之前执行），通常需套子查询/CTE 才能对窗口结果过滤。

窗口函数在分组聚合之后计算。若要基于窗口结果过滤，需先算窗口再外层过滤（子查询/CTE）。

• 窗口内 ORDER BY
• 语句级 ORDER BY
• 执行顺序

窗口函数中的 ORDER BY（OVER 子句内）用于定义窗口内的排序，决定帧与排名计算的基础，在窗口函数计算阶段执行；语句级 ORDER BY 在窗口函数之后执行，用于最终结果排序。两者独立：窗口 ORDER BY 只影响窗口计算，语句级 ORDER BY 只影响最终输出顺序。执行顺序：窗口函数（含窗口内排序）→ 语句级 ORDER BY。因此窗口 ORDER BY 与最终 ORDER BY 可以不同。

窗口内 ORDER BY 定义窗口计算，语句级 ORDER BY 定义输出。两者独立，先后执行。

• PARTITION BY 分区
• 减少排序范围
• 分区裁剪

在窗口函数中，PARTITION BY 把数据划分为多个分区，每个分区独立计算窗口函数。对大表性能的影响：分区可减少每个分区的排序范围（每个分区内排序，而非全表排序），从而降低排序代价与内存；若分区键与表的分区/索引一致，可实现分区裁剪（Partition Pruning），只处理相关分区。但分区数过多时每个分区很小，调度开销可能上升；分区过少则排序范围大。合理选择 PARTITION BY 键可显著提升窗口函数性能。

PARTITION BY 把大任务拆成小区间，减少排序规模。结合分区裁剪可减少 IO。键选择影响性能平衡。

• SQL 逻辑执行顺序
• 窗口函数阶段
• 与 DISTINCT/ORDER BY 的关系

窗口函数在逻辑执行顺序中位于聚合之后、DISTINCT 之前或 SELECT 投影阶段。完整顺序：FROM → WHERE → GROUP BY → 聚合 → HAVING → 窗口函数 → SELECT（投影）→ DISTINCT → ORDER BY → LIMIT。窗口函数在 SELECT 列表阶段计算，但逻辑上在 GROUP BY/聚合之后、DISTINCT 与 ORDER BY 之前。因此窗口结果不能在 WHERE/HAVING 中引用，但可在 ORDER BY 中引用（多数数据库允许）。DISTINCT 在窗口后，若窗口函数与 DISTINCT 同用，DISTINCT 会去重已计算的窗口结果。

窗口函数在 SELECT 阶段计算，位于 GROUP BY/聚合之后、DISTINCT/ORDER BY/LIMIT 之前。这是理解窗口函数可用位置的关键。

• 窗口函数执行阶段
• WHERE 的过滤
• 报错原因

不能。窗口函数不能出现在 WHERE 子句中，因为执行顺序上 WHERE 在窗口函数之前执行（WHERE 过滤行，窗口函数在 SELECT 阶段计算），此时窗口结果尚未计算。若要在 WHERE 中基于窗口结果过滤（如只保留每组第一行），需先在一个子查询/CTE 中计算窗口函数，再在外层 WHERE 中过滤（如 WHERE rn = 1）。同理，窗口函数也不能直接用于 HAVING。

因为窗口函数在 SELECT 阶段才计算，WHERE/HAVING 已过。要过滤窗口结果必须套子查询/CTE。

• OVER 子句
• PARTITION BY
• frame_clause

窗口函数基本语法：func() OVER (PARTITION BY ... ORDER BY ... frame_clause)。PARTITION BY 把数据划分为分区，各分区独立计算；ORDER BY 定义分区内排序；frame_clause（窗口帧）定义"当前行窗口"的边界，如 ROWS BETWEEN ... AND ...，决定聚合/排名作用范围。三部分都可选：无 PARTITION BY 则全表一个分区；无 ORDER BY 则默认整个分区为帧（对聚合函数）；frame_clause 默认 RANGE UNBOUNDED PRECEDING TO CURRENT ROW（若有 ORDER BY）。窗口函数包括排名、聚合、偏移等。

PARTITION BY 分区、ORDER BY 排序、frame 定帧。三者共同决定窗口函数计算范围。

• 聚合窗口函数
• 累计计算
• 帧的作用

聚合函数（SUM、AVG、COUNT）可作窗口函数使用，带上 OVER 后按窗口帧做累计计算。例如 SUM(col) OVER (ORDER BY id) 表示从第一行到当前行（默认帧 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）的累计和（running total）；SUM(col) OVER (PARTITION BY g ORDER BY id) 是分区内累计和。AVG OVER 是累计平均，COUNT OVER 是累计计数。帧边界决定累计范围，无 ORDER BY 时整个分区为一个帧，聚合结果为分区总值。

聚合窗口函数利用帧做滑动/累计聚合。ORDER BY 加默认帧即得累计和。这是移动/累计统计的核心。

• 分布式窗口函数
• shuffle 分区
• 单分区瓶颈

分布式引擎（Spark、Trino、Flink）并行化窗口函数时，按 PARTITION BY 键对数据做 shuffle，把相同分区键的行发送到同一节点，然后在各节点内对该分区排序并计算窗口函数，从而并行处理多个分区。若查询缺少 PARTITION BY（或分区键分布不均），所有数据都进入一个分区，只能由单节点处理，形成数据倾斜与单节点瓶颈，无法并行。因此分布式窗口函数应尽量指定合理的 PARTITION BY，使分区均匀分布到各节点。

shuffle 是分布式窗口函数的关键。无 PARTITION BY 则单分区单节点，无法并行，是性能瓶颈。

• GROUPS 帧模式
• 按 peer 组数计边界
• 去抖场景

GROUPS 帧模式按"peer 组"（排序值相同的行组）的数量定义边界，而非行号（ROWS）或值（RANGE）。例如 GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示当前行所在 peer 组及其前后各一个 peer 组。与 ROW 相比，同一 peer 组整组纳入，避免只取组内部分行导致的抖动。移动平均去抖（smoothing）常用 GROUPS + EXCLUDE GROUPS：因为按 peer 组计算平均值时，若把同一时刻的平局行拆开计算会产生抖动，用 GROUPS 把整组作为单位、EXCLUDE GROUPS 排除同值组，可获得稳定的滑动平均。

GROUPS 以 peer 组为边界单位，整组纳入，避免同值行被拆分。EXCLUDE GROUPS 排除当前 peer 组，用于去抖移动平均。

• SELECT 中使用窗口函数
• ORDER BY 中使用
• 位置限制

窗口函数既可以在 SELECT 列表中使用，也可以在 ORDER BY 中使用。SELECT 列表是窗口函数最常见的位置（如 SELECT row_number() OVER (...), SUM(...) OVER (...)）。ORDER BY 中也可以引用窗口函数（Oracle 允许，PostgreSQL 也允许在 ORDER BY 中写窗口函数表达式，如 ORDER BY row_number() OVER (...)，或按窗口计算的别名排序）。但窗口函数不能用于 WHERE、GROUP BY、HAVING 子句（执行阶段早于窗口计算）。

窗口函数可用在 SELECT 与 ORDER BY，不能在 WHERE/GROUP BY/HAVING。这是标准允许的位置。

• OLAP 与 OLTP 特点
• 窗口函数开销
• 适用场景

窗口函数是 OLAP（分析型）利器，因为它能在一趟扫描内完成排名、累计、移动、分组 TopN 等复杂分析，适合批量、大范围数据的分析查询。但 OLTP（事务型）不适用，原因：OLTP 是高频、小范围、单行读写，强调低延迟与高并发；窗口函数通常需要扫描并排序大范围数据、消耗大量内存与 CPU，且无法利用单一索引快速定位单行，延迟高、成本大，不适合作为 OLTP 的常规操作。窗口函数更适合分析报表、数据挖掘等离线场景。

OLAP 批量分析用窗口函数高效，OLTP 单行高频不适用因为窗口函数需全量排序扫描，延迟高。

• NULL 排序位置
• NULLS FIRST/LAST
• 默认行为

窗口函数的 ORDER BY 可用 NULLS FIRST 或 NULLS LAST 指定 NULL 值的排序位置。默认行为：PostgreSQL 中升序时 NULL 排最后（NULLS LAST），降序时 NULL 排最前（NULLS FIRST）；MySQL 中 NULL 升序默认排最前。显式指定可控制 NULL 在帧/排名中的位置，影响排名函数（如 ROW_NUMBER 对 NULL 行）与累计计算。例如 ORDER BY salary DESC NULLS LAST 让 NULL 工资排最后。

NULLS FIRST/LAST 控制 NULL 排序位置，影响窗口计算。默认行为因数据库与升降序而异，显式指定更稳妥。

• work_mem 与排序
• 落盘
• WINDOW 定义复用

窗口函数执行时，WindowAgg 节点通常需要先按 PARTITION BY 和 ORDER BY 对数据排序（Sort 节点），排序在 work_mem 内进行，若数据量超过 work_mem 则溢出到临时文件（落盘），导致大量磁盘 IO 与性能下降。若排序的是大分区，内存压力更大。多个窗口函数若共用相同的 PARTITION BY/ORDER BY 定义，可把它们合并为一次排序，排序结果复用，避免为每个窗口重复排序。因此用 WINDOW w AS (...) 命名公共窗口定义并用 OVER w 引用，可减少排序次数。

排序耗 work_mem，超限落盘。复用 WINDOW 定义让多个窗口共享一次排序，减少排序开销。

• 分组 vs 分区
• 行数保留
• 结果粒度

GROUP BY 把多行合并为一行（每组输出一行），且只能输出分组键或聚合结果，原始行被压缩；PARTITION BY（窗口函数）不合并行，每行都保留并输出一行，只是把数据划分为分区，在每个分区内计算窗口函数（如排名、累计），每行仍独立存在。GROUP BY 是"分组压缩"，PARTITION BY 是"分区计算但保留每行"。这是两者本质区别。

GROUP BY 压缩行数，PARTITION BY 保留每行只分区计算。结果粒度不同是核心差异。

• row_number 语义
• 无 PARTITION BY
• 全局编号

SELECT row_number() OVER (ORDER BY id) FROM t 按 id 对整个表排序，然后为每行分配从 1 开始的连续唯一编号（无 PARTITION BY，全表一个分区）。由于 id 通常唯一，编号按 id 升序为 1,2,3,...。若 id 有重复，row_number 仍给每行唯一编号（顺序在平局内不确定）。该编号是每行唯一的行号，从 1 到总行数。

无 PARTITION BY 即全表一个分区，row_number 按 ORDER BY id 给全局唯一序号。

• 有无 PARTITION BY
• 累计范围
• 分区累计

SUM(col) OVER (ORDER BY id) 无 PARTITION BY，全表一个分区，按 id 排序后从第一行到当前行累计（running total），是一个全局累计和。SUM(col) OVER (PARTITION BY id ORDER BY id) 按 id 分区，每个 id 值独立分区，每个分区内按 id 排序累计。由于分区键就是 id，每个分区只含一个 id 值（一行），所以每个分区的累计和就是该行自身的 col 值（若 id 唯一）。区别核心：有无 PARTITION BY 决定累计范围是全局还是分区内。

无 PARTITION BY 是全局累计，有 PARTITION BY 是分区累计。分区键若唯一则每分区一行。

• ORDER BY 可选
• 默认帧
• 排名函数需 ORDER BY

ORDER BY 在窗口函数中是可选的。对于排名函数（ROW_NUMBER、RANK 等），ORDER BY 是必需的（否则无意义）。对于聚合窗口函数（SUM OVER 等），无 ORDER BY 时整个分区作为一个帧，返回分区总值（对每行都相同）；有 ORDER BY 时按帧累计算累计。因此 ORDER BY 可选，但缺省时影响帧语义：聚合函数取整个分区，排名函数语法上要求 ORDER BY。

ORDER BY 可选。排名函数必须 ORDER BY，聚合窗口函数无 ORDER BY 时整个分区为帧。

• TopN 分组
• 累计和
• 移动平均

窗口函数在 OLAP 中的典型应用：1) 分组 TopN，用 ROW_NUMBER()/RANK() OVER (PARTITION BY group ORDER BY value DESC) 取每组前 N 名；2) 累计和（running total），用 SUM() OVER (ORDER BY date) 计算累计值；3) 移动平均，用 AVG() OVER (ORDER BY date ROWS BETWEEN n PRECEDING AND CURRENT ROW) 计算 N 日移动平均；4) 同比/环比，用 LAG/LEAD 获取前后行；5) 排名、分桶（NTILE）。这些分析在 OLAP 报表中高频使用。

分组 TopN、累计、移动平均、环比是窗口函数四大典型分析场景，均在一趟扫描内完成。

• 组合使用
• 执行顺序
• 对聚合结果做窗口

可以。窗口函数可以与 GROUP BY 同时使用，此时先执行 GROUP BY 分组聚合，再对聚合结果应用窗口函数。例如 SELECT dept, COUNT() AS cnt, RANK() OVER (ORDER BY COUNT() DESC) FROM t GROUP BY dept 可对每个部门按计数排名。注意窗口函数作用于分组后的行（每个分组一行），因此窗口函数中可引用聚合结果（如 COUNT(*)）或分组键。这是先聚合后窗口的典型用法。

窗口函数在 GROUP BY/聚合之后执行，可对分组结果做窗口计算（如按聚合值排名）。两者可组合。

• UPDATE 与窗口函数
• 语法限制
• 子查询替代

PostgreSQL 的 UPDATE 语句中 SET 子句不能直接使用窗口函数（窗口函数只能在 SELECT 的 OVER 子句中使用）。但可以通过子查询/CTE 间接实现：先在一个子查询中计算窗口函数，再在 UPDATE 中引用。例如 UPDATE t SET rank = s.rn FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY salary) rn FROM t) s WHERE t.id = s.id。这样借由派生表把窗口结果应用到 UPDATE。

窗口函数不能直接写入 UPDATE SET，但可先子查询算窗口再 UPDATE 关联更新。

• CTE 与窗口函数
• 组合使用
• 外层过滤

可以。窗口函数可以在 CTE（WITH 子句）的查询中使用，作为 CTE 的输出列。例如 WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM emp) SELECT * FROM ranked WHERE rn = 1。这是"先算窗口再过滤"的经典模式，因为窗口结果不能在 WHERE 中直接使用，而 CTE 先物化/内联窗口结果，外层再过滤。CTE 是承载窗口函数结果的常用容器。

窗口函数可在 CTE 内计算，外层引用其结果。这是规避窗口函数不能用于 WHERE 限制的标准做法。

• CTE 物化
• Inline 内联
• PostgreSQL 12+ 行为

PostgreSQL 12+ 中，CTE 默认不物化（可被优化器内联进主查询，Inlining），除非：CTE 被多次引用、含递归、或有副作用（如 volatile 函数）时强制物化。物化（Materialized）会把 CTE 结果物化为临时结构，只执行一次，适合被多次引用或计算昂贵、预期结果小的情况；内联（Inline）则把 CTE 展开进查询，可被优化器进一步优化（如谓词下推、索引），但若 CTE 被多次引用则重复计算。AS MATERIALIZED 强制物化，AS NOT MATERIALIZED 强制内联。当下游多次引用同一昂贵 CTE 时用物化更优。

物化 vs 内联是"执行一次 vs 展开优化"的取舍。多次引用或昂贵计算用物化，单次引用且可下推用内联。

• 窗口函数 TopN
• LATERAL 子查询
• 性能对比

每组 TopN 有两种主流写法：窗口函数法用 ROW_NUMBER() OVER (PARTITION BY g ORDER BY v DESC) 再过滤 rn<=N；LATERAL 法用 LATERAL 关联子查询，对每个组取前 N 行：FROM t LEFT JOIN LATERAL (SELECT ... FROM t2 WHERE t2.g=t.g ORDER BY v DESC LIMIT N)。性能对比：窗口函数需一次全量排序，适合数据量中等；LATERAL 若每组行数少且有索引（g, v 联合索引）则每组的排序可用索引，性能好；若每组数据量大或索引缺失，LATERAL 逐组执行代价高。窗口函数实现更简洁、可读性好。

窗口函数全量排序、简洁；LATERAL 逐组 LIMIT，依赖索引生效时性能好。选择取决于数据规模与索引。

• CTE 语法
• 与子查询对比
• 可读性

CTE 基本语法：WITH cte AS (SELECT ...) SELECT ... FROM cte。它把子查询提取到前面命名，主查询直接引用。取舍：CTE 提升可读性、可复用（同一 CTE 多次引用）、便于递归（WITH RECURSIVE），并可让逻辑分层清晰；子查询更内联、无额外命名开销。性能上，PostgreSQL 中 CTE 默认可能内联，两者计划通常接近；但多次引用时 CTE 物化可避免重复计算。建议逻辑复杂、复用或递归时用 CTE，简单场景用子查询。

CTE 是命名子查询，优化可读性与复用。递归、复用、复杂逻辑用 CTE，简单用子查询。

• CYCLE 子句
• 防环
• SET USING 语法

PostgreSQL 14+ 支持 CYCLE 子句用于递归 CTE 的防环检测。语法：WITH RECURSIVE t AS (...) CYCLE col SET is_cycle USING path。CYCLE col 指定用于检测环的列（如节点 id），SET is_cycle 添加一个布尔列标记该行是否形成环，USING path 添加一个数组列记录访问路径。递归引擎沿路径检测，若某节点已在路径中（形成环）则标记 is_cycle=TRUE 并停止向下扩展，避免无限递归。

CYCLE 子句自动做环检测，比手写"历史节点数组"更简洁。is_cycle 标记环，path 记录路径。

• 12+ 默认内联
• 12 之前默认物化
• 语义变化

PostgreSQL 12 之前，CTE 默认总是物化（Materialized），即 CTE 结果先计算并物化，再供主查询引用，导致无法做谓词下推、外层 LIMIT 无法下推等优化，且多次引用时重复物化。PostgreSQL 12 起，普通 CTE 默认不再强制物化，优化器可将其内联（Inlining）进主查询，获得谓词下推、索引利用等优化；但含递归、多次引用、volatile 副作用等情况下仍会物化。12 之后还引入 AS MATERIALIZED / AS NOT MATERIALIZED 显式控制。这是默认物化语义的重大变化。

12 之前默认物化（优化受限），12+ 默认可内联（优化更好）。多次引用可显式 AS MATERIALIZED。

• SEARCH 子句
• 深度优先/广度优先
• 排序列

PostgreSQL 14+ 的 SEARCH 子句用于控制递归 CTE 的遍历顺序。SEARCH DEPTH FIRST BY col SET seq 表示深度优先（先沿一条路径深入再回溯），SEARCH BREADTH FIRST BY col SET seq 表示广度优先（逐层扩展）。SEARCH 子句会添加一个 seq 列表达遍历顺序，使结果按深度优先或广度优先排序。无 SEARCH 子句时递归顺序不确定。广度优先需先按层分组再排序，成本更高。

SEARCH DEPTH/BREADTH FIRST BY 控制遍历顺序，SET 列保存顺序号。深度优先可用一个 path 列实现，广度优先需额外排序。

• 多 CTE 语法
• 依赖关系
• 并行性

多个 CTE 可链式定义：WITH cte1 AS (...), cte2 AS (...) SELECT ...。后续 CTE 可以引用前面定义的 CTE（如 cte2 引用 cte1），形成依赖链。CTE 之间若存在依赖，则必须按依赖顺序定义（cte1 先于 cte2）。并行性：PostgreSQL 中 CTE 默认内联，多个 CTE 是否有并行取决于执行计划；若 CTE 被物化且被引用，可能串行执行。优化器可并行扫描不同 CTE 的底层表，但 CTE 之间的依赖会限制并行。整体上多 CTE 提升可读性、模块化，但需注意依赖顺序。

多 CTE 链式引用按依赖顺序定义。并行性由优化器决定，依赖链可能限制并行。

• 数据修改 CTE
• RETURNING
• 原子性

数据修改 CTE 允许在 WITH 子句中执行 INSERT/UPDATE/DELETE，并通过 RETURNING 返回被修改的行供主查询引用。例如 WITH deleted AS (DELETE FROM t WHERE ... RETURNING *) SELECT * FROM deleted。原子性：整个语句（含所有数据修改 CTE 与主查询）在一个事务中原子执行，要么全部成功要么全部失败。可见性：所有数据修改 CTE 和主查询共享同一快照，主查询能看到 CTE 中 RETURNING 的行，但数据修改 CTE 之间及主查询对表的修改遵循 MVCC 规则，同一语句内不可见其他修改 CTE 对同表的变更（除非 RETURNING 传递）。

数据修改 CTE 原子执行，主查询可引用 RETURNING 结果。同语句内多修改 CTE 的相互可见性受 MVCC 限制。

• 递归 CTE 结构
• 树/层级遍历
• 图遍历

递归 CTE 典型模式：WITH RECURSIVE cte AS (锚成员 SELECT 初始行 UNION ALL 递归成员 SELECT 连接 cte 与基表生成下一层) SELECT ... FROM cte。用于树遍历/组织层级：锚成员取根节点，递归成员按 parent_id 连接子节点，逐层展开；用于图遍历：锚成员取起始节点，递归成员沿边扩展邻居，配合路径数组防环。递归 CTE 实现组织树的层级展开、路径拼接、图可达性等。

锚成员 + 递归成员 + UNION ALL 是递归 CTE 骨架。层级/树/图遍历都基于此模式，图遍历需防环。

• 终止条件
• 无穷递归防护
• MAX_RECURSIONS

递归 CTE 的终止依赖递归成员在连接时不再产生新行（即没有新的可扩展节点），此时递归自然停止。若存在环（如自引用形成循环）或递归条件设计错误，会无限递归。防护方法：1) 在递归成员中记录路径/已访问节点，用条件跳过已访问节点（防环）；2) 使用 CYCLE 子句自动检测环；3) 设置递归深度上限，如 WHERE depth < 10；4) 使用方言限制：SQL Server 有 MAXRECURSION 提示（如 OPTION (MAXRECURSION 100) 超过则报错），PostgreSQL 无 MAX_RECURSIONS 参数但可用深度条件或 CYCLE 防环。

终止靠递归成员不再产生新行。防环用路径/深度限制/CYCLE 子句。SQL Server 用 MAXRECURSION 硬限制。

• 锚成员
• 递归成员
• UNION vs UNION ALL

递归 CTE 语法：WITH RECURSIVE cte AS (锚成员 UNION [ALL] 递归成员) SELECT ...。锚成员（anchor member）是递归的初始查询，返回第一层结果；递归成员（recursive member）引用 CTE 自身，从上一层结果连接基表生成下一层，直到不再产生新行。UNION 会去重（消除重复行），可防止部分重复但增加开销；UNION ALL 保留所有行（不去重），速度更快，是递归的常用选择。递归 CTE 必须用 UNION 或 UNION ALL 连接，不能用 INTERSECT/EXCEPT。

锚成员初始化、递归成员逐层扩展、UNION/UNION ALL 决定去重。递归 CTE 用 UNION ALL 更高效。

• 数据修改 CTE
• RETURNING 子句
• 链式操作

CTE 可用于 DML：INSERT ... RETURNING、UPDATE ... RETURNING、DELETE ... RETURNING 返回被影响的行。这些可以作为数据修改 CTE 的返回值，供主查询或其他 CTE 引用。例如 WITH ins AS (INSERT INTO t VALUES (...) RETURNING id) SELECT * FROM ins 返回新插入的 id。典型应用：插入后返回 id 供后续插入外键、删除后记录被删行、更新后返回新旧值。RETURNING 支持 * 或指定列，也支持表达式。

RETURNING 让 DML 返回受影响行，配合 CTE 可实现链式操作（插入后取 id 再关联）。

• 迭代次数
• 深度限制
• 内存消耗

递归 CTE 的性能取决于迭代次数（每层一次递归成员执行）与每层数据量。深度过大时迭代次数多，可能产生大量中间结果，内存消耗大（结果可能溢出到临时文件）。深度限制：PostgreSQL 无内置深度参数，需在递归成员中加 WHERE depth < N 限制；SQL Server 用 MAXRECURSION。内存消耗：递归 CTE 会累积所有层结果，某层爆炸（如完全图）会导致内存/临时空间耗尽。优化：用 UNION ALL 减少去重开销、限制深度、用索引加速连接条件。

递归 CTE 迭代次数与深度相关，内存随层累积。深度限制与索引连接是性能关键。

• CTE 允许集合操作
• 递归 CTE 限制
• 普通 CTE

普通 CTE 的查询可以包含 UNION、INTERSECT、EXCEPT 等集合操作，因为 CTE 就是一个命名查询。例如 WITH cte AS (SELECT ... UNION SELECT ...) SELECT * FROM cte。但递归 CTE 有特殊限制：递归 CTE 必须用 UNION 或 UNION ALL 连接锚成员与递归成员，且不支持 INTERSECT/EXCEPT 作为递归主体连接；递归成员本身内部可以有限制，但顶层连接必须是 UNION/UNION ALL。因此"CTE 含有 UNION/INTERSECT"一般允许，但递归 CTE 的递归连接只能用 UNION/UNION ALL。

普通 CTE 可用集合操作；递归 CTE 的锚/递归连接必须用 UNION/UNION ALL，不能用 INTERSECT/EXCEPT。

• 强制物化
• 场景判断
• 多次引用

AS MATERIALIZED 强制 CTE 物化，适合以下场景：1) CTE 结果被多次引用，物化只执行一次避免重复计算；2) CTE 计算昂贵（大量聚合、复杂操作）且结果较小，物化划算；3) CTE 含 volatile 函数或副作用，需只执行一次；4) 需要物化结果快照，避免内联后受主查询影响。反之，若 CTE 单次引用且可内联做谓词下推、索引利用，则 AS NOT MATERIALIZED 或默认内联更优。

物化 vs 内联的取舍：多次引用/昂贵计算/volatile 用物化，单次引用可下推用内联。

• 链式引用
• 依赖顺序
• 递归限制

可以。CTE 可以引用在同一 WITH 子句中先前定义的 CTE，形成链式依赖。例如 WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b。限制：只能引用前面定义的 CTE（不能引用后面的）；不能循环引用（A 引用 B、B 引用 A 会报错）；递归 CTE 只能自引用自身（且必须是 WITH RECURSIVE），不能引用其他 CTE 形成交叉递归。链式 CTE 提升可读性，但依赖必须保持无环。

链式 CTE 只能引用先前定义的 CTE，依赖必须无环。递归 CTE 只允许自引用。

• 默认内联
• 物化条件
• 12+ 行为

PostgreSQL 12+ 中，普通 CTE 默认不再强制物化，优化器可将其内联（Inline）进主查询，从而获得谓词下推、索引利用、连接顺序优化等收益。但以下情况强制物化（不内联）：CTE 被多次引用、CTE 含递归、CTE 含 volatile 函数或副作用（如 nextval）、CTE 含 LIMIT 等非可下推操作。优化器也可通过成本评估决定内联或物化。12 之前默认总是物化。可用 AS MATERIALIZED / AS NOT MATERIALIZED 显式覆盖。

12+ 默认可内联，但多次引用/递归/volatile/非可下推时强制物化。这是 PostgreSQL CTE 优化行为。

• 数据修改 CTE 可见性
• RETURNING
• 主查询引用

数据修改 CTE 通过 RETURNING 返回被修改的行，主查询（SELECT 部分）可以引用这些 RETURNING 结果。例如 WITH deleted AS (DELETE FROM t WHERE ... RETURNING *) SELECT * FROM deleted。主查询能看到 DELETE RETURNING 返回的行。但注意：主查询无法直接看到同一语句中数据修改 CTE 对表本身造成的修改（除非通过 RETURNING 传递），因为 SQL 语句内多个子语句的可见性遵循特定规则（主查询在语句开始时的快照上执行，数据修改的可见性通过 RETURNING 传递）。

主查询可引用数据修改 CTE 的 RETURNING 结果，但不直接看到对表的修改，需 RETURNING 传递。

• 闭环防环
• MAXRECURSION
• 终止策略

递归 CTE 终止的两种典型模式：1) 自引用闭环终止：递归成员通过连接条件不断扩展，直到不再产生新行（无环自然终止），或通过路径数组/已访问集合检测环，遇到已访问节点即停止，从而在图上收敛；2) MAX RECURSION 硬限制：在递归成员中设置深度上限（如 WHERE depth < N），或 SQL Server 用 OPTION (MAXRECURSION N) 强制限制迭代次数，超过则报错。前者依赖数据结构性终止，后者是安全兜底。

闭环终止靠数据无环或路径检测，MAXRECURSION 是强制深度上限。两者结合可防无限递归。

• GAPS/NO GAPS
• 帧边界
• 依赖实际值

GAPS 与 NO GAPS 是 SQL 标准中 RANGE 帧模式的选项，影响 RANGE 帧边界如何映射到实际存在的值。NO GAPS 表示帧边界直接对应实际存在的排序值；GAPS 表示帧边界允许落在"间隙"（不存在的值）上，即按值区间计算。例如 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING 时，GAPS 会按"当前值±1"的连续值区间包含所有行，NO GAPS 只包含实际存在的值范围内的行。注意 PostgreSQL 目前并未实现 GAPS/NO GAPS 关键字，其 RANGE 帧按实际存在的行计算。

GAPS/NO GAPS 控制 RANGE 帧边界是否按实际值映射。GAPS 按值区间（含间隙），NO GAPS 按实际存在值。PostgreSQL 未实现该关键字，RANGE 帧按实际行处理。

• LAG/LEAD 偏移
• FIRST/LAST_VALUE
• 边界处理

LAG(col, n, default) 返回分区内当前行前 n 行的值，LEAD(col, n, default) 返回后 n 行的值，n 默认 1，default 为越界（超出分区）时返回的默认值（默认 NULL）。FIRST_VALUE(col) 返回帧内第一行的值，LAST_VALUE(col) 返回帧内最后一行的值（注意：LAST_VALUE 默认帧只延伸到当前行，需显式设置 UNBOUNDED FOLLOWING 才能取到分区最后一行）。NTH_VALUE(col, n) 返回帧内第 n 行的值。边界处理：LAG/LEAD 越界返回 default；FIRST/LAST/NTH_VALUE 依赖帧范围。

LAG/LEAD 是偏移访问（前后 n 行），越界用 default。FIRST/LAST/NTH_VALUE 是帧内取值，依赖帧边界。

• 移动平均帧
• 累计和帧
• ROWS BETWEEN

移动平均（Moving Average）用 AVG() OVER (ORDER BY date ROWS BETWEEN n PRECEDING AND CURRENT ROW) 计算最近 n+1 期的平均值；累计和（Cumulative Sum）用 SUM() OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 计算从第一行到当前行的累计值。两者都依赖 ROWS 帧设置：移动平均用有限窗口（n PRECEDING），累计和用 UNBOUNDED PRECEDING。默认帧是 RANGE UNBOUNDED PRECEDING TO CURRENT ROW，对累计和适用，但移动平均需显式指定 ROWS。

移动平均用 ROWS BETWEEN n PRECEDING AND CURRENT ROW，累计和用 UNBOUNDED PRECEDING。帧是核心。

• WINDOW 子句
• 命名复用
• 减少排序

窗口定义可用 WINDOW 子句命名并复用。语法：SELECT ..., func() OVER w FROM t WINDOW w AS (PARTITION BY ... ORDER BY ...)。多个窗口函数可共用同一 WINDOW 定义：SUM(x) OVER w, AVG(y) OVER w。命名窗口还能在 OVER 中叠加修改（如 OVER (w ORDER BY col)），但 WINDOW 子句必须出现在 ORDER BY 之前。复用窗口定义减少重复书写，也便于优化器对共享同一窗口定义的函数复用排序，减少排序次数。

WINDOW w AS (...) 命名窗口定义，多个函数复用，减少重复与排序。语法上 WINDOW 子句在 ORDER BY 前。

• FIRST_VALUE 语义
• LAST_VALUE 语义
• 帧默认

FIRST_VALUE(col) 返回帧内第一行的值，LAST_VALUE(col) 返回帧内最后一行的值。默认帧是 RANGE UNBOUNDED PRECEDING TO CURRENT ROW，因此：FIRST_VALUE 始终返回分区第一行（不受影响）；LAST_VALUE 默认只返回当前行（因为帧到当前行结束），往往不是分区最后一行。若想 LAST_VALUE 返回整个分区最后一行，需显式设置帧 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。ORDER BY 不同时帧边界不同，导致 LAST_VALUE 结果不同。

FIRST_VALUE 是分区第一行，LAST_VALUE 默认只到当前行，需显式 UNBOUNDED FOLLOWING 才取分区最后一行。

• LAG 参数
• 偏移量
• 默认值

LAG(col, 1, 0) 的三个参数：col 是要求值的列；1 是偏移量（offset），表示取当前行之前第 1 行的 col 值；0 是默认值（default），当偏移越界（当前行之前没有第 1 行，即第一行）时返回 0 而非 NULL。因此 LAG(col, 1, 0) 返回当前行前一行 col 的值，第一行因无前一行返回 0。三个参数中偏移量默认 1、默认值默认 NULL，可省略。

LAG(col, offset, default)：col 列、offset 偏移行数、default 越界默认值。LAG(col,1,0) 表前一行，首行返回 0。

• CTE 与视图复用
• 物化
• 性能

CTE 与视图都用于代码复用，但取舍不同：CTE 是单条查询内的临时命名查询，只在当前语句内有效，不持久化，适合单次复杂的查询分解；视图是持久化的命名查询对象，可被多个查询/应用复用，支持权限控制、列隐藏，但每次使用都重新执行（除非物化视图）。性能上：CTE 默认内联（PostgreSQL 12+）或可物化，视图每次查询都按其中 SQL 执行（可被优化器展开）。CTE 适合单次查询逻辑分解，视图适合跨查询复用与接口封装。

CTE 单语句临时复用，视图持久化跨查询复用。视图可建物化视图提升性能，CTE 局部优化。

• 百分比排名
• 累积分布
• 计算公式

PERCENT_RANK() 返回某行在分区内的相对百分比排名，公式为 (rank-1)/(总行数-1)，值为 0 到 1（第一行为 0，最后一行为 1）。CUME_DIST() 返回累积分布，公式为 (小于等于当前行的行数)/总行数，值为 0 到 1（最后一行总为 1）。两者都基于 ORDER BY，但 PERCENT_RANK 基于排名（rank 的归一化），CUME_DIST 基于行数比例。例如并列时 PERCENT_RANK 与 CUME_DIST 值不同。

PERCENT_RANK 用 (rank-1)/(n-1)，CUME_DIST 用 (<=当前行行数)/n。前者基于排名，后者基于累积比例。

• 并列排名
• 是否跳号
• 区别

rank 与 dense_rank 在相同排序键值（并列）时给出相同排名，但区别在于并列之后是否跳号：rank 在并列后会跳号（如 1,1,3），dense_rank 不跳号（如 1,1,2）。例如有 3 行，前两行并列第一，rank 给 1,1,3，dense_rank 给 1,1,2。rank 的排名等于"严格小于当前值的行数 + 1"，dense_rank 的排名是"去重后的序号"。核心区别就是并列后是否跳过排名。

rank 并列后跳号，dense_rank 不跳号。这是两个排名函数最核心、最常考的区别。

• 帧边界
• PRECEDING
• FOLLOWING

frame_clause 定义窗口帧的边界，PRECEDING 表示"当前行之前的行"，FOLLOWING 表示"当前行之后的行"。例如 ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING 表示帧从当前行前 2 行到当前行后 1 行。UNBOUNDED PRECEDING 表示分区的第一行，UNBOUNDED FOLLOWING 表示分区的最后一行，CURRENT ROW 表示当前行。帧内行参与聚合/窗口计算。PRECEDING/FOLLOWING 决定帧的上下边界。

PRECEDING 指当前行之前，FOLLOWING 指之后。UNBOUNDED 为极值，CURRENT ROW 为当前行。帧边界决定计算范围。

• CYCLE path
• 路径数组
• 防环

CYCLE 子句的 USING path 会添加一个数组列，用于存储从递归起点到当前节点经过的路径。例如 CYCLE id SET is_cycle USING path，path 是数组，记录访问过的节点 id 序列（如 {1,2,3} 表示从 1 到 3 的路径）。递归引擎在扩展时检查当前节点是否已在 path 中（沿路径），若已存在则标记 is_cycle=TRUE 并停止扩展，从而防止环。path 列存储的是深度优先路径的访问历史。

path 列是数组，记录从起点到当前节点的访问序列，用于检测环（节点是否已在路径中）。

• RECURSIVE 关键字
• 自引用
• 防误用

WITH RECURSIVE 关键字是必需的，因为它显式声明 CTE 包含递归（自引用），让解析器知道该 CTE 定义中引用自身是合法的。没有 RECURSIVE 时，CTE 中不能引用自身（自引用会报错）。强制写 RECURSIVE 有三重意义：1) 明确意图，提醒读者该 CTE 递归；2) 防止误写自引用导致意外；3) 让解析器以递归语义处理（锚成员 + 递归成员 + UNION）。因此递归 CTE 必须带 WITH RECURSIVE。

RECURSIVE 显式声明递归，允许自引用，并让解析器按递归语义处理。防止误用与歧义。

• NTILE 分桶
• 桶数
• 分配

NTILE(4) 把满足 ORDER BY 排序的结果集分成 4 个桶（尽量均匀），为每行分配桶号 1 到 4。例如总行数能被 4 整除时每桶行数相同；不能整除时，前几桶多一行。桶号按排序顺序分配，用于分位数/分桶分析（如四分位）。NTILE(n) 的 n 是桶数。若行数小于桶数，则部分桶为空。

NTILE(4) 分 4 桶，行尽量均匀分配，桶号 1-4。用于分位数分桶。

• ROWS 行号
• RANGE 值范围
• 平局差异

例：数据 id 为 1,2,2,3。用 SUM(x) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，第 3 行（id=2）累计到第 3 行；而用 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，第 3 行（id=2）因为是 peer 组，会累计到所有 id=2 的行（第 2、3 行），结果与 ROWS 不同。即 ROWS 按行位置，RANGE 按值，同值行整组纳入。因此 RANGE 下同值行 share 相同累计值。

ROWS 按行号、RANGE 按值（同值 peer 整组纳入）。同值行时 RANGE 累计值会跳变。

• IGNORE NULLS
• RESPECT NULLS
• 偏移取值

IGNORE NULLS 让窗口函数（LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE）跳过 NULL 值，只考虑非 NULL 值。RESPECT NULLS（默认）则把 NULL 也当作普通值参与。例如 LAG(col) OVER (ORDER BY id) IGNORE NULLS 会返回向前看时最近的非 NULL 值，而非紧邻的（可能为 NULL 的）值。这对稀疏数据（有大量 NULL 的列）取"上一个有效值"很有用。注意 IGNORE NULLS 只影响取值函数，不影响聚合。

IGNORE NULLS 跳过 NULL 取非 NULL 值，RESPECT NULLS 默认保留 NULL。对稀疏列取前/首有效值有效。