PostgreSQL 中的左横向连接

数据库 6月前 101

PostgreSQL 官方文档指出，“LATERAL关键字可以位于子SELECT FROM项之前。这允许子SELECT引用出现在FROM中的FROM项的列（如果没有LATERAL，每个子 SELECT都是独立评估的，因此不能交叉引用任何其他FROM 项目。）。

PostgreSQL 中的左连接

有效地，它更像是一个 for each 循环，你可以在其中迭代结果，并且对于每一行，你可以对其执行查询（子查询）。为了演示 LATERAL JOIN，让我们创建下表。

createtableproduct(idINT,product_nameVARCHAR(50),price_per_unitDECIMAL(5,2));createtablewishlist(wish_list_idINTPRIMARYKEY,first_nameVARCHAR(50),last_nameVARCHAR(50),emailVARCHAR(50),price_rangeINT);

以下是 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*FROMwishlistaswish,LATERAL(SELECT*FROMPRODUCTWHEREPRODUCT.price_per_unit<wish.price_rangeORDERBYPRODUCT.price_per_unitDESCLIMIT5)ASLORDERBYwish_list_id,price_per_unitDESC;

输出：

 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 个事务。

createtablePurchase(transaction_idINTNOTNULL,user_idINT,product_idINT,dateDATE);

从此处插入数据后，表格将如下所示。

postgres=#select*frompurchase;transaction_id|user_id|product_id|date----------------+---------+------------+------------
1|1|43|2013-10-212|7|24|2017-10-043|12|60|2011-12-294|11|17|2015-01-075|15|21|2019-09-146|2|41|2013-07-237|15|41|2013-08-228|3|27|2013-09-189|15|24|2010-01-1110|12|4|2011-01-2011|2|34|2020-12-05-- More --

现在，你想知道每个用户的第一个订单、第二个订单日期和第二个购买项目名称。因此，你需要使用 join 命令进行子查询。

SQL 如下所示：

SELECTfirst_name,First_Order,Next_Order,product_nameasnext_product_nameFROM(SELECTPurchase.user_id,min(date)ASFirst_OrderFROMPurchaseGROUPBYuser_id)Q1LEFTJOINLATERAL(SELECTuser_id,wishlist.first_name,product_name,dateASNext_OrderFROMPurchase,wishlist,productWHEREuser_id=Q1.user_idanddate>Q1.First_Orderanduser_id=wishlist.wish_list_idandproduct_id=product.idORDERBYdateASCLIMIT1)Q2ONtrue;

这是以下查询的输出。

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