PostgreSQL LATERAL 关键字详解

一、什么是 LATERAL

LATERAL 允许 FROM 子句中后面的子查询/表函数引用前面表的列。

一句话理解：让 JOIN 右侧的子查询可以"看到"左侧每一行的值，对每一行单独执行一次。

二、为什么需要 LATERAL

普通 JOIN 的限制

-- ❌ 这样写会报错: column "u.id" does not exist
SELECT u.name, t.title
FROM users u,
     (SELECT title FROM posts WHERE user_id = u.id LIMIT 3) t;
-- 错误: 子查询无法引用外层的 u.id

LATERAL 解决了这个问题

-- ✅ 加 LATERAL 就可以引用外层
SELECT u.name, t.title
FROM users u,
     LATERAL (SELECT title FROM posts WHERE user_id = u.id LIMIT 3) t;

三、LATERAL 的核心特点

特性 说明
逐行执行 左表每一行,右侧子查询执行一次
可引用外层列 右侧子查询能看到左侧表的所有列
类似函数迭代 行为上类似编程语言的 for 循环
常配合 LIMIT 经典场景:每组取 N 条
类似 Oracle CROSS APPLY SQL Server / Oracle 的 CROSS APPLY 等价

四、典型使用场景

场景 1：每个用户的最新 N 条记录（最经典用法）⭐

需求：每个用户取最新的 3 条订单

-- 用 LATERAL（推荐写法）
SELECT u.id, u.name, o.order_no, o.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT order_no, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o;

对比传统写法（用窗口函数）：

-- 传统写法：先查全部再过滤
SELECT id, name, order_no, created_at
FROM (
    SELECT u.id, u.name, o.order_no, o.created_at,
           ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.created_at DESC) AS rn
    FROM users u
    JOIN orders o ON u.id = o.user_id
) t
WHERE rn <= 3;

LATERAL 优势：

  • 子查询只取需要的 3 条，不需要扫描全部订单
  • 当订单表巨大时，性能远超窗口函数

场景 2：与表函数配合（如 unnest、generate_series）

-- 把数组列拆成多行
SELECT u.id, t.tag
FROM users u,
     LATERAL unnest(u.tags) AS t(tag);
-- 这里 LATERAL 可省略,PG 自动识别表函数

-- 为每个订单生成分期还款计划
SELECT o.id, o.amount, m.month_no, o.amount / o.installments AS payment
FROM orders o,
     LATERAL generate_series(1, o.installments) AS m(month_no);

场景 3：动态计算字段，避免重复表达式

需求：计算订单的折扣价、税金、最终价格

-- ❌ 不用 LATERAL，重复计算
SELECT
    order_no,
    amount,
    amount * 0.9 AS discounted,
    amount * 0.9 * 0.13 AS tax,
    amount * 0.9 * 1.13 AS final_price
FROM orders;

-- ✅ 用 LATERAL,引用计算结果
SELECT
    o.order_no,
    o.amount,
    calc.discounted,
    calc.tax,
    calc.final_price
FROM orders o,
     LATERAL (
         SELECT
             o.amount * 0.9 AS discounted,
             o.amount * 0.9 * 0.13 AS tax,
             o.amount * 0.9 * 1.13 AS final_price
     ) calc;

代码更清晰，且计算只执行一次。

场景 4：聚合 + 关联（每个分类的统计 + 明细）

-- 每个分类的总销售额 + 销售额最高的商品
SELECT
    c.category_name,
    stats.total_sales,
    top.product_name,
    top.sales
FROM categories c
JOIN LATERAL (
    SELECT SUM(sales) AS total_sales
    FROM products
    WHERE category_id = c.id
) stats ON true
JOIN LATERAL (
    SELECT product_name, sales
    FROM products
    WHERE category_id = c.id
    ORDER BY sales DESC
    LIMIT 1
) top ON true;

场景 5：JSON/JSONB 字段展开

-- 展开 JSON 数组中每个元素
CREATE TABLE events (
    id INT,
    payload JSONB
);

INSERT INTO events VALUES
(1, '{"items":[{"name":"a","qty":2},{"name":"b","qty":5}]}');

SELECT e.id, item->>'name' AS item_name, (item->>'qty')::int AS qty
FROM events e,
     LATERAL jsonb_array_elements(e.payload->'items') AS item;

-- 输出：
-- id | item_name | qty
-- 1  | a         | 2
-- 1  | b         | 5

场景 6：解决 “Top N per group” 类需求

需求：每个城市气温最高的 2 天

SELECT c.name, t.measure_date, t.high_temp
FROM cities c
CROSS JOIN LATERAL (
    SELECT measure_date, high_temp
    FROM weather
    WHERE city_id = c.id
    ORDER BY high_temp DESC
    LIMIT 2
) t;

五、LATERAL 的两种写法

写法 1：CROSS JOIN LATERAL（左侧无匹配也保留？看具体）

SELECT u.name, p.title
FROM users u
CROSS JOIN LATERAL (
    SELECT title FROM posts WHERE user_id = u.id LIMIT 3
) p;
-- 行为：用户没有 post 时，该用户不出现在结果中

写法 2：LEFT JOIN LATERAL … ON true（左侧无匹配也保留）

SELECT u.name, p.title
FROM users u
LEFT JOIN LATERAL (
    SELECT title FROM posts WHERE user_id = u.id LIMIT 3
) p ON true;
-- 行为：即使用户没有 post,该用户依然出现,p.title 为 NULL

⚠️ LATERAL 后面的 JOIN 必须用 ON true 或 ON 1=1，因为关联条件已写在子查询内部。

六、LATERAL vs 窗口函数 vs DISTINCT ON 对比

