PostgreSQL LATERAL JOIN 使用指南-未完纪

你是否遇到过需要在子查询中引用某张表的情况？例如下面的查询，尝试在子查询中引用 accounts：

 SELECT
  accounts.id,
  accounts.name,
  last_purchase.*
FROM
  accounts
INNER JOIN (SELECT
              *
            FROM purchases
            WHERE account_id = accounts.id
            ORDER BY created_at DESC
            LIMIT 1
           ) AS last_purchase ON true;

结果却出现错误：

ERROR:  invalid reference to FROM-clause entry for table "accounts"
LINE 9:  WHERE account_id = accounts.id
                            ^
HINT:  There is an entry for table "accounts", but it cannot be referenced from this part of the query.

引入 LATERAL

人们通常把它称为“LATERAL JOIN”，但 LATERAL 本身只是一个关键字，让子查询能够引用上层查询中的表。以下使用 INNER JOIN LATERAL 查找每个账户最近一次购买；与上面的查询相比，唯一变化就是加入 LATERAL：

 SELECT
  accounts.id,
  accounts.name,
  last_purchase.*
FROM
  accounts
INNER JOIN LATERAL (SELECT
                      *
                    FROM purchases
                    WHERE account_id = accounts.id
                    ORDER BY created_at DESC
                    LIMIT 1
                   ) AS last_purchase ON true;

这个查询遍历 accounts，对每个 account.id 查找最近的购买记录，并将结果限制为一条。LATERAL 允许子查询引用 accounts，根据其中的值筛选 purchases。

LATERAL 可以与连接类型配合，也可以使用隐式连接：

 SELECT
	accounts.id,
	last_purchase.*
FROM
	accounts, LATERAL (SELECT
                       *
                     FROM purchases
                     WHERE account_id = accounts.id
                     ORDER BY created_at DESC
                     LIMIT 1
                    ) AS last_purchase;

这里数据量较小，LATERAL 工作良好。对于更大的数据集，本例使用 GROUP BY 可能更适合。记住，LATERAL 的工作方式类似逐行循环：在本例中，accounts 有多少条记录，子查询就会求值多少次。因此，如果追求性能或扩展性，可以考虑下面的 GROUP BY 方案。

用 GROUP BY 解决类似问题

在 LATERAL 之前，同一需求可以通过 GROUP BY 完成。下面在 CTE 中按 account_id 分组，找到最大的 purchases.created_at，然后按对应值连接 accounts 与 purchases：

 WITH latest_purchase_per_account AS (
	SELECT
		account_id,
		MAX(purchases.created_at) AS created_at
	FROM purchases
	GROUP BY 1
)

SELECT
	accounts.id,
	purchases.*
FROM latest_purchase_per_account
	INNER JOIN accounts ON latest_purchase_per_account.account_id = accounts.id
	INNER JOIN purchases ON latest_purchase_per_account.created_at = purchases.created_at
		AND latest_purchase_per_account.account_id = purchases.account_id;

原作者预计，在行数很多时，GROUP BY 会比 LATERAL 快得多。但每种场景都有差异，不能一概而论。重要的是掌握解决同一问题的两种模式。

用 LATERAL 处理 JSONB

与很多 SQL 功能一样，LATERAL 解决的是简单问题，却能组合起来解决复杂需求。它经常用于 GIS 函数与 JSON。

下面利用 LATERAL 找出 JSON 结构中的匹配子元素，并通过条件返回位于 California 的所有地址：

 SELECT
  accounts.id,
  accounts.name,
  address_elements.value->>'state' AS state,
  address_elements.value->>'city' AS city
FROM
  accounts,
  LATERAL jsonb_array_elements(accounts.addresses) AS address_elements
WHERE
  address_elements.value->>'state' = 'California';

这里将 LATERAL 与 jsonb_array_elements 配合，展开 accounts 列中的 JSON 数组，然后根据特定条件筛选结果，定位 JSON 结构中的目标元素。

展开嵌套元素是 LATERAL 最常见的用途，因为嵌套元素通常是有限集合，而 LATERAL 很适合处理它们。

用 LATERAL 处理逗号分隔文本

数据集中，有人将一次购买的所有标签保存为逗号分隔列表。如果希望查找具有某个标签的全部购买记录，可以将标签字段拆成数组，再使用 unnest 将数组元素展开为独立行。

以下查询找出带有 electronics 标签的全部购买记录：

 SELECT
  accounts.id AS account_id,
  accounts.name AS account_name,
  purchases.name AS product_name,
  unnested_tags.tag
FROM
  accounts
INNER JOIN purchases ON accounts.id = purchases.account_id
JOIN LATERAL unnest(REGEXP_SPLIT_TO_ARRAY(purchases.tags, E',')) AS unnested_tags(tag) ON true
WHERE
  unnested_tags.tag = 'electronics';

查询依次执行：

用正则表达式拆分字符串。REGEXP_SPLIT_TO_ARRAY 以逗号为分隔符，将 purchases.tags 转为数组。
展开标签。UNNEST 将标签数组转换为多行。
按标签筛选。仅保留指定标签的行。

这种方法让数据库可以处理逗号分隔值，并对分隔字符串中的单个值执行复杂查询。

类似策略也可以统计每个标签对应的购买次数：

 SELECT
  unnested_tags.tag,
  COUNT(*) AS purchases_per_tag
FROM
  purchases,
LATERAL unnest(REGEXP_SPLIT_TO_ARRAY(purchases.tags, E',')) AS unnested_tags(tag)
GROUP BY
  unnested_tags.tag;