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