数据库基础体系 · 第 94/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

PostgreSQL 高级 SQL：LATERAL、递归 CTE、窗口、数组和范围

本文基于 PostgreSQL 当前稳定版本的公开 SQL 语义。示例默认在一个 PostgreSQL 数据库连接中执行；除非特别说明，不涉及跨数据库、分布式事务或 ORM 改写。SQL 中使用的表和数据可以在同一个事务内创建，因此示例之间不会依赖外部系统状态。

本文覆盖以下核心内容：

• LATERAL 的相关表表达式语义、逐行求值、JOIN 组合和 Top-N 查询
• 公用表表达式（CTE）与递归 CTE 的固定点计算过程、路径、环检测和层级查询
• 窗口函数的分区、排序、Frame、排名、累计值和间隔分析
• PostgreSQL 数组的一维与多维语义、下标、展开、ANY/ALL 和索引
• PostgreSQL 范围类型的边界、空范围、无限边界、重叠运算、索引和排他约束
• 这些特性之间的组合，以及常见误解、空值行为、执行顺序和诊断方式

---

一、先建立 SQL 的计算模型

理解这些高级语法，首先要区分三个层次：

1. 行集合如何产生：FROM、JOIN、LATERAL、CTE、递归 CTE。
2. 行集合如何被划分和排序：窗口函数的 PARTITION BY、ORDER BY 和 Frame。
3. 单个值如何表示和比较：数组、范围、空值以及相关运算符。

一个查询可以粗略表示为：

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
WINDOW ...
ORDER BY ...
LIMIT ...

这不是完整的物理执行计划，但有助于理解一个关键事实：

窗口函数是在 FROM、WHERE、GROUP BY、HAVING 形成的结果集上计算的，不能直接在同一层的 WHERE 中引用窗口结果。

例如，下面的查询是错误的：

SELECT
    id,
    amount,
    row_number() OVER (ORDER BY amount DESC) AS rn
FROM payments
WHERE row_number() OVER (ORDER BY amount DESC) <= 3;

窗口函数不能直接出现在 WHERE 中。应先在子查询或 CTE 中计算，再过滤：

SELECT *
FROM (
    SELECT
        id,
        amount,
        row_number() OVER (ORDER BY amount DESC) AS rn
    FROM payments
) AS ranked
WHERE rn <= 3;

另一方面，LATERAL 改变的是 FROM 子句中表表达式之间的依赖关系；递归 CTE 改变的是行集合的生成方式；数组和范围则改变的是单列值内部的结构和比较规则。

---

二、LATERAL：让 FROM 中的右侧表达式依赖左侧当前行

2.1 普通子查询与 LATERAL 的区别

考虑两个表：

CREATE TEMP TABLE customers (
    customer_id integer PRIMARY KEY,
    name text NOT NULL
);

CREATE TEMP TABLE orders (
    order_id integer PRIMARY KEY,
    customer_id integer NOT NULL,
    created_at timestamptz NOT NULL,
    amount numeric(12, 2) NOT NULL
);

INSERT INTO customers VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Carol');

INSERT INTO orders VALUES
    (101, 1, '2025-01-01 10:00+00', 30.00),
    (102, 1, '2025-01-03 10:00+00', 80.00),
    (103, 1, '2025-01-05 10:00+00', 50.00),
    (201, 2, '2025-01-02 10:00+00', 20.00);

下面的子查询不能引用外层 c.customer_id：

SELECT c.customer_id, x.order_id
FROM customers AS c
JOIN (
    SELECT *
    FROM orders
    WHERE customer_id = c.customer_id
) AS x ON true;

原因是普通 FROM 子查询在语义上独立计算。它并不知道左侧的 c。

加入 LATERAL 后，右侧表达式可以引用它左侧已经出现的表：

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT order_id, amount
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.created_at DESC
    LIMIT 2
) AS o ON true
ORDER BY c.customer_id, o.order_id;

这里的逻辑可以写成：

$$\text{结果} = \bigcup_{c \in customers} \left(c \mathbin{\text{LEFT JOIN}} f(c)\right)$$

其中：

• c 是左侧当前行；
• f(c) 是把 c.customer_id 代入右侧子查询后得到的结果；
• 右侧子查询对每个左侧客户分别求值；
• LEFT JOIN 保证即使 f(c) 没有行，客户仍然保留。

预期结果类似：

 customer_id | name  | order_id | amount
--------------+-------+----------+--------
            1 | Alice |      102 |  80.00
            1 | Alice |      103 |  50.00
            2 | Bob   |      201 |  20.00
            3 | Carol |           |

这里有两个容易混淆的点。

第一，LIMIT 2 是每个客户限制两行，而不是整个查询限制两行。因为这个 LIMIT 位于依赖当前客户的右侧子查询内部。

第二，LEFT JOIN LATERAL 需要一个连接条件。对于“右侧是否有结果都保留左侧行”的写法，通常使用：

ON true

如果使用普通 CROSS JOIN LATERAL，右侧没有结果时左侧行也会消失：

SELECT c.customer_id, o.order_id
FROM customers AS c
CROSS JOIN LATERAL (
    SELECT order_id
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.created_at DESC
    LIMIT 2
) AS o;

因此：

• CROSS JOIN LATERAL：右侧为空时丢弃左侧行；
• LEFT JOIN LATERAL ... ON true：右侧为空时保留左侧行，并用空值补齐右侧列。

2.2 LATERAL 的求值范围

在 FROM 中，LATERAL 只能引用它左侧已经出现的表项。例如：

FROM customers AS c
CROSS JOIN LATERAL (...) AS x

右侧 x 可以引用 c。

但下面的顺序不成立：

FROM LATERAL (
    SELECT ...
    WHERE customer_id = c.customer_id
) AS x
CROSS JOIN customers AS c;

因为 c 在右侧表达式求值时还没有建立。

对于显式 JOIN，可引用范围由连接树决定。实际写复杂查询时，最好把被依赖的表明确放在左侧，并使用括号或拆分 CTE，避免仅依赖复杂的隐式关联范围。

2.3 LATERAL 不等于“必然逐行执行”

LATERAL 的语义是右侧表达式可以依赖左侧当前行。它不等于执行计划一定采用朴素的“外层循环加内层重新扫描”实现。

