PostgreSQL 教程: LATERAL JOIN 横向连接

摘要：在本文中，我们将了解 SQL 中 LATERAL JOIN 的工作原理，以及如何使用它来交叉引用子查询中的行，和构建复合结果集。

数据表

假设我们有以下blog数据表，来存储由我们的平台托管的博客：

而且，我们目前有两个博客：

| id | created_on | title               | url                                |
|----|------------|---------------------|------------------------------------|
| 1  | 2013-09-30 | PostgreSQL Blog     | https://www.rockdata.net/blog/     |
| 2  | 2017-01-22 | PostgreSQL Tutorial | https://www.rockdata.net/tutorial/ |

在不使用 LATERAL JOIN 的情况下获取报表

我们需要生成一个报表，用于从blog表中提取以下数据：

• 博客 ID
• 博客年龄，以年为单位
• 下一次博客周年日的日期
• 距离下一次周年日还剩多少天

使用日期间隔函数计算博客年龄

博客年龄需要通过将当前日期减去博客创建日期来计算。

下一次博客周年日的日期，可以通过将年龄以年为单位递增，并将其添加到博客创建日期来计算。

可以通过从下一次博客周年日和当前日期之间的间隔中提取天数，来计算到下一次周年日的天数。

若要获取结果，可以使用以下查询：

SELECT
  b.id as blog_id,
  extract(
    YEAR FROM age(now(), b.created_on)
  ) AS age_in_years,
  date(
    created_on + (
      extract(YEAR FROM age(now(), b.created_on)) + 1
    ) * interval '1 year'
  ) AS next_anniversary,
  date(
    created_on + (
      extract(YEAR FROM age(now(), b.created_on)) + 1
    ) * interval '1 year'
  ) - date(now()) AS days_to_next_anniversary
FROM blog b
ORDER BY blog_id

这样，您将获得预期的结果：

| blog_id | age_in_years | next_anniversary | days_to_next_anniversary |
|---------|--------------|------------------|--------------------------|
| 1       | 7            | 2021-09-30       | 295                      |
| 2       | 3            | 2021-01-22       | 44                       |

如您所见，age_in_years必须定义三次，因为在计算next_anniversary和days_to_next_anniversary值时需要它。

而这正是 LATERAL JOIN 可以帮助我们的地方。

使用 LATERAL JOIN 获取报表

LATERAL JOIN 允许我们重用age_in_years值，并在计算next_anniversary和days_to_next_anniversary值时进一步传递它。

例如，前面的 SQL 查询可以像这样重写：

SELECT
  b.id as blog_id,
  age_in_years,
  date(
    created_on + (age_in_years + 1) * interval '1 year'
  ) AS next_anniversary,
  date(
    created_on + (age_in_years + 1) * interval '1 year'
  ) - date(now()) AS days_to_next_anniversary
FROM blog b
CROSS JOIN LATERAL (
  SELECT
    cast(
      extract(YEAR FROM age(now(), b.created_on)) AS int
    ) AS age_in_years
) AS t
ORDER BY blog_id

并且，age_in_years值可以只计算一次，并重用于next_anniversary和days_to_next_anniversary的计算：

| blog_id | age_in_years | next_anniversary | days_to_next_anniversary |
|---------|--------------|------------------|--------------------------|
| 1       | 7            | 2021-09-30       | 295                      |
| 2       | 3            | 2021-01-22       | 44                       |

好多了，对吧？

为blog表的每条记录计算age_in_years。这样，它的工作方式类似于相关的子查询，但子查询记录与主表连接，因此，我们可以引用子查询生成的列。