需求：每个用户的最新 1 条订单

写法 1：LATERAL

SELECT u.id, u.name, o.order_no, o.created_at
FROM users u
LEFT JOIN LATERAL (
    SELECT order_no, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) o ON true;

写法 2：窗口函数

SELECT id, name, order_no, created_at
FROM (
    SELECT u.id, u.name, o.order_no, o.created_at,
           ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.created_at DESC) rn
    FROM users u LEFT JOIN orders o ON u.id = o.user_id
) t WHERE rn = 1;

写法 3：DISTINCT ON（PG 特有）

SELECT DISTINCT ON (u.id) u.id, u.name, o.order_no, o.created_at
FROM users u LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.created_at DESC;

性能对比

场景 推荐方案
每组取 少量 行（Top 1~10） LATERAL ⭐
每组取所有行并加序号 窗口函数
每组只取第 1 行 DISTINCT ON（语法最简洁）
涉及复杂计算逻辑 LATERAL（最灵活）

LATERAL 性能优势：

  • 子查询可以用上索引（如 (user_id, created_at DESC)）
  • 每个用户只扫描 N 行就停，不像窗口函数要排序整张表

七、性能优化要点

1. 给关联列建索引

-- LATERAL 子查询的 WHERE 和 ORDER BY 列必须有合适索引
CREATE INDEX idx_orders_user_created
    ON orders(user_id, created_at DESC);

2. 用 EXPLAIN ANALYZE 验证

EXPLAIN ANALYZE
SELECT u.id, o.order_no
FROM users u
CROSS JOIN LATERAL (
    SELECT order_no FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC LIMIT 3
) o;

期望看到：

->  Nested Loop
      ->  Seq Scan on users
      ->  Limit
            ->  Index Scan using idx_orders_user_created on orders
                  Index Cond: (user_id = u.id)

如果出现 Seq Scan on orders 就说明索引没用上。

八、与 SQL Server / Oracle 的对应关系

PostgreSQL SQL Server / Oracle
CROSS JOIN LATERAL CROSS APPLY
LEFT JOIN LATERAL ... ON true OUTER APPLY 
-- Oracle / SQL Server 写法
SELECT u.name, o.order_no
FROM users u
CROSS APPLY (
    SELECT TOP 3 order_no
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
) o;

-- PostgreSQL 等价写法
SELECT u.name, o.order_no
FROM users u
CROSS JOIN LATERAL (
    SELECT order_no FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC LIMIT 3
) o;

九、常见陷阱

1. 忘记 ON true

-- ❌ 报错: missing ON clause
LEFT JOIN LATERAL (SELECT ...) sub;

-- ✅ 必须加 ON true
LEFT JOIN LATERAL (SELECT ...) sub ON true;

2. 滥用导致性能问题

-- ❌ 大表的 LATERAL 子查询没用索引,每行执行一次全表扫描
SELECT u.id, t.cnt
FROM users u  -- 100万行
CROSS JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM logs WHERE description LIKE '%' || u.name || '%'
) t;
-- 100万次全表扫描,极慢

3. 与普通 JOIN 混用顺序

-- ❌ LATERAL 必须在引用的表之后
SELECT ...
FROM LATERAL (SELECT ... FROM orders WHERE user_id = u.id) o,
     users u;
-- 错误: u 还没定义

-- ✅ 正确顺序
FROM users u, LATERAL (SELECT ... WHERE user_id = u.id) o;

十、实战完整示例

示例：电商运营报表

需求：列出每个商家的"最近 5 笔订单 + 累计销售额 + 最热商品"

SELECT
    m.id,
    m.merchant_name,
    stats.total_sales,
    stats.order_count,
    hot.top_product,
    recent.recent_orders
FROM merchants m
-- 累计统计
LEFT JOIN LATERAL (
    SELECT SUM(amount) AS total_sales,
           COUNT(*)    AS order_count
    FROM orders
    WHERE merchant_id = m.id
      AND created_at >= NOW() - INTERVAL '30 days'
) stats ON true
-- 最热商品
LEFT JOIN LATERAL (
    SELECT product_name AS top_product
    FROM order_items
    WHERE merchant_id = m.id
    GROUP BY product_name
    ORDER BY SUM(quantity) DESC
    LIMIT 1
) hot ON true
-- 最近 5 笔订单（拼成数组）
LEFT JOIN LATERAL (
    SELECT array_agg(order_no ORDER BY created_at DESC) AS recent_orders
    FROM (
        SELECT order_no, created_at
        FROM orders
        WHERE merchant_id = m.id
        ORDER BY created_at DESC
        LIMIT 5
    ) t
) recent ON true
WHERE m.is_active = true;

一条 SQL 完成多维度统计，易读且高效。

一句话总结

LATERAL 让 FROM 子句中的子查询能"看到"前面表的列，逐行执行。它最适合解决 “Top N per group”、展开 JSON/数组、复杂行级计算 等场景。在大多数 “每组取 Top N” 的需求中，LATERAL + 合适的索引比窗口函数快得多，是 PostgreSQL 高级 SQL 的必备工具。

如果你有具体的查询场景，可以贴出来，我帮你判断是否适合用 LATERAL 改写。

# postgresql
Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

四天蒸发近2000亿，宇树科技股价近乎“腰斩”；苹果举办库克告别派对：9月1日正式卸任CEO；Anthropic两款新Claude模型曝光 | 极客头条

avatar AI编程社区
cover

Claude Code反超GitHub Copilot登顶第一、90%程序员已用上Agent，最新AI编码调查报告来了

avatar AI编程社区
cover

如何用好 Claude Code 写后端代码

avatar AI编程社区