优化器可能：

• 使用 Nested Loop；
• 在可行时利用索引；
• 重排不相关部分；
• 对子查询进行其他优化。

但只要右侧真正引用了左侧变量，右侧的结果就必须符合“针对当前左侧行求值”的语义。

例如，对上面的查询建立索引通常有助于 Top-N：

CREATE INDEX ON orders (customer_id, created_at DESC);

这个索引表达的是访问路径，而不是 LATERAL 的语义要求。是否使用它应通过执行计划确认：

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    c.customer_id,
    o.order_id,
    o.amount
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT order_id, amount
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.created_at DESC
    LIMIT 2
) AS o ON true;

EXPLAIN (ANALYZE) 会实际执行查询，因此在生产环境中要注意查询的副作用、锁和资源消耗。

2.4 LATERAL 的常见用途

每个分组取 Top-N

这是最典型的用途之一：

SELECT
    c.customer_id,
    o.order_id,
    o.created_at,
    o.amount
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT order_id, created_at, amount
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.created_at DESC, o.order_id DESC
    LIMIT 2
) AS o ON true
ORDER BY c.customer_id, o.created_at DESC, o.order_id DESC;

order_id DESC 是一个稳定的并列打破规则。如果只写 ORDER BY created_at DESC，同一时间的记录顺序未被完全定义，Top-N 中具体保留哪几行可能不稳定。

将函数结果展开为行

假设有一个返回集合的函数：

CREATE OR REPLACE FUNCTION make_series_for_customer(n integer)
RETURNS SETOF integer
LANGUAGE sql
AS $$
    SELECT generate_series(1, n)
$$;

可以对每个客户分别调用：

SELECT
    c.customer_id,
    s.value
FROM customers AS c
CROSS JOIN LATERAL make_series_for_customer(c.customer_id) AS s(value)
ORDER BY c.customer_id, s.value;

对于 PostgreSQL 的集合返回函数，某些位置存在隐式的横向引用行为，但显式写出 LATERAL 更清楚，也更容易表达“这个集合依赖左侧当前行”的意图。

将复杂计算命名并复用

SELECT
    o.order_id,
    calc.net_amount,
    calc.tax_amount
FROM orders AS o
CROSS JOIN LATERAL (
    SELECT
        o.amount * 0.90 AS net_amount,
        o.amount * 0.10 AS tax_amount
) AS calc;

这里没有产生额外表行，只是把依赖当前行的计算组织成一个可读的关系表达式。

2.5 LATERAL 的边界

LATERAL 适合“每个左侧实体有一个依赖它的关系结果”的问题，但不应把它当作循环语句使用。

例如，如果左表有一百万行，右侧子查询无法使用索引，且每次都需要扫描大表，那么逻辑上确实可能产生高昂的重复工作。可以比较以下两类方案：

• LATERAL + ORDER BY ... LIMIT：适合每组只取少量结果，且右侧有匹配索引；
• 窗口函数：适合一次性处理大量分组结果；
• 聚合或预计算：适合重复查询同一类派生数据。

LATERAL 是表达能力，不是性能保证。

---

三、CTE 与递归 CTE：从固定结果到固定点

3.1 普通 CTE 的作用

CTE 使用 WITH 定义一个命名的查询结果：

WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE created_at >= '2025-01-01'
)
SELECT customer_id, count(*)
FROM recent_orders
GROUP BY customer_id;

普通 CTE 主要提供：

• 查询结构拆分；
• 在同一条 SQL 中复用一个逻辑结果；
• 递归查询的语法入口；
• INSERT、UPDATE、DELETE、MERGE 中的前置数据集。

CTE 不应被简单理解为“必然物化的临时表”。在 PostgreSQL 中，满足条件的非递归、无副作用 CTE 可能被折叠进主查询；MATERIALIZED 可以要求物化，NOT MATERIALIZED 可以请求不要强制物化。

WITH recent_orders AS MATERIALIZED (
    SELECT *
    FROM orders
    WHERE created_at >= '2025-01-01'
)
SELECT *
FROM recent_orders
WHERE amount > 50;

MATERIALIZED 的语义是把该 CTE 作为独立结果计算并供后续引用；它可能避免重复计算，也可能阻止谓词下推。是否使用应根据查询计划和数据访问模式判断。

3.2 递归 CTE 的形式

递归 CTE 通常写成：

WITH RECURSIVE name(columns) AS (
    -- 锚点项
    SELECT ...

    UNION ALL

    -- 递归项
    SELECT ...
    FROM name
    JOIN ...
)
SELECT ...
FROM name;

它由两个部分构成：

• 锚点项（anchor term）：初始结果；
• 递归项（recursive term）：根据已找到的结果继续产生新结果。

设锚点结果为 $T_0$，第 $i$ 次递归产生的新结果为 $T_i$，则：

$$T_{i+1} = F(T_i)$$

实际递归结果是：

$$T = T_0 \cup T_1 \cup T_2 \cup \cdots$$

当某次递归不再产生行时，计算结束。这就是固定点：继续应用递归规则，结果集合不再增长。

对于 UNION，重复行会被消除；对于 UNION ALL，重复行保留。递归查询通常使用 UNION ALL，然后显式控制环和重复路径，因为这能保留路径信息，也避免不必要的全局去重。

3.3 一个完整的组织层级查询

先建立组织表：

CREATE TEMP TABLE employee (
    employee_id integer PRIMARY KEY,
    name text NOT NULL,
    manager_id integer REFERENCES employee(employee_id)
);

INSERT INTO employee VALUES
    (1, 'CEO',    NULL),
    (2, 'Alice',  1),
    (3, 'Bob',    1),
    (4, 'Carol',  2),
    (5, 'David',  2),
    (6, 'Eve',    3);

目标是从 CEO 出发，列出所有下属，并保留层级深度和路径。

WITH RECURSIVE org AS (
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        0 AS depth,
        ARRAY[e.employee_id] AS path
    FROM employee AS e
    WHERE e.manager_id IS NULL

    UNION ALL

    SELECT
        child.employee_id,
        child.name,
        child.manager_id,
        parent.depth + 1,
        parent.path || child.employee_id
    FROM org AS parent
    JOIN employee AS child
      ON child.manager_id = parent.employee_id
)
SELECT
    employee_id,
    name,
    depth,
    path
