[译文] LATERAL 使用的简单示例

几天前，我在 Facebook 上发现了一个用户的问题：如何从一个表中选择一个事件，它们之间的间隔不超过 10 分钟？

这篇文章我试图用一个例子来表示我是如何理解和解决上述问题的。

首先，让我们建立一个事件表，其中每一行都有一个时间戳。

testdb=> CREATE TABLE events(
  pk int generated always as identity
  , event text
  , ts timestamp default CURRENT_TIMESTAMP
  , PRIMARY KEY( pk )
);

CREATE TABLE
复制

现在，让我们用“随机”数据填充表格，以便在2 minutes每个范围内传播事件：

testdb=> insert into events( event, ts )
select 'event #' || v, current_timestamp - ( ( v * 2 ) || ' minutes' )::interval
from generate_series( 1, 100 ) v;

INSERT 0 100
复制

设置好表格和数据后，我们如何将每个元组与不在十分钟窗口之外的其他事件相关联？LATERAL来救援。

testdb=> SELECT e1.pk, e1.event, e2.*  
         FROM events e1, 
         LATERAL ( SELECT pk, event, ts - e1.ts as time_elapsed 
                   FROM events 
                   WHERE pk <> e1.pk AND e1.ts - ts <= '10 minutes'::interval ) e2 
        ORDER BY e1.pk LIMIT 20;
        
 pk  |  event   | pk  |  event   | time_elapsed 
-----+----------+-----+----------+--------------
 501 | event #1 | 502 | event #2 | -00:02:00
 501 | event #1 | 503 | event #3 | -00:04:00
 501 | event #1 | 504 | event #4 | -00:06:00
 501 | event #1 | 505 | event #5 | -00:08:00
 501 | event #1 | 506 | event #6 | -00:10:00
 502 | event #2 | 501 | event #1 | 00:02:00
 502 | event #2 | 503 | event #3 | -00:02:00
 502 | event #2 | 504 | event #4 | -00:04:00
 502 | event #2 | 505 | event #5 | -00:06:00
 502 | event #2 | 506 | event #6 | -00:08:00
 502 | event #2 | 507 | event #7 | -00:10:00
 503 | event #3 | 501 | event #1 | 00:04:00
 503 | event #3 | 502 | event #2 | 00:02:00
 503 | event #3 | 504 | event #4 | -00:02:00
 503 | event #3 | 505 | event #5 | -00:04:00
 503 | event #3 | 506 | event #6 | -00:06:00
 503 | event #3 | 507 | event #7 | -00:08:00
 503 | event #3 | 508 | event #8 | -00:10:00
 504 | event #4 | 501 | event #1 | 00:06:00
 504 | event #4 | 502 | event #2 | 00:04:00
(20 rows)
复制

让我们反汇编查询，看看它是如何工作的。子查询选择在一个10 minutes范围内并且与系统当前正在评估的查询（即e1.pk）不同的所有元组。但通常子查询会为外部查询评估一次，但请注意，子查询以 前缀LATERAL，简单来说，意味着评估外部结果集的每一行的子查询。这意味着LATERAL子查询可以访问外部查询行，并且可以对自己的结果集进行“推理”。
在处理时要记住的一件重要事情LATERAL是子查询必须使用别名引用，在我的例子中e2。请注意，在LATERAL子查询我确实计算了外部元组的时间戳和内部结果集的时间戳之间的时间差，正如您从输出列中看到的，time_elapsed每行相差 2 分钟，这就是我们生成行的方式。

如果不使用会发生什么LATERAL？好吧，您不能引用e1外部元组，即子查询无法交叉引用其范围之外的内容：

testdb=> SELECT e1.pk, e1.event, e2.*  
         FROM events e1,  
         ( SELECT pk, event, ts - e1.ts as time_elapsed 
           FROM events WHERE pk <> e1.pk AND e1.ts - ts <= '10 minutes'::interval ) e2 
         ORDER BY e1.pk LIMIT 20;
ERROR:  invalid reference to FROM-clause entry for table "e1"
LINE 1: ..., e2.*  FROM events e1,  ( SELECT pk, event, ts - e1.ts as t...
                                                             ^
HINT:  There is an entry for table "e1", but it cannot be referenced from this part of the query.
复制

如您所见，PostgreSQL 明确指出您不能e1从子查询的范围内引用（外部元组）。

LATERAL 加入

当然，可以LATERAL在连接中使用，在这种情况下，上面的查询可以重写为：

testdb=> SELECT e1.pk, e1.event, e2.*  
         FROM events e1 JOIN  LATERAL 
            ( SELECT pk, event, ts - e1.ts as time_elapsed 
              FROM events WHERE pk <> e1.pk AND e1.ts - ts <= '10 minutes'::interval ) e2 
        ON true  
        ORDER BY e1.pk LIMIT 20;
 pk  |  event   | pk  |  event   | time_elapsed 
-----+----------+-----+----------+--------------
 501 | event #1 | 502 | event #2 | -00:02:00
 501 | event #1 | 503 | event #3 | -00:04:00
 501 | event #1 | 504 | event #4 | -00:06:00
 501 | event #1 | 505 | event #5 | -00:08:00
 501 | event #1 | 506 | event #6 | -00:10:00
 502 | event #2 | 501 | event #1 | 00:02:00
 502 | event #2 | 503 | event #3 | -00:02:00
 502 | event #2 | 504 | event #4 | -00:04:00
 502 | event #2 | 505 | event #5 | -00:06:00
 502 | event #2 | 506 | event #6 | -00:08:00
 502 | event #2 | 507 | event #7 | -00:10:00
 503 | event #3 | 501 | event #1 | 00:04:00
 503 | event #3 | 502 | event #2 | 00:02:00
 503 | event #3 | 504 | event #4 | -00:02:00
 503 | event #3 | 505 | event #5 | -00:04:00
 503 | event #3 | 506 | event #6 | -00:06:00
 503 | event #3 | 507 | event #7 | -00:08:00
 503 | event #3 | 508 | event #8 | -00:10:00
 504 | event #4 | 501 | event #1 | 00:06:00
 504 | event #4 | 502 | event #2 | 00:04:00
(20 rows)
复制

结论

LATERAL 是 PostgreSQL 中非常强大的 SQL 运算符，可以帮助解决通常通过游标和迭代解决的问题。