PHP小码农

Appearance

LATERAL 实战场景 ​

前两节讲了 LATERAL 的语法和它与相关子查询、窗口函数的取舍。这一节把这些知识落到具体场景上——哪些现实需求用 LATERAL 写起来最自然，用别的写法反而别扭。

LATERAL 的价值集中在两类需求上：

  1. 逐行取子集：左表每一行需要到右表动态挑选一部分记录（Top-N、最近一条、满足某条件的若干条）；
  2. 逐行做展开：左表每一行存了一个「复合值」（数组、JSONB、分隔字符串），需要把它摊成多行；或者反过来，左表每行需要补出一段连续序列（日期、序号）用于零填充。

学完本节，你能独立写出这六类需求的 LATERAL 方案，并知道每种的性能陷阱在哪：

  • 组内 Top-N（每用户最近 N 笔）；
  • 数组元素展开为多行；
  • JSONB 数组展开为结构化多行；
  • generate_series 补日期，实现零填充报表；
  • 字符串按分隔符拆分为多行；
  • 逐行取多个不同子集（多列多行一起返回）。

前置知识

阅读本节前，建议先了解：LATERAL 与相关子查询

基础概念 ​

本节的场景涉及几类「一行变多行」的机制，先对齐术语：

概念术语说明
集合返回函数Set-Returning Function（SRF）一行输入、多行输出的函数，如 unnest、generate_series
展开Unnest / Expand把数组或 JSON 数组的每个元素变成结果里的一行
零填充Zero Filling统计报表中把没有数据的日期补成 0，保证时间轴连续
序号列Ordinality元素在数组中的位置序号，由 WITH ORDINALITY 提供
逐行取子集Per-Row Subset左表每行独立到右表筛选出一批记录
行放大Row Multiplication展开或连接后结果行数变多，需注意后续聚合的粒度
侧向引用Lateral Reference子查询里引用左侧 FROM 项的列
隐式侧向Implicit LATERALSRF 出现在 FROM 中时自动获得侧向引用能力
双展开Double Unnest同时展开两个数组，需用 WITH ORDINALITY 对齐下标

其中最容易忽略的是双展开对齐问题：unnest(a), unnest(b) 不等价于「按位置配对展开」，它会做笛卡尔积。需要在同一个 LATERAL 里按序号对齐。

语法与用法 ​

六类场景的骨架对比如下：

sql
-- 场景 1：组内 Top-N（内层 LIMIT）
SELECT u.id, o.order_no
FROM users u
LEFT JOIN LATERAL (
  SELECT order_no FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC LIMIT 3
) o ON TRUE;

-- 场景 2：数组展开（SRF 隐式侧向，无需写 LATERAL）
SELECT u.id, tag
FROM users u
CROSS JOIN LATERAL unnest(u.tags) AS tag;

-- 场景 3：JSONB 数组展开
SELECT u.id, item->>'sku' AS sku
FROM users u
CROSS JOIN LATERAL jsonb_array_elements(u.profile->'items') AS item;

-- 场景 4：补日期序列（零填充）
SELECT d.stat_date, coalesce(s.amount, 0) AS amount
FROM demo_days d
LEFT JOIN LATERAL (
  SELECT amount FROM daily_sales s WHERE s.stat_date = d.stat_date
) s ON TRUE;

-- 场景 5：字符串拆分
SELECT u.id, part
FROM users u
CROSS JOIN LATERAL unnest(string_to_array(u.remark, ',')) AS part;

-- 场景 6：逐行取多个子集
SELECT u.id, recent.order_no, total.total
FROM users u
LEFT JOIN LATERAL (
  SELECT order_no FROM orders o
  WHERE o.user_id = u.id ORDER BY o.created_at DESC LIMIT 1
) recent ON TRUE
LEFT JOIN LATERAL (
  SELECT sum(amount) AS total FROM orders o WHERE o.user_id = u.id
) total ON TRUE;

关键规律：

需求类型推荐形态是否需要显式 LATERAL
右表动态取子集LEFT JOIN LATERAL ... ON TRUE需要
左表列直接展开（数组/JSON/字符串）CROSS JOIN LATERAL func(...)可省，SRF 隐式侧向
补序列并左连接回明细LEFT JOIN LATERAL (SELECT ...)需要
同一左行取多个独立子集多个 LEFT JOIN LATERAL需要

详细说明 ​

为什么展开类场景不需要写 LATERAL ​

PostgreSQL 对「FROM 中的集合返回函数」有特殊规则：函数参数里出现左侧列时，会自动按侧向处理，所以下面两种写法等价：