FROM org
ORDER BY path;

中间结果可以理解为：

锚点：
employee_id | name | depth | path
------------+------+-------+------
1           | CEO  | 0     | {1}

第一次递归：
2 | Alice | 1 | {1,2}
3 | Bob   | 1 | {1,3}

第二次递归：
4 | Carol | 2 | {1,2,4}
5 | David | 2 | {1,2,5}
6 | Eve   | 2 | {1,3,6}

ARRAY[e.employee_id] 创建初始路径，parent.path || child.employee_id 把新节点追加到路径末尾。

为什么要显式保存 depth 和 path？

depth 表示根节点到当前节点经过的边数：

$$depth(child) = depth(parent) + 1$$

path 则保存完整访问链，能够用于：

• 展示缩进；
• 排序出层级结构；
• 检测环；
• 判断某个节点是否出现在祖先链中。

需要注意，递归结果的产生顺序不应直接当作最终展示顺序。即使某次执行表现出类似广度优先的顺序，SQL 结果集也只有在明确 ORDER BY 后才具有确定顺序。

3.4 递归查询中的环检测

如果组织表错误地出现环，例如：

2 -> 4 -> 5 -> 2

无条件的 UNION ALL 递归会不断生成新路径，最终可能因为语句超时、资源耗尽或被取消而失败。

可以使用数组检测当前节点是否已经出现在路径中：

WITH RECURSIVE org AS (
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        0 AS depth,
        ARRAY[e.employee_id] AS path,
        false AS is_cycle
    FROM employee AS e
    WHERE e.manager_id IS NULL

    UNION ALL

    SELECT
        child.employee_id,
        child.name,
        child.manager_id,
        parent.depth + 1,
        parent.path || child.employee_id,
        child.employee_id = ANY(parent.path) AS is_cycle
    FROM org AS parent
    JOIN employee AS child
      ON child.manager_id = parent.employee_id
    WHERE NOT (child.employee_id = ANY(parent.path))
)
SELECT *
FROM org
ORDER BY path;

这里有两个动作：

1. child.employee_id = ANY(parent.path) 判断新节点是否已经在路径中；
2. WHERE NOT (...) 阻止再次进入该节点。

如果希望把发现环的那条记录也输出，但不再继续扩展，可以把条件改成：

WITH RECURSIVE org AS (
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        0 AS depth,
        ARRAY[e.employee_id] AS path,
        false AS is_cycle
    FROM employee AS e
    WHERE e.manager_id IS NULL

    UNION ALL

    SELECT
        child.employee_id,
        child.name,
        child.manager_id,
        parent.depth + 1,
        parent.path || child.employee_id,
        child.employee_id = ANY(parent.path)
    FROM org AS parent
    JOIN employee AS child
      ON child.manager_id = parent.employee_id
    WHERE NOT parent.is_cycle
)
SELECT *
FROM org
ORDER BY path;

这个版本的停止条件是：父行已经是环时不再展开。实际使用时还可以增加最大深度作为防御性边界：

WHERE NOT parent.is_cycle
  AND parent.depth < 100;

这不是环检测的替代品，而是防止脏数据导致异常深度的额外限制。

PostgreSQL 还支持递归查询的 SEARCH 和 CYCLE 子句，用于声明遍历顺序和循环检测。它们适合结构化递归查询，但手动维护 path 的方式仍然有价值：路径可以直接用于展示、调试和业务判断。

3.5 递归 CTE 的类型匹配

锚点项和递归项对应列必须具有兼容类型。例如：

WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 5
)
SELECT *
FROM numbers;

这里 1 和 n + 1 都是整数，能够匹配。

当表达式类型不一致时，必须显式转换：

WITH RECURSIVE paths(path) AS (
    SELECT ARRAY[1]::bigint[]
    UNION ALL
    SELECT path || 2::bigint
    FROM paths
    WHERE cardinality(path) < 3
)
SELECT *
FROM paths;

递归查询不是动态类型系统。锚点阶段选择的列类型会影响递归项，尤其是：

• integer 与 bigint；
• text 与 varchar；
• 一维数组与不同元素类型的数组；
• numeric 精度和隐式转换。

遇到“类型不匹配”错误时，应先分别查看锚点表达式和递归表达式的类型：

SELECT pg_typeof(ARRAY[1]);
SELECT pg_typeof(ARRAY[1]::bigint[]);

3.6 递归 CTE 的限制与性能边界

递归项不是任意查询都能无条件放入的循环体。聚合、窗口函数等操作在递归项中存在语义限制，不能简单地把一个复杂报表查询塞入递归部分。通常的拆分方式是：

1. 递归 CTE 只负责产生节点、边、深度和路径；
2. 外层查询再进行聚合、窗口计算或排序。

例如，先生成所有组织节点：

WITH RECURSIVE org AS (
    ...
)
SELECT
    manager_id,
    count(*)
FROM org
GROUP BY manager_id;

此外，递归查询会随着层级、分支因子和路径数量增长。需要关注：

• 图是否有环；
• 一个节点是否可以通过多条路径到达；
• 是否需要保留“路径级结果”，还是只需要“节点去重结果”；
• 是否存在深度上限；
• 是否需要先限制根节点或业务范围。

如果一个图查询要求“每个节点只出现一次”，但又使用 UNION ALL 保留所有路径，就必须明确选择去重策略。路径去重和节点去重不是一回事：

• {1, 2, 4} 和 {1, 3, 4} 是两条不同路径；
• 但它们都到达节点 4。

---

四、窗口函数：在不折叠行的情况下进行分区计算

4.1 聚合与窗口的区别

聚合会把多行折叠成更少的行：

SELECT customer_id, sum(amount)
FROM orders
GROUP BY customer_id;

结果每个客户一行。

窗口函数则在保留明细行的同时，为每行附加计算结果：

SELECT
    order_id,
    customer_id,
    amount,
    sum(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_total
FROM orders;

结果仍然每笔订单一行，只是每行都带有该客户的总金额。

窗口函数的一般形式是：

function_name(...) OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [frame_clause]
)

其中：

• PARTITION BY 将输入行划分为互不影响的分区；
• ORDER BY 定义分区内的逻辑顺序；
• Frame 定义当前行计算时实际能看到的行范围。

