PostgreSQL 中的左橫向連線

Shihab Sikder 2023年1月30日 2022年5月14日
  1. PostgreSQL 中的左連線
  2. PostgreSQL 中的左連線
PostgreSQL 中的左橫向連線

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*1575 行。

虛擬碼如下:

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 --

這是我們在查詢中所做的:

  1. 從願望單中抽出一行。
  2. 檢查該行的價格範圍是否小於或等於產品。
  3. 在查詢中使用 LIMIT 5,我們為願望清單中的每一行限制 5 個產品。
  4. 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 中每一行的所有查詢的能力。

要了解有關 LATERALJOIN 的更多資訊,請訪問這裡

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website