sql
-- 写法 A：不写 LATERAL
SELECT u.id, tag
FROM demo_lu_users u, unnest(u.tags) AS tag;

-- 写法 B：显式写 LATERAL（更清晰，推荐）
SELECT u.id, tag
FROM demo_lu_users u
CROSS JOIN LATERAL unnest(u.tags) AS tag;

但这条规则只适用于函数调用，不适用于子查询。FROM (SELECT ... WHERE t.col = ...) s 依然必须写 LATERAL。这是新手最容易混的一处。

双数组展开必须对齐下标 ​

如果一个 JSONB 或数组场景需要同时展开「名称数组」和「数量数组」，不能简单并列两个 unnest：

sql
-- 错误：两个 unnest 并列会做笛卡尔积（4 行而不是 2 行）
SELECT u.id, name, qty
FROM demo_lu_users u, unnest(u.names) name, unnest(u.qtys) qty;

正确做法是用 WITH ORDINALITY 拿到序号，再按序号连接：

sql
-- 正确：按序号配对，结果行数与数组长度一致
SELECT u.id, n.name, q.qty
FROM demo_lu_users u
CROSS JOIN LATERAL unnest(u.names) WITH ORDINALITY AS n(name, ord)
JOIN LATERAL unnest(u.qtys) WITH ORDINALITY AS q(qty, ord) ON q.ord = n.ord;

零填充：为什么用 LATERAL 而不是 LEFT JOIN ​

「补日期」这类需求，标准做法是先生成完整日期序列，再左连接明细：

sql
SELECT d.stat_date, coalesce(s.amount, 0) AS amount
FROM demo_lu_days d
LEFT JOIN LATERAL (
  SELECT amount FROM demo_lu_sales s WHERE s.stat_date = d.stat_date
) s ON TRUE;

这里的 LATERAL 内层其实只可能返回 0 行或 1 行，用普通 LEFT JOIN 也能写：

sql
SELECT d.stat_date, coalesce(s.amount, 0) AS amount
FROM demo_lu_days d
LEFT JOIN demo_lu_sales s ON s.stat_date = d.stat_date;

那为什么还提 LATERAL？因为在真实报表里，「每个日期」往往还要顺带取多个不同粒度的值（当日订单数、当日新增用户数、当日退款金额），这些来自不同的表且都按日期匹配。此时把每个子集写成一个 LATERAL 比串联多个 LEFT JOIN 更清晰，也更容易逐个子集单独优化。

如果只有一对一的日期匹配，用普通 LEFT JOIN 就够了——不要为了用 LATERAL 而用 LATERAL。

实战示例 ​

步骤 1：准备演示数据 ​

sql
-- 演示库统一为 demo
CREATE TABLE demo_lu_users (
  id       integer PRIMARY KEY,
  username text    NOT NULL,
  tags     text[]  NOT NULL DEFAULT '{}',
  profile  jsonb   NOT NULL DEFAULT '{}'::jsonb,
  remark   text    NOT NULL DEFAULT ''
);

CREATE TABLE demo_lu_orders (
  id         bigserial PRIMARY KEY,
  user_id    integer       NOT NULL,
  order_no   text          NOT NULL,
  amount     numeric(10,2) NOT NULL,
  created_at date          NOT NULL
);

INSERT INTO demo_lu_users (id, username, tags, profile, remark) VALUES
  (1, 'alice', ARRAY['vip', 'new'],
   '{"items": [{"sku": "S-100", "qty": 2}, {"sku": "S-200", "qty": 1}]}',
   'red,large,waterproof'),
  (2, 'bob',   ARRAY['vip'],
   '{"items": [{"sku": "S-300", "qty": 5}]}',
   'blue,small'),
  (3, 'carol', ARRAY[]::text[],
   '{"items": []}',
   '');

INSERT INTO demo_lu_orders (user_id, order_no, amount, created_at) VALUES
  (1, 'A-001', 500.00, '2026-01-05'),
  (1, 'A-002', 300.00, '2026-02-10'),
  (1, 'A-003', 200.00, '2026-03-15'),
  (1, 'A-004', 150.00, '2026-04-20'),
  (2, 'B-001', 800.00, '2026-05-01'),
  (2, 'B-002', 120.00, '2026-06-08');

carol 是空数组、空 JSON、空字符串的代表，用来验证 CROSS JOIN 会丢行、LEFT JOIN LATERAL 会保留行。

