PostgreSQL 中的左横向连接

当前位置：主页 > 学无止境 > 数据库 > PostgreSQL >

PostgreSQL 中的左横向连接

作者：迹忆客最近更新：2023/03/20 浏览次数：

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