PostgreSQL中的LATERAL简介
PostgreSQL中的LATERAL简介
Bigbig2022-10-18
563
横向查询已经存在很长一段时间了——特别是从Pg 9.3开始——大约 10 年。
那么，横向查询是什么？
从广义上讲——横向子查询（有时也称为laterl join）是开发人员使PostgreSQL基于单行数据生成多行的一种方式。
最简单的例子：假设表包含一些事件作为两列（我知道我可以使用范围数据类型，但我想保持简单）：event_start 和 event_end。像这样：
= $ CREATE TABLE events (
id int8 GENERATED ALWAYS AS IDENTITY PRIMARY KEY ,
event_start date NOT NULL ,
event_end date NOT NULL ,
CHECK ( event_end >= event_start )
) ;
现在，让我们添加一些事件：
= $ with event_starts as (
select now ( ) - '2 周' :: interval * random ( ) as start
from generate_series ( 1 , 5 ) i
)
插入 事件( event_start , event_end ) select start , start + '3 days ' ::区间+随机( ) *
'4 天' ::
event_starts的间隔
；
这给了我一些可以处理的好事件：
= $从事件中选择 * ； 编号| 事件开始| event_end ----+-------------+------------ 1 | 2022 - 09 - 15 | 2022 - 09 - 22 2 | 2022 - 09 - 06 | 2022 - 09 - 11 3 | 2022 - 09 - 06 | 2022 - 09 - 10 4 | 2022 - 09 - 12 | 2022- 09 - 18 5 | 2022 - 09 - 05 | 2022年9月10 日 （5 行）
现在，假设我想获取所有日期的列表，其中包含一些事件，并计算这些日子每天有多少事件。
我可以从简单的 select * from events 开始，然后使用横向获取所有日期的列表。让我们来看看：
= $ select
e .*,
l .*
from
events e ，
lateral (
select x:: date
from generate_series ( e . event_start , e . event_end , '1 day' :: interval ) as x
) as l
编号| 事件开始| 事件结束 | x
----+-------------+------------+------------
1 | 2022 - 09 - 15 | 2022 - 09 - 22 | 2022 - 09 - 15
1 | 2022 - 09 - 15 | 2022 - 09 - 22 | 2022 - 09 - 16
1 | 2022 - 09 - 15 | 2022 - 09 - 22 | 2022 - 09 - 17
1 | 2022 - 09 - 15 | 2022 - 09 - 22 | 2022 - 09 - 18
1 | 2022 - 09 - 15 | 2022 - 09 - 22 | 2022 - 09 - 19
1 | 2022 - 09 - 15 | 2022 - 09 - 22 | 2022 - 09 - 20
1 | 2022 - 09 - 15 | 2022 - 09 - 22 | 2022 - 09 - 21
1 | 2022 - 09 - 15 | 2022 - 09 - 22 | 2022 - 09 - 22
2 | 2022 - 09 - 06 | 2022 - 09 - 11 | 2022 - 09 - 06
2 | 2022 - 09 - 06 | 2022 - 09 - 11 | 2022 - 09 - 07
2 | 2022 - 09 - 06 | 2022 - 09 - 11 | 2022 - 09 - 08
2 | 2022 - 09 - 06 | 2022 - 09 - 11 | 2022 - 09 - 09
2 | 2022 - 09 - 06 | 2022 - 09 - 11 | 2022 - 09 - 10
2 | 2022 - 09 - 06 | 2022 - 09 - 11 | 2022 - 09 - 11
3 | 2022 - 09 - 06 | 2022 - 09 - 10 | 2022 - 09 - 06
3 | 2022 - 09 - 06 | 2022 - 09 - 10 | 2022 - 09 - 07
3 | 2022 - 09 - 06 | 2022 - 09 - 10 | 2022 - 09 - 08
3 | 2022 - 09 - 06 | 2022 - 09 - 10 | 2022 - 09 - 09
3 | 2022 - 09 - 06 | 2022 - 09 - 10 | 2022 - 09 - 10
4 | 2022 - 09 - 12 | 2022 - 09 - 18 | 2022 - 09 - 12
4 | 2022 - 09 - 12 | 2022 - 09 - 18 | 2022 - 09 - 13
4 | 2022 - 09 - 12 | 2022 - 09 - 18 | 2022 - 09 - 14
4 | 2022 - 09 - 12 | 2022 - 09 - 18 | 2022 - 09 - 15
4 | 2022 - 09 - 12 | 2022- 09 - 18 | 2022 - 09 - 16
4 | 2022 - 09 - 12 | 2022 - 09 - 18 | 2022 - 09 - 17
4 | 2022 - 09 - 12 | 2022 - 09 - 18 | 2022 - 09 - 18
5 | 2022 - 09 - 05 | 2022 -09 - 10 | 2022 - 09 - 05
5 | 2022 - 09 - 05 | 2022 - 09 - 10 | 2022 - 09 - 06
5 | 2022 - 09 - 05 | 2022 - 09 - 10 | 2022 - 09 - 07
5 | 2022 - 09 - 05 | 2022 -09 - 10 | 2022 - 09 - 08
5 | 2022 - 09 - 05 | 2022 - 09 - 10 | 2022 - 09 - 09
5 | 2022 - 09 - 05 | 2022 - 09 - 10 | 2022-09-10 （32行）_ _ _ _
请注意，前 3 列中的数据是重复的——因为它是 events 表中的同一行，由于横向魔术，只添加了第 4 列。
横向，在这个例子中，调用 generate_series() 函数，它生成一组时间戳，然后将其转换为日期数据类型，这样我们就只能得到日期。
现在，要获取所有天数及其计数，我只需要按 lx 分组，然后获取计数：
= $选择
l 。x ，
从
事件 e中计数( * ) ，
横向
(从generate_series ( e.event_start , e.event_end , ' 1 day' :: interval )中选择x::日期作为x
)作为l按l
分组。x
按l排序。X
x | 计数
------------+--------
2022 - 09 - 05 | 1
2022 - 09 - 06 | 3
2022 - 09 - 07 | 3
2022 - 09 - 08 | 3
2022 - 09 - 09 | 3
2022 - 09 - 10 | 3
2022 - 09 - 11 | 1
2022 -09 - 12 | 1
2022 - 09 - 13 | 1
2022 - 09 - 14 | 1
2022 - 09 - 15 | 2
2022 - 09 - 16 | 2
2022 - 09 - 17 | 2
2022 - 09 - 18 | 2
2022 - 09 - 19 | 1
2022 - 09 -20 | 1
2022 - 09 - 21 | 1
2022 - 09 - 22 | 1个
（18 行）
重要的部分是，对于源（事件）中的每一行，横向内部的查询被调用并具有对正常逻辑（包括 where 子句、函数、分组、聚合、排序、限制等任何内容）的完全访问权限，并且结果记录集可用于我们的查询。
例如，这可以用于获取诸如按薪水排序的每个部门的前五名员工：
select d . * ,
le . *
from department d ,
lateral (
select *
from employees
e where e.dept_id = d.id order by e.salary desc limit 5 ) as le
通常，当您需要从复杂数据（json？）中提取信息或对连接数据集进行非显而易见的修改（如上例中的限制行数）时，横向是天赐之物。
我还想展示一件事。具体来说——虽然我不特别喜欢将横向查询称为“横向连接”，但事实上您可以使用连接语法。这在横向查询不返回任何内容的情况下很有用。
给定事件表，让我们尝试获取 9 月每天的事件数。
要获得 9 月的所有日期，我可以简单地：
= $ select d:: date as day
from generate_series ( '2022-09-01' , '2022-09-30' , '1 day' :: interval ) d;
天
------------
2022 - 09 - 01
2022 - 09 - 02
2022 - 09 - 03
2022 - 09 - 04
...
2022 - 09 - 30
（30 行）
现在，我可以添加横向子查询来获取当天发生的事件：
= $ select
d:: date as day ,
l .*
from
generate_series ( '2022-09-01' , '2022-09-30' , '1 day' :: interval ) d ,
lateral (
select * from events e
where d ::
e.event_start和e.event_end之间的日期 ）
为l ； _ 天| 编号| 事件开始| event_end
------------+----+-------------+------------
2022 - 09 - 05 | 5 | 2022 - 09 - 05 | 2022 - 09 - 10
2022 - 09 - 06 | 2 | 2022 - 09 - 06 | 2022 - 09 - 11
2022 - 09 - 06 | 3 | 2022 - 09- 06 | 2022 - 09 - 10
2022 - 09 - 06 | 5 | 2022 - 09 - 05 | 2022 - 09 - 10
2022 - 09 - 07 | 2 | 2022 - 09 - 06 | 2022 - 09 - 11
2022 - 09 - 07 | 3 | 2022 - 09- 06 | 2022 - 09 - 10
2022 - 09 - 07 | 5 | 2022 - 09 - 05 | 2022 - 09 - 10
2022 - 09 - 08 | 2 | 2022 - 09 - 06 | 2022 - 09 - 11
2022 - 09 - 08 | 3 | 2022 - 09- 06 | 2022 - 09 - 10
2022 - 09 - 08 | 5 | 2022 - 09 - 05 | 2022 - 09 - 10
2022 - 09 - 09 | 2 | 2022 - 09 - 06 | 2022 - 09 - 11
2022 - 09 - 09 | 3 | 2022 - 09- 06 | 2022 - 09 - 10
2022 - 09 - 09 | 5 | 2022 - 09 - 05 | 2022 - 09 - 10
2022 - 09 - 10 | 2 | 2022 - 09 - 06 | 2022 - 09 - 11
2022 - 09 - 10 | 3 | 2022 - 09- 06 | 2022 - 09 - 10
2022 - 09 - 10 | 5 | 2022 - 09 - 05 | 2022 - 09 - 10
2022 - 09 - 11 | 2 | 2022 - 09 - 06 | 2022 - 09 - 11
2022 - 09 - 12 | 4 | 2022 - 09- 12 | 2022 - 09 - 18
2022 - 09 - 13 | 4 | 2022 - 09 - 12 | 2022 - 09 - 18
2022 - 09 - 14 | 4 | 2022 - 09 - 12 | 2022 - 09 - 18
2022 - 09 - 15 | 1 | 2022 - 09 -15 | 2022 - 09 - 22
2022 - 09 - 15 | 4 | 2022 - 09 - 12 | 2022 - 09 - 18
2022 - 09 - 16 | 1 | 2022 - 09 - 15 | 2022 - 09 - 22
2022 - 09 - 16 | 4 | 2022 - 09 - 12 | 2022 - 09 - 18
2022 - 09 - 17 | 1 | 2022 - 09 - 15 | 2022 - 09 - 22
2022 - 09 - 17 | 4 | 2022 - 09 - 12 | 2022 - 09 - 18
2022 - 09 - 18 | 1 | 2022 - 09 - 15 | 2022 - 09 - 22
2022 - 09 - 18 | 4 | 2022 - 09 - 12 | 2022 - 09 - 18
2022 - 09 - 19 | 1 | 2022 - 09 - 15 | 2022 - 09 - 22
2022 - 09 - 20 | 1 | 2022 - 09 - 15 | 2022- 09 - 22
2022 - 09 - 21 | 1 | 2022 - 09 - 15 | 2022 - 09 - 22
2022 - 09 - 22 | 1 | 2022 - 09 - 15 | 2022-09-22 （32行）_ _ _ _
这显然有效，但如果我现在得到计数，我会错过一些日子：
= $ select
d:: date as day ,
count ( l . id ) as events
from
generate_series ( '2022-09-01' , '2022-09-30' , '1 day' :: interval ) d ,
lateral (
select * 来自事件 e
其中d:: e . event_start和e . event_end
之间的日期 ）为 l
按d分组 。d的日期
顺序。约会
日| 事件
------------+-------- 2022 - 09 - 05 | 1 2022 - 09 - 06 | 3 2022 - 09 - 07 | 3 2022 - 09 - 08 | 3 2022 - 09 - 09 | 3 2022 - 09 -
10 | 3
2022 - 09 - 11 | 1
2022 - 09 - 12 | 1
2022 - 09 - 13 | 1
2022 - 09 - 14 | 1
2022 - 09 - 15 | 2
2022 - 09 - 16 | 2
2022 - 09 - 17 | 2
2022 - 09 - 18 | 2
2022 - 09 - 19 | 1
2022 - 09 - 20 | 1
2022 - 09 - 21 | 1
2022 - 09 - 22 | 1个
（18 行）
具体来说——我没有数据为 0 行的日子。这是因为这些天的横向返回了 0 行，而这个从 generate_series 中“取消”的行。
由于对横向进行了左连接，我们可以解决它：
= $ select
d:: date as day ,
count ( l . id ) as events
from
generate_series ( '2022-09-01' , ' 2022-09-30' , '1 day' :: interval ) d
left joinlateral (
select * from events e
where d:: date between e . event_start和e . event_end
) 作为l on ( true )
group by d 。d的日期
顺序 。约会
日| 事件
------------+-------- 2022 - 09 - 01 | 0 2022 - 09 - 02 | 0 2022 - 09 - 03 | 0 2022 - 09 - 04 | 0 2022 - 09 - 05 |
1
2022 - 09 - 06 | 3
2022 - 09 - 07 | 3
2022 - 09 - 08 | 3
2022 - 09 - 09 | 3
2022 - 09 - 10 | 3
2022 - 09 - 11 | 1
2022 - 09 - 12 | 1
2022 - 09 - 13 | 1
2022- 09 - 14 | 1
2022 - 09 - 15 | 2
2022 - 09 - 16 | 2
2022 - 09 - 17 | 2
2022 - 09 - 18 | 2
2022 - 09 - 19 | 1
2022 - 09 - 20 | 1
2022 - 09 - 21 | 1
2022 - 09- 22 | 1
2022 - 09 - 23 | 0
2022 - 09 - 24 | 0
2022 - 09 - 25 | 0
2022 - 09 - 26 | 0
2022 - 09 - 27 | 0
2022 - 09 - 28 | 0
2022 - 09 - 29 | 0
2022 - 09 - 30 | 0
（30 行）
这带来了 0 个计数，这是因为横向返回的所有列在没有事件的日子里都是 NULL。这意味着 count(l.id) 没有增加计数（count(...) 仅对非空值增加计数）。
对于左连接，我使用了奇怪的连接条件：on (true)。这是因为 JOIN 需要连接条件。在我们的例子中，真正的条件是在横向查询中构建的（其中 d::date ...），但语法仍然需要加入一些东西。由于横向返回的任何行都将是“可以加入”，所以我们加入的条件始终为真。
原文标题：What is LATERAL, what is it for, and how can one use it?
原文作者：Jérémie
原文链接：https://www.depesz.com/2022/09/18/what-is-lateral-what-is-it-for-and-how-can-one-use-it/
漫思
浙公网安备 33010602011771号