场景 1：组内 Top-N（每用户最近 3 笔） ​

sql
SELECT u.id,
       u.username,
       o.order_no,
       o.amount,
       o.created_at
FROM demo_lu_users u
LEFT JOIN LATERAL (
  SELECT order_no, amount, created_at
  FROM demo_lu_orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC, o.id DESC
  LIMIT 3
) o ON TRUE
ORDER BY u.id, o.created_at DESC NULLS LAST;
text
 id | username | order_no | amount | created_at
----+----------+----------+--------+------------
  1 | alice    | A-004    | 150.00 | 2026-04-20
  1 | alice    | A-003    | 200.00 | 2026-03-15
  1 | alice    | A-002    | 300.00 | 2026-02-10
  2 | bob      | B-002    | 120.00 | 2026-06-08
  2 | bob      | B-001    | 800.00 | 2026-05-01
  3 | carol    |          |        |
(6 rows)

配套索引：

sql
CREATE INDEX idx_lu_orders_user_created ON demo_lu_orders (user_id, created_at DESC, id DESC);

改用 CROSS JOIN LATERAL 时 carol 那行会消失，只剩 5 行。

场景 2：数组展开为多行 ​

sql
SELECT u.id,
       u.username,
       tag
FROM demo_lu_users u
CROSS JOIN LATERAL unnest(u.tags) AS tag
ORDER BY u.id, tag;
text
 id | username | tag
----+----------+-----
  1 | alice    | new
  1 | alice    | vip
  2 | bob      | vip
(3 rows)

carol 没有标签，CROSS JOIN 直接把她过滤掉了。要保留她（标签列为空），改用 LEFT JOIN LATERAL ... ON TRUE：

sql
SELECT u.id,
       u.username,
       tag
FROM demo_lu_users u
LEFT JOIN LATERAL unnest(u.tags) AS tag ON TRUE
ORDER BY u.id, tag NULLS LAST;
text
 id | username | tag
----+----------+-----
  1 | alice    | new
  1 | alice    | vip
  2 | bob      | vip
  3 | carol    |
(4 rows)

需要保留数组元素顺序时，用 WITH ORDINALITY：

sql
SELECT u.id,
       t.tag,
       t.ord
FROM demo_lu_users u
CROSS JOIN LATERAL unnest(u.tags) WITH ORDINALITY AS t(tag, ord)
ORDER BY u.id, t.ord;

场景 3：JSONB 数组展开为结构化多行 ​

sql
SELECT u.id,
       u.username,
       item->>'sku'            AS sku,
       (item->>'qty')::integer AS qty
FROM demo_lu_users u
CROSS JOIN LATERAL jsonb_array_elements(u.profile->'items') AS item
ORDER BY u.id, sku;
text
 id | username |  sku  | qty
----+----------+-------+-----
  1 | alice    | S-100 |   2
  1 | alice    | S-200 |   1
  2 | bob      | S-300 |   5
(3 rows)

只想取纯文本值时用 jsonb_array_elements_text，省掉 ->>：

sql
SELECT u.id, sku
FROM demo_lu_users u
CROSS JOIN LATERAL jsonb_array_elements_text(u.profile->'items') AS sku;

注意 jsonb_array_elements 要求目标必须是 JSON 数组。如果 profile->'items' 是对象或 NULL，会报错；生产代码里建议先用 jsonb_typeof 或 coalesce(..., '[]'::jsonb) 兜底：

sql
SELECT u.id, item->>'sku' AS sku
FROM demo_lu_users u
CROSS JOIN LATERAL jsonb_array_elements(
  CASE WHEN jsonb_typeof(u.profile->'items') = 'array'
       THEN u.profile->'items'
       ELSE '[]'::jsonb
  END
) AS item;

场景 4：generate_series 补日期（零填充报表） ​

sql
-- 先生成完整日期序列，再按日期左连接当日汇总
WITH days AS (
  SELECT generate_series('2026-04-18'::date,
                         '2026-04-22'::date,
                         interval '1 day')::date AS stat_date
)
SELECT d.stat_date,
       coalesce(s.amount, 0) AS amount
FROM days d
LEFT JOIN LATERAL (
  SELECT sum(o.amount) AS amount
  FROM demo_lu_orders o
  WHERE o.created_at = d.stat_date
) s ON TRUE
ORDER BY d.stat_date;
text
 stat_date  | amount
------------+---------
 2026-04-18 |       0
 2026-04-19 |       0
 2026-04-20 |  150.00
 2026-04-21 |       0
 2026-04-22 |       0

