Postgresql LATERAL例子

lateral 子查询可以支持横向连接外面的表，在FROM 或者JOIN子句的子查询里面可以关联查询LATERAL前面的FROM子句或者JOIN子句，通过例子看如何使用：

#创建测试表并初始化数据
CREATE TABLE t_product AS
    SELECT   id AS product_id,
             id * 10 * random() AS price,
             'product ' || id AS product
    FROM generate_series(1, 1000) AS id;
 
CREATE TABLE t_wishlist
(
    wishlist_id        int,
    username           text,
    desired_price      numeric
);
 
INSERT INTO t_wishlist VALUES
    (1, 'hans', '450'),
    (2, 'joe', '60'),
    (3, 'jane', '1500')
;

#查看一下每个表的数据
<10:47:44><db:postgres><user:postgres><pid:1573>=#  SELECT * FROM t_product LIMIT 10;
 product_id │       price        │  product   
────────────┼────────────────────┼────────────
          1 │  4.849889621310801 │ product 1
          2 │  18.81473301122142 │ product 2
          3 │  2.934305279297824 │ product 3
          4 │  14.18731331577817 │ product 4
          5 │ 22.151528571809642 │ product 5
          6 │ 3.4324350838734574 │ product 6
          7 │ 24.743909068355343 │ product 7
          8 │  79.50281297415131 │ product 8
          9 │  38.75210636088138 │ product 9
         10 │ 12.209404338634755 │ product 10
(10 rows)

<11:02:45><db:postgres><user:postgres><pid:1573>=# SELECT * FROM t_wishlist;
 wishlist_id │ username │ desired_price 
─────────────┼──────────┼───────────────
           1 │ hans     │           450
           2 │ joe      │            60
           3 │ jane     │          1500
(3 rows)

#查询每个用户价格price小于desired_price的前三个商品
SELECT        *
FROM      t_wishlist AS w,
    LATERAL  (SELECT      *
        FROM       t_product AS p
        WHERE       p.price < w.desired_price
        ORDER BY p.price DESC
        LIMIT 3
       ) AS x
ORDER BY wishlist_id, price DESC;
 wishlist_id │ username │ desired_price │ product_id │       price        │   product   
─────────────┼──────────┼───────────────┼────────────┼────────────────────┼─────────────
           1 │ hans     │           450 │        226 │  447.5753406367529 │ product 226
           1 │ hans     │           450 │        679 │  444.4336426288043 │ product 679
           1 │ hans     │           450 │        442 │ 443.12261756231703 │ product 442
           2 │ joe      │            60 │         32 │  57.47824763256176 │ product 32
           2 │ joe      │            60 │         22 │  49.20090436720116 │ product 22
           2 │ joe      │            60 │         44 │  48.21971975833719 │ product 44
           3 │ jane     │          1500 │        458 │ 1489.3024550007774 │ product 458
           3 │ jane     │          1500 │        968 │ 1488.6776403534532 │ product 968
           3 │ jane     │          1500 │        157 │ 1484.9044355451265 │ product 157
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

执行计划如下：

<10:33:20><db:postgres><user:postgres><pid:1573>=# explain SELECT        *                                                                               FROM      t_wishlist AS w,
    LATERAL  (SELECT      *
        FROM       t_product AS p
        WHERE       p.price < w.desired_price
        ORDER BY p.price DESC
        LIMIT 3
       ) AS x
ORDER BY wishlist_id, price DESC;
                                      QUERY PLAN                                       
───────────────────────────────────────────────────────────────────────────────────────
 Sort  (cost=23428.53..23434.90 rows=2550 width=91)
   Sort Key: w.wishlist_id, p.price DESC
   ->  Nested Loop  (cost=27.30..23284.24 rows=2550 width=91)
         ->  Seq Scan on t_wishlist w  (cost=0.00..18.50 rows=850 width=68)
         ->  Limit  (cost=27.30..27.31 rows=3 width=23)
               ->  Sort  (cost=27.30..28.14 rows=333 width=23)
                     Sort Key: p.price DESC
                     ->  Seq Scan on t_product p  (cost=0.00..23.00 rows=333 width=23)
                           Filter: (price < (w.desired_price)::double precision)
(9 rows)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

用窗口函数也可以实现：

WITH pp AS (
    SELECT
        *,
        row_number() OVER (PARTITION BY tt.wishlist_id ORDER BY tt.price DESC) AS rn
    FROM (
        SELECT
            *
        FROM
            t_wishlist AS w,
            t_product AS p
        WHERE
            p.price < w.desired_price
        ORDER BY
            wishlist_id,
            p.price DESC) tt
)
SELECT
    wishlist_id,
    username,
    desired_price,
    product_id,
    price,
    product
FROM
    pp
WHERE
    pp.rn <= 3;

 wishlist_id │ username │ desired_price │ product_id │       price        │   product   
─────────────┼──────────┼───────────────┼────────────┼────────────────────┼─────────────
           1 │ hans     │           450 │        226 │  447.5753406367529 │ product 226
           1 │ hans     │           450 │        679 │  444.4336426288043 │ product 679
           1 │ hans     │           450 │        442 │ 443.12261756231703 │ product 442
           2 │ joe      │            60 │         32 │  57.47824763256176 │ product 32
           2 │ joe      │            60 │         22 │  49.20090436720116 │ product 22
           2 │ joe      │            60 │         44 │  48.21971975833719 │ product 44
           3 │ jane     │          1500 │        458 │ 1489.3024550007774 │ product 458
           3 │ jane     │          1500 │        968 │ 1488.6776403534532 │ product 968
           3 │ jane     │          1500 │        157 │ 1484.9044355451265 │ product 157
(9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

参考：
https://www.postgresql.org/docs/13/queries-table-expressions.html
https://www.cybertec-postgresql.com/en/understanding-lateral-joins-in-postgresql/
https://github.com/digoal/blog/blob/master/201210/20121008_01.md