PostgreSQL 中的左横向连接
Shihab Sikder
2023年1月30日
PostgreSQL 官方文档指出，``LATERAL关键字可以位于子SELECT FROM项之前。这允许子SELECT引用出现在FROM中的FROM项的列（如果没有LATERAL，每个子 SELECT都是独立评估的，因此不能交叉引用任何其他FROM 项目。）。
PostgreSQL 中的左连接
有效地，它更像是一个 for each 循环，你可以在其中迭代结果，并且对于每一行，你可以对其执行查询（子查询）。为了演示 LATERAL JOIN，让我们创建下表。
create table product (
id INT,
product_name VARCHAR(50),
price_per_unit DECIMAL(5,2)
);
create table wishlist (
wish_list_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
price_range INT
);
以下是 pastebin 中的 insert 查询。我们创建了一个包含 100 个产品的 product 表和一个包含 15 个条目的 wishlist 表。
postgres=# select * from wishlist limit 5;
wish_list_id | first_name | last_name | email | price_range
--------------+------------+-----------+------------------------+-------------
1 | Riannon | Nuzzetti | rnuzzetti0@wp.com | 82
2 | Caresse | Onyon | conyon1@reddit.com | 75
3 | Lexi | Fyndon | lfyndon2@google.com.au | 95
4 | Cybil | Rycraft | crycraft3@oaic.gov.au | 21
5 | Cherry | Greir | cgreir4@boston.com | 46
(5 rows)
postgres=# select * from product order by price_per_unit DESC LIMIT 10;
id | product_name | price_per_unit
----+------------------------------+----------------
67 | Wine - White, Gewurtzraminer | 98.87
3 | Irish Cream - Baileys | 95.24
31 | Tuna - Fresh | 93.49
65 | Bar Energy Chocchip | 90.22
60 | Ecolab - Medallion | 89.54
70 | Yogurt - French Vanilla | 86.18
42 | Shrimp - Baby, Cold Water | 86.15
26 | Tea - Black Currant | 85.92
64 | Scallop - St. Jaques | 85.71
61 | Red Currants | 85.66
(10 rows)
postgres=#
你想知道每个愿望清单上的 TOP 5 产品。有 15 个愿望清单条目，我们有 100 个产品；它应该在查询后返回 5*15 或 75 行。
伪代码如下：
for wish in wishlist
found = 0
for product in productLists(DESC order)
if found<5
return product row
else
break
现在，让我们在 PSQL 查询中编写它。如果我们使用横向，它可以访问每一行，我们可以使用 AS 提取每一行并分配该行。
SELECT * FROM wishlist as wish,
LATERAL (
SELECT * FROM PRODUCT
WHERE PRODUCT.price_per_unit < wish.price_range
ORDER BY PRODUCT.price_per_unit DESC
LIMIT 5
) AS L
ORDER BY wish_list_id, price_per_unit DESC;
输出：
wish_list_id | first_name | last_name | email | price_range | id | product_name | price_per_unit
--------------+------------+------------+---------------------------------+-------------+----+---------------------------------+----------------
1 | Riannon | Nuzzetti | rnuzzetti0@wp.com | 82 | 79 | Cheese - Victor Et Berthold | 81.79
1 | Riannon | Nuzzetti | rnuzzetti0@wp.com | 82 | 76 | Pastry - Key Limepoppy Seed Tea | 81.45
1 | Riannon | Nuzzetti | rnuzzetti0@wp.com | 82 | 32 | Juice - Ocean Spray Kiwi | 81.42
1 | Riannon | Nuzzetti | rnuzzetti0@wp.com | 82 | 41 | Wine - Domaine Boyar Royal | 81.42
1 | Riannon | Nuzzetti | rnuzzetti0@wp.com | 82 | 84 | Propel Sport Drink | 78.59
2 | Caresse | Onyon | conyon1@reddit.com | 75 | 73 | Muffin - Mix - Creme Brule 15l | 74.82
2 | Caresse | Onyon | conyon1@reddit.com | 75 | 48 | Schnappes - Peach, Walkers | 74.49
2 | Caresse | Onyon | conyon1@reddit.com | 75 | 36 | Ice Cream - Strawberry | 73.52
2 | Caresse | Onyon | conyon1@reddit.com | 75 | 45 | Shark - Loin | 73.39
2 | Caresse | Onyon | conyon1@reddit.com | 75 | 47 | Clam - Cherrystone | 73.37
3 | Lexi | Fyndon | lfyndon2@google.com.au | 95 | 31 | Tuna - Fresh | 93.49
3 | Lexi | Fyndon | lfyndon2@google.com.au | 95 | 65 | Bar Energy Chocchip | 90.22
3 | Lexi | Fyndon | lfyndon2@google.com.au | 95 | 60 | Ecolab - Medallion | 89.54
3 | Lexi | Fyndon | lfyndon2@google.com.au | 95 | 70 | Yogurt - French Vanilla | 86.18
3 | Lexi | Fyndon | lfyndon2@google.com.au | 95 | 42 | Shrimp - Baby, Cold Water | 86.15
4 | Cybil | Rycraft | crycraft3@oaic.gov.au | 21 | 80 | Pea - Snow | 20.96
4 | Cybil | Rycraft | crycraft3@oaic.gov.au | 21 | 83 | Creme De Menthe Green | 20.88
4 | Cybil | Rycraft | crycraft3@oaic.gov.au | 21 | 15 | Bread - Sticks, Thin, Plain | 20.57
4 | Cybil | Rycraft | crycraft3@oaic.gov.au | 21 | 39 | Gatorade - Cool Blue Raspberry | 19.36
-- More --
这是我们在查询中所做的：
- 从愿望单中抽出一行。
- 检查该行的价格范围是否小于或等于产品。
- 在查询中使用
LIMIT 5，我们为愿望清单中的每一行限制 5 个产品。 ORDER BY PRODUCT.price_per_unit DESC根据价格占据前几行。
输出正是我们想要的。它更像是一个带有参数的 join 查询。
PostgreSQL 中的左连接
为了证明这一点，我们为购买历史制作了另一个表格。该表包含上面愿望清单中每个用户的 1000 个事务。
create table Purchase (
transaction_id INT NOT NULL,
user_id INT,
product_id INT,
date DATE
);
从此处插入数据后，表格将如下所示。
postgres=# select * from purchase;
transaction_id | user_id | product_id | date
----------------+---------+------------+------------
1 | 1 | 43 | 2013-10-21
2 | 7 | 24 | 2017-10-04
3 | 12 | 60 | 2011-12-29
4 | 11 | 17 | 2015-01-07
5 | 15 | 21 | 2019-09-14
6 | 2 | 41 | 2013-07-23
7 | 15 | 41 | 2013-08-22
8 | 3 | 27 | 2013-09-18
9 | 15 | 24 | 2010-01-11
10 | 12 | 4 | 2011-01-20
11 | 2 | 34 | 2020-12-05
-- More --
现在，你想知道每个用户的第一个订单、第二个订单日期和第二个购买项目名称。因此，你需要使用 join 命令进行子查询。
SQL 如下所示：
SELECT first_name,First_Order,Next_Order,product_name as next_product_name FROM
(SELECT Purchase.user_id, min(date) AS First_Order FROM Purchase GROUP BY user_id) Q1
LEFT JOIN LATERAL
(SELECT user_id,wishlist.first_name,product_name, date AS Next_Order FROM Purchase,wishlist,product
WHERE user_id = Q1.user_id
and date>Q1.First_Order
and user_id=wishlist.wish_list_id
and product_id = product.id
ORDER BY date ASC
LIMIT 1
) Q2 ON true;
这是以下查询的输出。
first_name | first_order | next_order | next_product_name
------------+-------------+------------+---------------------------------
Cybil | 2010-02-23 | 2010-03-09 | Gatorade - Cool Blue Raspberry
Simonne | 2010-04-27 | 2010-06-23 | Gatorade - Cool Blue Raspberry
Lexi | 2010-07-12 | 2010-08-12 | Artichoke - Fresh
Evaleen | 2010-04-27 | 2010-05-22 | Bread - Sticks, Thin, Plain
Noell | 2010-04-03 | 2010-05-01 | Jameson Irish Whiskey
Joyce | 2010-02-26 | 2010-03-15 | Pastry - Baked Scones - Mini
Trixi | 2010-01-09 | 2010-01-13 | Cheese - Brie, Cups 125g
Riannon | 2010-04-30 | 2010-07-07 | Wine - Cotes Du Rhone Parallele
Cherry | 2010-04-20 | 2011-01-20 | Cheese - Brie, Cups 125g
Caresse | 2010-10-05 | 2011-02-03 | Cheese - Brie, Cups 125g
Andonis | 2010-01-11 | 2011-04-28 | Yogurt - French Vanilla
Stephannie | 2010-05-31 | 2010-07-07 | Shrimp - 16/20, Iqf, Shell On
Linn | 2010-02-09 | 2010-03-25 | Food Colouring - Pink
Matilda | 2010-01-01 | 2010-03-14 | Propel Sport Drink
Jesse | 2010-05-16 | 2010-07-05 | Fennel
(15 rows)
postgres=#
使用 LATERAL，我们可以访问查询的行。LEFT JOIN LATERAL 提供了迭代 Q1 中每一行的所有查询的能力。
要了解有关 LATERAL 和 JOIN 的更多信息，请访问这里。
作者： Shihab Sikder