4.2 分区与排序

SELECT
    order_id,
    customer_id,
    created_at,
    amount,
    row_number() OVER (
        PARTITION BY customer_id
        ORDER BY created_at, order_id
    ) AS customer_order_no
FROM orders
ORDER BY customer_id, created_at, order_id;

对每个客户，窗口分区独立编号。order_id 作为第二排序键，使时间相同的订单也有确定顺序。

如果没有 PARTITION BY：

row_number() OVER (ORDER BY created_at)

所有输入行属于一个分区。

如果没有 ORDER BY：

sum(amount) OVER (PARTITION BY customer_id)

它可以计算整个分区总额，但不能定义“上一行”“前五行”这样的顺序概念。

4.3 排名函数的差异

假设分区中的排序值为：

100, 100, 80, 70

三种常见排名函数结果如下：

amount	row_number	rank	dense_rank
100	1	1	1
100	2	1	1
80	3	3	2
70	4	4	3

含义分别是：

• row_number()：即使排序值相同，也给每行唯一序号；
• rank()：并列行排名相同，后续排名跳过并列数量；
• dense_rank()：并列行排名相同，但后续排名不跳号。

因此“每组取前三名”必须先明确业务含义：

-- 严格只保留三行
row_number() OVER (...) <= 3

-- 保留排名不超过 3 的所有并列行
rank() OVER (...) <= 3

4.4 Frame：窗口中真正参与计算的行

PARTITION BY 和 ORDER BY 只确定分区和逻辑顺序；Frame 决定当前行计算时使用哪部分数据。

常见写法：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

表示从当前分区第一行到当前行。

累计金额：