amount 为 NULL 时用 coalesce 补 0，这是零填充的核心一步——否则图表上会出现断点。

generate_series 也可以直接放在 FROM 里（隐式侧向），不必包在 CTE 中：

sql
SELECT d.stat_date, coalesce(s.amount, 0) AS amount
FROM generate_series('2026-04-18'::date,
                     '2026-04-22'::date,
                     interval '1 day')::date AS d(stat_date)
LEFT JOIN LATERAL (
  SELECT sum(o.amount) AS amount
  FROM demo_lu_orders o
  WHERE o.created_at = d.stat_date
) s ON TRUE
ORDER BY d.stat_date;

场景 5：字符串按分隔符拆分为多行 ​

sql
SELECT u.id,
       trim(part) AS part
FROM demo_lu_users u
CROSS JOIN LATERAL unnest(string_to_array(u.remark, ',')) AS part
WHERE u.remark <> ''
ORDER BY u.id, part;
text
 id |    part
----+-------------
  1 | large
  1 | red
  1 | waterproof
  2 | blue
  2 | small
(5 rows)

PostgreSQL 14+ 可以用更语义化的 string_to_table，效果相同且不必再包 unnest：

sql
-- PostgreSQL 14+
SELECT u.id,
       trim(part) AS part
FROM demo_lu_users u
CROSS JOIN LATERAL string_to_table(u.remark, ',') AS part
WHERE part <> ''
ORDER BY u.id, part;

string_to_table 会把连续分隔符产生的空串也返回（本例中 'red,,blue' 会得到一行空串），所以常加 WHERE part <> '' 过滤空项。

场景 6：逐行取多个不同子集 ​

「最近一单」和「累计总额」来自同一张表但聚合粒度不同，写成两个独立的 LATERAL 最清晰：

sql
SELECT u.id,
       u.username,
       recent.order_no AS 最近一单,
       recent.created_at AS 最近日期,
       total.total AS 累计金额,
       total.cnt AS 订单数
FROM demo_lu_users u
LEFT JOIN LATERAL (
  SELECT order_no, created_at
  FROM demo_lu_orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC, o.id DESC
  LIMIT 1
) recent ON TRUE
LEFT JOIN LATERAL (
  SELECT sum(amount) AS total, count(*) AS cnt
  FROM demo_lu_orders o
  WHERE o.user_id = u.id
) total ON TRUE
ORDER BY u.id;
text
 id | username | 最近一单 | 最近日期   | 累计金额 | 订单数
----+----------+----------+------------+----------+--------
  1 | alice    | A-004    | 2026-04-20 |  1150.00 |      4
  2 | bob      | B-002    | 2026-06-08 |   920.00 |      2
  3 | carol    |          |            |          |      0

注意 carol 的 订单数 是 0 而不是 NULL：count(*) 在无行时返回 0，而 sum(amount) 返回 NULL（那列留空）。这个差异在做除法前必须处理，否则会出现除零或空值。

两个 LATERAL 分别被优化：第二个（纯聚合）会被去关联成一次 HashAggregate，第一个（带 LIMIT）必然逐行执行。所以同一个查询里可以同时存在「被去关联」和「逐行执行」两种 LATERAL，优化时要用 EXPLAIN 分别看。

场景 7：用 EXPLAIN 定位哪个 LATERAL 是热点 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, recent.order_no, total.total
FROM demo_lu_users u
LEFT JOIN LATERAL (
  SELECT order_no FROM demo_lu_orders o
  WHERE o.user_id = u.id ORDER BY o.created_at DESC, o.id DESC LIMIT 1
) recent ON TRUE
LEFT JOIN LATERAL (
  SELECT sum(amount) AS total FROM demo_lu_orders o WHERE o.user_id = u.id
) total ON TRUE;
text
Nested Loop Left Join  (cost=... rows=... width=...) (actual time=... rows=3 loops=1)
  Hash Cond: (o.user_id = u.id)
  ->  Nested Loop Left Join  (actual time=... rows=3 loops=1)
        ->  Seq Scan on demo_lu_users u  (actual time=0.006..0.009 rows=3 loops=1)
        ->  Limit  (actual time=0.010..0.012 rows=1 loops=3)
              ->  Sort  (Sort Key: o.created_at DESC, o.id DESC)
                    ->  Index Scan using idx_lu_orders_user_created on demo_lu_orders o
                          Index Cond: (user_id = u.id)
  ->  Hash  (actual time=... rows=2 loops=1)
        ->  HashAggregate  (Group Key: o.user_id)
              ->  Seq Scan on demo_lu_orders o

