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