SELECT
    order_id,
    customer_id,
    created_at,
    amount,
    sum(amount) OVER (
        PARTITION BY customer_id
        ORDER BY created_at, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_amount
FROM orders;

对于默认 Frame，必须特别小心。包含 ORDER BY 的窗口聚合通常默认使用与 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 等价的行为。RANGE 以排序值的“同行组（peer group）”为边界，因此相同排序值的行可能同时进入当前 Frame。

例如：

WITH t(amount) AS (
    VALUES (100), (100), (80)
)
SELECT
    amount,
    sum(amount) OVER (ORDER BY amount DESC) AS default_sum,
    sum(amount) OVER (
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rows_sum
FROM t;

可能得到：

amount	default_sum	rows_sum
100	200	100
100	200	200
80	280	280

原因是默认的 RANGE Frame 对第一行的 100 包含了所有排序值同为 100 的 peer；显式 ROWS 则严格按照物理逻辑行位置逐行累计。

因此：

• 需要“同一排序值一起计算”时，可以使用 RANGE；
• 需要“恰好前 N 行”时，应使用 ROWS；
• 需要按 peer group 计数时，可以考虑 GROUPS。

前后行 Frame

SELECT
    order_id,
    customer_id,
    amount,
    sum(amount) OVER (
        PARTITION BY customer_id
        ORDER BY created_at, order_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS last_three_rows_sum
FROM orders;

这表示当前行和前两行，不是“最近三天”。“行数窗口”和“时间范围窗口”不是一回事。

4.5 累计、移动平均和间隔分析

移动平均

SELECT
    order_id,
    customer_id,
    created_at,
    amount,
    avg(amount) OVER (
        PARTITION BY customer_id
        ORDER BY created_at, order_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders;

对于每个客户，这里计算当前订单及前两笔订单的平均金额。开头不足三行时，Frame 会包含实际存在的行。

LAG 与 LEAD

lag 访问前一行，lead 访问后一行：

SELECT
    order_id,
    customer_id,
    created_at,
    amount,
    lag(created_at) OVER (
        PARTITION BY customer_id
        ORDER BY created_at, order_id
    ) AS previous_created_at,
    lead(created_at) OVER (
        PARTITION BY customer_id
        ORDER BY created_at, order_id
    ) AS next_created_at
FROM orders;

第一行没有前一行，因此 lag 返回 NULL；最后一行没有后一行，因此 lead 返回 NULL。

计算相邻订单间隔：

SELECT
    order_id,
    customer_id,
    created_at,
    created_at
      - lag(created_at) OVER (
            PARTITION BY customer_id
            ORDER BY created_at, order_id
        ) AS elapsed_since_previous
FROM orders;

timestamp - timestamp 的结果是 interval。如果要得到秒数：

SELECT
    order_id,
    extract(
        epoch FROM (
            created_at
            - lag(created_at) OVER (
                  PARTITION BY customer_id
                  ORDER BY created_at, order_id
              )
        )
    ) AS seconds_since_previous
FROM orders;

第一行的结果仍然是 NULL，因为没有可相减的前一条记录。

4.6 窗口结果的过滤与并列

完整的“每个客户金额最高的两笔订单”：

WITH ranked AS (
    SELECT
        o.*,
        row_number() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC, order_id DESC
        ) AS rn
    FROM orders AS o
)
SELECT *
FROM ranked
WHERE rn <= 2
ORDER BY customer_id, rn;

如果使用：

rank() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
)

再过滤 rank <= 2，并列订单可能导致每个客户返回超过两行。

窗口函数不能直接用于 WHERE 的根本原因，是 SQL 逻辑阶段中 WHERE 早于窗口计算。子查询或 CTE 是把窗口结果转成可过滤列的标准方式。

---

五、数组：一个值中的有序元素集合

5.1 数组的基本语义

PostgreSQL 数组是一个值，不是自动展开的多行表。最常见的一维整数数组：

SELECT ARRAY[10, 20, 30] AS values;

默认数组下标从 1 开始：

SELECT
    (ARRAY[10, 20, 30])[1] AS first_value,
    (ARRAY[10, 20, 30])[3] AS third_value;

结果是：

 first_value | third_value
-------------+------------
 10          | 30

访问不存在的下标通常得到 NULL：

SELECT (ARRAY[10, 20, 30])[4];

数组也可以显式指定下界：

SELECT '[0:2]={10,20,30}'::integer[] AS values;

这时合法下标是 0、1、2。因此不能把“数组下标从 1 开始”理解成绝对的存储约束；它是默认构造行为，数组值本身可以携带其他下界。

查看边界和长度：

SELECT
    array_lower(a, 1) AS lower_bound,
    array_upper(a, 1) AS upper_bound,
    cardinality(a) AS total_elements,
    array_length(a, 1) AS first_dimension_length
FROM (VALUES (ARRAY[10, 20, 30])) AS t(a);

区别是：

• cardinality：所有维度元素总数；
• array_length(a, n)：第 n 个维度的长度；
• 空数组、NULL 数组和缺失维度的结果要注意区分。

5.2 数组的空值与比较

数组本身可以是 NULL，数组元素也可以是 NULL：

SELECT
    NULL::integer[] AS null_array,
    ARRAY[1, NULL, 3] AS array_with_null;

这两者完全不同：

• NULL::integer[]：没有数组值；
• ARRAY[1, NULL, 3]：有一个数组值，其中第二个元素为空。

数组相等比较会逐元素比较，并考虑数组维度和下界等数组结构信息。数组排序比较通常按元素逐个比较；如果元素相同，还会比较维度信息。因此不要把数组简单当作无序集合。

例如：

SELECT
    ARRAY[1, 2] = ARRAY[1, 2] AS same_value,
    ARRAY[1, 2] @> ARRAY[2] AS contains_2,
    ARRAY[1, 2] <@ ARRAY[1, 2, 3] AS is_subset;

数组包含运算符：

• a @> b：a 包含 b 的所有元素；
• a <@ b：a 被 b 包含。

这类包含判断不等同于数学集合的严格多重集合语义。重复元素不会按“出现次数”产生通常所理解的多重集合约束。例如，不能仅凭 @> 判断两个数组中某个元素的重复次数相同。

5.3 ANY、ALL 与 NULL

数组常用于标量与数组的比较：

SELECT 20 = ANY(ARRAY[10, 20, 30]);

x = ANY(array) 表示只要数组中存在一个元素使比较为真，结果就为真。

等价的直觉形式是：

$$x = a_1 \lor x = a_2 \lor \cdots \lor x = a_n$$

ALL 则近似于逻辑与：

SELECT 20 < ALL(ARRAY[30, 40, 50]);

但空数组和 NULL 会影响三值逻辑：

SELECT
    20 = ANY(ARRAY[]::integer[]) AS any_empty,
    20 = ALL(ARRAY[]::integer[]) AS all_empty,
    20 = ANY(NULL::integer[]) AS any_null;

直觉上：

• ANY 对空集合为假；
• ALL 对空集合为真；
• 对 NULL 数组，结果为 NULL。

数组含有 NULL 元素时也可能得到 NULL，例如没有任何真值，但存在无法确定的比较。

5.4 展开数组：unnest、WITH ORDINALITY 和 generate_subscripts

unnest 把数组展开为行：

SELECT *
FROM unnest(ARRAY['a', 'b', 'c']) AS u(value);

结果：

 value
-------
 a
 b
 c

如果需要位置：

SELECT *
FROM unnest(ARRAY['a', 'b', 'c'])
     WITH ORDINALITY AS u(value, position);

position 从 1 开始，表示展开序号。

对于需要保留真实数组下标的场景，应使用 generate_subscripts：

WITH data AS (
    SELECT '[0:2]={10,20,30}'::integer[] AS a
)
SELECT
    s AS subscript,
    a[s] AS value
FROM data
CROSS JOIN LATERAL generate_subscripts(data.a, 1) AS g(s)
ORDER BY s;

输出下标为 0、1、2，不会错误地假设下标从 1 开始。

unnest 适合顺序展开；generate_subscripts 适合需要真实下标、反向遍历或多维数组定位的场景。

多维数组

SELECT ARRAY[
    [1, 2],
    [3, 4]
] AS matrix;

使用 unnest 时，多维数组会被展平：

SELECT *
FROM unnest(ARRAY[
    [1, 2],
    [3, 4]
]) AS u(value);

得到四行，而不是行列坐标。如果需要行列下标：

WITH data AS (
    SELECT ARRAY[
        [1, 2],
        [3, 4]
    ]::integer[][] AS matrix
)
SELECT
    r AS row_no,
    c AS col_no,
    matrix[r][c] AS value
FROM data
CROSS JOIN LATERAL generate_subscripts(matrix, 1) AS rows(r)
CROSS JOIN LATERAL generate_subscripts(matrix, 2) AS cols(c)
ORDER BY r, c;

这里 LATERAL 用来让 generate_subscripts 访问当前行的 matrix 值。

5.5 数组聚合与顺序

把多行聚合为数组：

SELECT
    customer_id,
    array_agg(order_id ORDER BY created_at, order_id) AS order_ids
FROM orders
GROUP BY customer_id;

array_agg 内部的 ORDER BY 很重要。关系表本身没有固有行顺序，如果不指定顺序，就不应依赖聚合结果数组的元素顺序。

反向操作：

WITH grouped AS (
    SELECT
        customer_id,
        array_agg(order_id ORDER BY created_at, order_id) AS order_ids
    FROM orders
    GROUP BY customer_id
)
SELECT
    g.customer_id,
    u.order_id,
    u.position
FROM grouped AS g
CROSS JOIN LATERAL unnest(g.order_ids)
     WITH ORDINALITY AS u(order_id, position);

5.6 数组索引与建模边界

如果经常执行：

SELECT *
FROM customer_profiles
WHERE tags @> ARRAY['vip'];

可以考虑 GIN 索引：

CREATE INDEX customer_profiles_tags_gin
ON customer_profiles
USING gin (tags);

但索引能否使用取决于表达式、运算符类别、统计信息和查询选择性，应通过 EXPLAIN 验证。

数组适合表示：

• 固定结构的小型列表；
• 标签、路径、维度值；
• 需要作为一个值整体读写的字段。

如果数组中的每个元素都需要独立约束、独立关联、独立频繁查询，关系表通常更自然。例如“订单包含多个商品”通常应使用订单明细表，而不是把商品 ID 全塞入数组，因为数组不能直接替代外键行约束。

---

六、范围类型：表示连续区间及其边界

6.1 范围的数学模型

一个范围可以表示为：

$$[l, u)$$

其中：

• $l$ 是下界；
• $u$ 是上界；
• [ 表示包含边界；
• ( 表示不包含边界。

PostgreSQL 内置常见范围类型：

范围类型	元素类型	常见用途
int4range	integer	整数区间
int8range	bigint	大整数区间
numrange	numeric	数值区间
tsrange	timestamp	不带时区时间区间
tstzrange	timestamptz	带时区时间区间
daterange	date	日期区间

构造范围：

SELECT
    int4range(1, 10, '[)') AS numbers,
    tstzrange(
        '2025-01-01 10:00+00',
        '2025-01-01 12:00+00',
        '[)'
    ) AS meeting_time;

[) 表示包含下界、不包含上界。时间区间常用半开区间，因为相邻区间可以无歧义地拼接：

[10:00, 11:00)
[11:00, 12:00)

二者不重叠，但没有空隙。

6.2 离散范围与规范化

整数和日期是离散类型，PostgreSQL 会对内置离散范围进行规范化。比如：

SELECT
    int4range(1, 4, '(]') AS original_form,
    int4range(1, 4, '[)') AS canonical_form;

对于整数，(1,4] 与 [2,5) 表示相同的整数集合：

{2,3,4}

因此 PostgreSQL 的离散范围通常采用半开规范形式。相同语义的范围可能以规范化后的形式显示。

连续类型如 numeric、timestamp 没有同样的“下一个离散元素”概念，边界形式的语义不能按整数范围类推。

6.3 空范围、无限边界和 NULL

空范围不是 NULL：

SELECT
    int4range(1, 1, '[)') AS empty_range,
    NULL::int4range AS null_range;

• int4range(1, 1, '[)') 表示空范围；
• NULL::int4range 表示列值缺失。

判断是否为空：

SELECT isempty(int4range(1, 1, '[)'));

范围可以有无限边界：

SELECT
    '(,10]'::int4range AS upper_bounded,
    '[10,)'::int4range AS lower_bounded,
    '(,)'::int4range AS unbounded;

范围的无限边界与 NULL 边界也要区分：

• 范围值存在，但某一侧没有有限边界：无限边界；
• 整个范围列是 NULL：没有范围值。

查看边界：

SELECT
    lower('[1,10)'::int4range) AS lower_value,
    upper('[1,10)'::int4range) AS upper_value,
    lower_inc('[1,10)'::int4range) AS lower_inclusive,
    upper_inc('[1,10)'::int4range) AS upper_inclusive,
    lower_inf('[1,)'::int4range) AS lower_infinite,
    upper_inf('(,10]'::int4range) AS upper_infinite;

6.4 范围的核心运算符

元素是否属于范围

SELECT
    5 <@ int4range(1, 10, '[)') AS five_is_inside,
    10 <@ int4range(1, 10, '[)') AS ten_is_inside;

结果分别是 true 和 false，因为 [1,10) 不包含 10。

范围是否重叠

SELECT
    int4range(1, 5, '[)') && int4range(4, 8, '[)') AS overlaps,
    int4range(1, 5, '[)') && int4range(5, 8, '[)') AS touches_only;

结果为：

overlaps    | true
touches_only| false

第二组区间在边界 5 接触，但由于都是半开区间，并不共享元素。

包含关系

SELECT
    int4range(1, 10, '[)') @> int4range(3, 5, '[)') AS contains_subrange,
    int4range(3, 5, '[)') <@ int4range(1, 10, '[)') AS is_subrange;

相邻关系

SELECT
    int4range(1, 5, '[)') -|- int4range(5, 10, '[)') AS adjacent;

-|- 表示两个范围相邻。

并集与差集

对于可以合并的范围：

SELECT
    int4range(1, 5, '[)') + int4range(5, 10, '[)') AS union_range;

结果是一个合并后的范围。

如果两个范围不相邻且不重叠，+ 无法表示为单个范围，会报错。此时应使用多范围类型或分别保留两个范围。

差集示例：

SELECT
    int4range(1, 10, '[)') - int4range(3, 5, '[)') AS difference;

如果差集产生两个不连续区间，单一范围类型无法表示，操作会失败。多范围类型适合表示这种不连续集合。

6.5 多范围类型

多范围（multirange）是多个不重叠、非相邻范围的集合。例如：

SELECT
    '{[1,3), [5,8)}'::int4multirange AS available_parts;

它适合表示：

• 多个空闲时间段；
• 一个实体的多个有效区间；
• 去掉若干占用区间后的剩余区间。

单一范围表达的是连续区间，多范围表达的是若干区间的并集。不能为了方便把所有非连续时间段强行塞入一个范围。

6.6 范围表与排他约束

创建会议室预订表：

CREATE TEMP TABLE room_booking (
    booking_id bigserial PRIMARY KEY,
    room_id integer NOT NULL,
    during tstzrange NOT NULL
);

只建立普通唯一约束无法阻止时间重叠，因为 (room_id, during) 两列即使不同，也可能覆盖相同时间。

PostgreSQL 可以使用排他约束：

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE room_booking
ADD CONSTRAINT room_booking_no_overlap
EXCLUDE USING gist (
    room_id WITH =,
    during WITH &&
);

它表达的约束是：

任意两行不能同时满足 room_id 相等且 during 范围重叠。

插入第一条预订：

INSERT INTO room_booking (room_id, during)
VALUES (
    101,
    tstzrange(
        '2025-02-01 10:00+00',
        '2025-02-01 12:00+00',
        '[)'
    )
);

插入重叠预订：

INSERT INTO room_booking (room_id, during)
VALUES (
    101,
    tstzrange(
        '2025-02-01 11:00+00',
        '2025-02-01 13:00+00',
        '[)'
    )
);

该语句会因排他约束冲突而失败。

但不同房间可以使用相同时间：

INSERT INTO room_booking (room_id, during)
VALUES (
    102,
    tstzrange(
        '2025-02-01 11:00+00',
        '2025-02-01 13:00+00',
        '[)'
    )
);

btree_gist 扩展用于让 integer = 参与 GiST 排他约束。它不是把整数变成范围，而是提供 GiST 可用的操作支持。

6.7 并发下为什么要依赖数据库约束

应用层常见的错误流程是：

1. 查询是否存在重叠预订；
2. 查询结果为空；
3. 插入新预订。

两个并发事务可能同时完成第 2 步，然后都插入冲突记录。普通的“先检查再插入”不是并发安全的原子约束。

排他约束把冲突判断放在数据库约束层。并发插入时，其中一个事务可能等待另一个事务结束；若最终违反约束，则插入失败。应用必须正确处理约束错误，并根据事务边界回滚或使用保存点恢复。

一个事务中的错误会使事务进入失败状态，不能继续执行普通 SQL，直到：

ROLLBACK;

或者在更大的事务中使用：

SAVEPOINT before_booking;

-- 执行可能冲突的 INSERT

ROLLBACK TO SAVEPOINT before_booking;

实际应用还要处理：

• 事务隔离级别；
• 锁等待和锁超时；
• 约束冲突重试；
• 客户端连接池是否正确回滚失败事务。

---

七、把数组、范围、LATERAL、递归和窗口组合起来

下面构造一个更接近实际业务的例子：组织树中的每个员工有若干工作时间段，查询某个根节点的整个下属树，并为每个员工计算：

• 层级深度；
• 从根到当前员工的路径；
• 工作时间段的总时长；
• 按层级中的工作时长排名；
• 工作时间段之间的间隔。

7.1 建立数据

CREATE TEMP TABLE employee_availability (
    employee_id integer NOT NULL,
    available tstzrange NOT NULL
);

INSERT INTO employee_availability VALUES
    (1, tstzrange('2025-03-01 09:00+00', '2025-03-01 12:00+00', '[)')),
    (1, tstzrange('2025-03-01 13:00+00', '2025-03-01 17:00+00', '[)')),
    (2, tstzrange('2025-03-01 09:30+00', '2025-03-01 11:30+00', '[)')),
    (2, tstzrange('2025-03-01 14:00+00', '2025-03-01 16:00+00', '[)')),
    (4, tstzrange('2025-03-01 10:00+00', '2025-03-01 10:30+00', '[)')),
    (4, tstzrange('2025-03-01 15:00+00', '2025-03-01 18:00+00', '[)'));

7.2 用递归 CTE 生成组织树

WITH RECURSIVE org AS (
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        0 AS depth,
        ARRAY[e.employee_id] AS path
    FROM employee AS e
    WHERE e.employee_id = 1

    UNION ALL

    SELECT
        child.employee_id,
        child.name,
        child.manager_id,
        parent.depth + 1,
        parent.path || child.employee_id
    FROM org AS parent
    JOIN employee AS child
      ON child.manager_id = parent.employee_id
    WHERE NOT (child.employee_id = ANY(parent.path))
)
SELECT *
FROM org
ORDER BY path;

这里选择员工 1 作为根，而不是使用 manager_id IS NULL，因此可以查询任意组织子树。

7.3 用 LATERAL 为每个员工计算依赖其数据的聚合

WITH RECURSIVE org AS (
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        0 AS depth,
        ARRAY[e.employee_id] AS path
    FROM employee AS e
    WHERE e.employee_id = 1

    UNION ALL

    SELECT
        child.employee_id,
        child.name,
        child.manager_id,
        parent.depth + 1,
        parent.path || child.employee_id
    FROM org AS parent
    JOIN employee AS child
      ON child.manager_id = parent.employee_id
    WHERE NOT (child.employee_id = ANY(parent.path))
)
SELECT
    o.employee_id,
    o.name,
    o.depth,
    o.path,
    COALESCE(a.total_seconds, 0) AS total_seconds
FROM org AS o
LEFT JOIN LATERAL (
    SELECT
        sum(
            extract(epoch FROM (upper(available) - lower(available)))
        ) AS total_seconds
    FROM employee_availability AS a
    WHERE a.employee_id = o.employee_id
) AS a ON true
ORDER BY o.path;

右侧聚合对每个组织树节点分别计算。没有可用时间的员工仍然保留，因为使用了 LEFT JOIN LATERAL；聚合本身有一行，但 sum 可能为 NULL，所以外层使用 COALESCE。

这里通过 upper() 和 lower() 计算范围时长，隐含了一个前提：范围两端都是有限时间边界。如果允许无限边界，应先用 lower_inf()、upper_inf() 排除或单独定义业务含义。

7.4 在递归结果上使用窗口函数

先把总时长算出，再按层级排名：

WITH RECURSIVE org AS (
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        0 AS depth,
        ARRAY[e.employee_id] AS path
    FROM employee AS e
    WHERE e.employee_id = 1

    UNION ALL

    SELECT
        child.employee_id,
        child.name,
        child.manager_id,
        parent.depth + 1,
        parent.path || child.employee_id
    FROM org AS parent
    JOIN employee AS child
      ON child.manager_id = parent.employee_id
    WHERE NOT (child.employee_id = ANY(parent.path))
),
availability_totals AS (
    SELECT
        o.employee_id,
        o.name,
        o.depth,
        o.path,
        COALESCE(SUM(
            extract(epoch FROM (upper(a.available) - lower(a.available)))
        ), 0) AS total_seconds
    FROM org AS o
    LEFT JOIN employee_availability AS a
      ON a.employee_id = o.employee_id
    GROUP BY
        o.employee_id,
        o.name,
        o.depth,
        o.path
)
SELECT
    employee_id,
    name,
    depth,
    total_seconds,
    rank() OVER (
        PARTITION BY depth
        ORDER BY total_seconds DESC
    ) AS rank_in_depth
FROM availability_totals
ORDER BY depth, rank_in_depth, employee_id;

处理过程分为两步：

1. 递归 CTE 得到组织节点；
2. 普通聚合得到每个员工的总时长；
3. 外层窗口函数在已经聚合好的员工行上按 depth 分区排名。

不能把“每个员工的总时长聚合”和“对聚合结果排名”混成不清晰的一层。先明确行粒度，再使用窗口函数，结果更容易验证。

7.5 在范围排序后计算间隔

SELECT
    employee_id,
    available,
    lower(available) AS starts_at,
    upper(available) AS ends_at,
    lower(available)
      - lag(upper(available)) OVER (
            PARTITION BY employee_id
            ORDER BY lower(available), upper(available)
        ) AS gap_from_previous
FROM employee_availability
ORDER BY employee_id, starts_at;

对于每个员工：

• 当前范围按开始时间排序；
• lag(upper(available)) 取得上一个范围的结束时间；
• 当前开始时间减去上一个结束时间，得到间隔。

如果两个范围重叠，结果会是负间隔；如果相邻，结果为零；如果中间有空闲时间，结果为正间隔。

若业务要求先合并重叠或相邻范围，再计算空闲间隔，就不能直接依赖原始行的 lag。应先进行范围合并或使用多范围逻辑，再对合并后的区间排序。

---

八、常见误解、失败表现与诊断

8.1 “ORDER BY 了，所以结果一定稳定”

窗口函数中的 ORDER BY 只定义窗口内部的逻辑顺序，不自动定义最终结果集的输出顺序：

SELECT
    id,
    row_number() OVER (ORDER BY created_at) AS rn
FROM events;

如果需要最终按 rn 展示，仍然要写：

ORDER BY rn;

并且如果 created_at 不唯一，需要补充唯一键：

ORDER BY created_at, id

同样，array_agg 如果需要确定数组元素顺序，应在聚合内部写 ORDER BY，而不是依赖外层排序。

8.2 “默认窗口就是逐行累计”

不是。重复排序值会使默认 RANGE Frame 将 peer 行一起纳入。需要严格逐行累计时，显式写：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

这是累计金额、余额、库存变化等查询中的常见错误来源。

8.3 “范围端点相同就一定重叠”

不一定：

SELECT int4range(1, 5) && int4range(5, 10);

对于默认的半开范围 [1,5) 和 [5,10)，结果为假。边界是否包含必须纳入判断。

8.4 “数组就是集合”

数组具有：

• 顺序；
• 下标；
• 维度；
• 可能的非 1 下界；
• 元素级 NULL。

@> 是包含判断，但不应直接当作排序无关、重复计数严格的数学集合操作。若业务真正需要实体之间的多对多关系，应优先考虑关联表。

8.5 “递归 CTE 会自动防止死循环”

不会。UNION ALL 递归需要自己处理：

• 环；
• 重复路径；
• 无限深度；
• 异常分支数量。

诊断递归问题时，可先加入：

depth,
path,
is_cycle

并增加深度上限，再检查哪条数据链路导致了异常扩展。

8.6 “LATERAL 一定比窗口函数更快”

两者解决的问题不同。

如果每个客户只需取两条最新订单，且有：

(customer_id, created_at DESC)

这样的索引，LATERAL 可能非常适合。

如果要为每个客户处理几乎全部订单，再取排名或累计值，窗口函数通常更直接。最终应比较：

EXPLAIN (ANALYZE, BUFFERS)
...

重点观察：

• 是否出现重复的大表扫描；
• Nested Loop 的外层行数；
• 索引扫描是否使用了正确的条件；
• Sort 是否占用大量内存或发生磁盘临时文件；
• CTE 是否被物化；
• 实际行数是否严重偏离估算。

8.7 NULL 使“否定条件”变得不直观

例如：

WHERE NOT (5 = ANY(ARRAY[NULL, 6]))

数组中没有使比较为真的值，但 5 = NULL 是未知，整体可能得到 NULL，而不是 TRUE。WHERE 只保留结果为 TRUE 的行，因此该行会被过滤。

如果业务要求明确处理空元素，应先清理数组、使用 IS DISTINCT FROM，或把数组展开后进行显式的空值逻辑。

---

九、生产取舍与边界

9.1 用数据库约束表达不变量

范围排他约束适合表达“同一资源的时间区间不能重叠”。它比应用层检查更接近真正的不变量，因为数据库可以在并发事务中统一判断。

但约束冲突是正常业务分支，不一定是系统故障。应用层需要：

• 捕获唯一约束或排他约束错误；
• 回滚失败事务或回滚到保存点；
• 向用户返回“时间已被占用”等可理解的信息；
• 在可重试场景中重新读取状态。

9.2 数组与关系表的边界

数组可以减少表数量、方便整体读写，但会牺牲：

• 元素级外键约束；
• 元素独立索引；
• 元素级审计；
• 多对多关系的自然表达；
• 对单个元素的并发更新粒度。

数组更适合“一个不可再分的属性值中带有少量结构化元素”的场景，而不是所有一对多关系的替代方案。

9.3 递归查询的部署边界

递归 CTE 在一个 PostgreSQL 事务中是单条 SQL 的查询计算，不会自动跨多个数据库实例传播，也不会为外部服务调用提供事务回滚能力。

如果递归查询结果要驱动外部系统：

1. 数据库事务只能保证数据库内的原子性；
2. 外部消息、HTTP 调用或文件写入不自动纳入同一事务；
3. 需要使用事务消息、Outbox 等机制解决跨边界一致性。

这是 SQL 语义与系统部署边界的区别，不能通过在 SQL 中增加一层 CTE 消除。

9.4 窗口排序的资源成本

窗口函数需要按照分区和排序键处理数据。大数据集上可能产生排序、内存和临时文件开销。尤其是：

ORDER BY random()

或大范围的复杂 Frame，可能非常昂贵。

诊断时使用：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...

但要记住：

• ANALYZE 会执行语句；
• 对写操作应在测试事务中执行并回滚；
• 生产环境应评估锁、缓存污染和临时空间影响。

---

LATERAL 解决的是“右侧关系依赖左侧当前行”；递归 CTE 解决的是“从初始集合反复应用关系规则直到不再产生新结果”；窗口函数解决的是“保留明细行，同时在有序分区上计算”；数组和范围则分别提供了有序元素集合与连续区间的值语义。

这几类能力组合后，可以在单条 SQL 中表达分组 Top-N、层级遍历、路径追踪、累计分析、时间间隔、区间冲突检测和资源排他约束。真正可靠的查询仍然需要明确三件事：输入行粒度、比较边界以及并发事务边界。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：MySQL 升级与迁移：兼容检查、DDL、字符集、灰度和回滚
• 下一篇：PostgreSQL 存储内部：Heap Page、Tuple、TOAST、FSM 和可见性图
• 延伸：PostgreSQL 数据类型与 Schema：数组、范围、JSONB、约束和域
• 延伸：SQL 窗口函数：分区、排序、Frame、排名、累计和间隔分析

官方资料

• PostgreSQL Current Documentation
• PostgreSQL Internals

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。