读法：

  • 带 Limit 的那个分支是 loops=3（等于左表 3 行），说明逐行执行——这是热点；
  • HashAggregate 分支是 loops=1，已被去关联——这不是热点；
  • 因此优化重点只放在第一个 LATERAL 的内层索引上，第二个不用管。

场景 8：验证 CROSS 与 LEFT 的行数差异 ​

sql
-- CROSS：空数组的直接丢弃
SELECT count(*) AS cross_行数
FROM demo_lu_users u
CROSS JOIN LATERAL unnest(u.tags) AS tag;
text
 cross_行数
------------
          3
sql
-- LEFT：空数组的保留为一行
SELECT count(*) AS left_行数
FROM demo_lu_users u
LEFT JOIN LATERAL unnest(u.tags) AS tag ON TRUE;
text
 left_行数
-----------
         4

3 比 4 的差就是 carol 那一行。在报表场景里，这类「静默丢行」是最危险的问题，因为行数变少了但没人会立刻发现。

注意事项 ​

  1. CROSS JOIN LATERAL 会静默丢弃无匹配的左表行，报表类需求一律用 LEFT JOIN LATERAL ... ON TRUE。

  2. 任何「一行变多行」的操作都会改变粒度，后续如需按原粒度聚合，要先想清楚是否需要 DISTINCT 或子查询隔离。

  3. 两个数组并列 unnest 会做笛卡尔积，需要按位置配对时必须用 WITH ORDINALITY 对齐序号。

  4. jsonb_array_elements 遇到非数组或 NULL 会报错，生产代码要用 jsonb_typeof 或 coalesce 兜底。

  5. string_to_table 需要 PostgreSQL 14+，低版本用 string_to_array + unnest。

  6. WITH ORDINALITY 需要 PostgreSQL 9.4+。

  7. 空数组展开后是 0 行，CROSS 会丢行、LEFT 会保一行且值为 NULL，两者语义不同，不能混用。

  8. count(*) 在无行时返回 0，sum() 返回 NULL，同一查询里两者可能混用，算术前必须 coalesce。

  9. 补日期时 generate_series 的结束日期是含端点的，跨月时用 interval '1 day' 递增比用整数递增更安全（不受月份天数影响）。

  10. LATERAL 内层不带索引时，逐行执行会退化成 N 次全表扫描，行数一多就是性能灾难。

  11. 多个 LATERAL 的开销不相加而是相乘，两层逐行子查询叠加时，实际执行次数是左表行数的平方级别。

最佳实践 ​

  1. 展开左表已有列（数组/JSON/字符串）用 CROSS JOIN LATERAL，语义就是「把每行摊开」，配合 LEFT JOIN 可以保留空值行。

  2. 需要保留数组顺序就用 WITH ORDINALITY，并把序号列带进结果：

    sql
    SELECT u.id, t.tag, t.ord
FROM demo_lu_users u
CROSS JOIN LATERAL unnest(u.tags) WITH ORDINALITY AS t(tag, ord)
ORDER BY u.id, t.ord;

  3. 为 Top-N 的内层建「关联列 + 排序键」复合索引，让逐行执行退化为索引查找：

    内层查询形态建议索引
    WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3(user_id, created_at DESC, id DESC)
    WHERE user_id = u.id ORDER BY amount DESC LIMIT 3(user_id, amount DESC, id)
    WHERE stat_date = d.stat_date(stat_date)

  4. 零填充报表用 generate_series + LEFT JOIN LATERAL，并用 coalesce(x, 0) 把缺失值补成 0。

  5. 多个不同子集用多个独立 LATERAL，每个只负责一件事，比把所有逻辑塞进一个子查询更好读、更好单独优化。

  6. 在 LATERAL 之前先过滤左表，减少循环次数；能用 WHERE 先排除的行，不要留给内层处理。

  7. 用 EXPLAIN (ANALYZE, BUFFERS) 逐个分支看 loops，只优化 loops > 1 且成本高的那个内层子查询。

  8. 报表类需求默认用 LEFT JOIN LATERAL ... ON TRUE，除非业务明确要求「只统计有数据的对象」。

  9. 展开类场景先确认数据形态：数组是否可能为 NULL、JSON 字段是否一定是数组、字符串是否可能为空——这些边界决定了用 CROSS 还是 LEFT，以及要不要 CASE 兜底。

下一节

继续学习：GROUPING SETS

参考链接 ​