postgresql:与数组的自联接

loading...


0

我的问题是关于为以下用例形成Postgres SQL查询

Approach#1

我有一个像下面的表,我在不同的类型(a,b,c,d)生成相同的uuid,如映射不同的类型 .

+----+------+-------------+
| id | type | master_guid |
+----+------+-------------+
|  1 | a    | uuid-1      |
|  2 | a    | uuid-2      |
|  3 | a    | uuid-3      |
|  4 | a    | uuid-4      |
|  5 | a    | uuid-5      |
|  6 | b    | uuid-1      |
|  7 | b    | uuid-2      |
|  8 | b    | uuid-3      |
|  9 | b    | uuid-6      |
| 10 | c    | uuid-1      |
| 11 | c    | uuid-2      |
| 12 | c    | uuid-3      |
| 13 | c    | uuid-6      |
| 14 | c    | uuid-7      |
| 15 | d    | uuid-6      |
| 16 | d    | uuid-2      |
+----+------+-------------+

Approach#2

我有一个创建的两个表用于id键入,然后id到master_guid,如下所示

table1:

+----+------+
| id | type |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  6 | b    |
|  7 | b    |
|  8 | b    |
|  9 | b    |
| 10 | c    |
| 11 | c    |
| 12 | c    |
| 13 | c    |
| 14 | c    |
| 15 | d    |
| 16 | d    |
+----+------+

table2

+----+-------------+
| id | master_guid |
+----+-------------+
|  1 | uuid-1      |
|  2 | uuid-2      |
|  3 | uuid-3      |
|  4 | uuid-4      |
|  5 | uuid-5      |
|  6 | uuid-1      |
|  7 | uuid-2      |
|  8 | uuid-3      |
|  9 | uuid-6      |
| 10 | uuid-1      |
| 11 | uuid-2      |
| 12 | uuid-3      |
| 13 | uuid-6      |
| 14 | uuid-7      |
| 15 | uuid-6      |
| 16 | uuid-2      |
+----+-------------+

我希望通过以下两种方法获得如下输出:

+----+------+--------+------------+
| id | type |  uuid  | mapped_ids |
+----+------+--------+------------+
|  1 | a    | uuid-1 | [6,10]     |
|  2 | a    | uuid-2 | [7,11]     |
|  3 | a    | uuid-3 | [8,12]     |
|  4 | a    | uuid-4 | null       |
|  5 | a    | uuid-5 | null       |
+----+------+--------+------------+

我已尝试使用array上的array_agg进行自联接,并基于uuid进行分组,但无法获得所需的输出 .

使用以下查询来填充数据:

Approach#1

insert into table1 values 
(1,'a','uuid-1'),
(2,'a','uuid-2'),
(3,'a','uuid-3'),
(4,'a','uuid-4'),
(5,'a','uuid-5'),
(6,'b','uuid-1'),
(7,'b','uuid-2'),
(8,'b','uuid-3'),
(9,'b','uuid-6'),
(10,'c','uuid-1'),
(11,'c','uuid-2'),
(12,'c','uuid-3'),
(13,'c','uuid-6'),
(14,'c','uuid-7'),
(15,'d','uuid-6'),
(16,'d','uuid-2')

Approach#2

insert into table1 values 
(1,'a'),
(2,'a'),
(3,'a'),
(4,'a'),
(5,'a'),
(6,'b'),
(7,'b'),
(8,'b'),
(9,'b'),
(10,'c'),
(11,'c'),
(12,'c'),
(13,'c'),
(14,'c'),
(15,'d'),
(16,'d')

insert into table2 values 
(1,'uuid-1'),
(2,'uuid-2'),
(3,'uuid-3'),
(4,'uuid-4'),
(5,'uuid-5'),
(6,'uuid-1'),
(7,'uuid-2'),
(8,'uuid-3'),
(9,'uuid-6'),
(10,'uuid-1'),
(11,'uuid-2'),
(12,'uuid-3'),
(13,'uuid-6'),
(14,'uuid-7'),
(15,'uuid-6'),
(16,'uuid-2')

loading...

2回答

  • 0

    demo: db<>fiddle

    使用window function ARRAY_AGG允许您聚合 id 每个组(在您的情况下,组是您的 uuid

    SELECT 
        id, type, master_guid as uuid, 
        array_agg(id) OVER (PARTITION BY master_guid) as mapped_ids
    FROM table1
    ORDER BY id
    

    结果:

    | id | type |   uuid | mapped_ids |
    |----|------|--------|------------|
    |  1 |    a | uuid-1 |     10,6,1 |
    |  2 |    a | uuid-2 |  16,2,7,11 |
    |  3 |    a | uuid-3 |     8,3,12 |
    |  4 |    a | uuid-4 |          4 |
    |  5 |    a | uuid-5 |          5 |
    |  6 |    b | uuid-1 |     10,6,1 |
    |  7 |    b | uuid-2 |  16,2,7,11 |
    |  8 |    b | uuid-3 |     8,3,12 |
    |  9 |    b | uuid-6 |    15,13,9 |
    | 10 |    c | uuid-1 |     10,6,1 |
    | 11 |    c | uuid-2 |  16,2,7,11 |
    | 12 |    c | uuid-3 |     8,3,12 |
    | 13 |    c | uuid-6 |    15,13,9 |
    | 14 |    c | uuid-7 |         14 |
    | 15 |    d | uuid-6 |    15,13,9 |
    | 16 |    d | uuid-2 |  16,2,7,11 |
    

    这些数组当前还包含当前行的id( mapped_ids id = 1 包含 1 ) . 这可以通过使用 array_remove 删除此元素来更正:

    SELECT 
        id, type, master_guid as uuid,  
        array_remove(array_agg(id) OVER (PARTITION BY master_guid), id) as mapped_ids
    FROM table1
    ORDER BY id
    

    结果:

    | id | type |   uuid | mapped_ids |
    |----|------|--------|------------|
    |  1 |    a | uuid-1 |       10,6 |
    |  2 |    a | uuid-2 |    16,7,11 |
    |  3 |    a | uuid-3 |       8,12 |
    |  4 |    a | uuid-4 |            |
    |  5 |    a | uuid-5 |            |
    |  6 |    b | uuid-1 |       10,1 |
    |  7 |    b | uuid-2 |    16,2,11 |
    |  8 |    b | uuid-3 |       3,12 |
    |  9 |    b | uuid-6 |      15,13 |
    | 10 |    c | uuid-1 |        6,1 |
    | 11 |    c | uuid-2 |     16,2,7 |
    | 12 |    c | uuid-3 |        8,3 |
    | 13 |    c | uuid-6 |       15,9 |
    | 14 |    c | uuid-7 |            |
    | 15 |    d | uuid-6 |       13,9 |
    | 16 |    d | uuid-2 |     2,7,11 |
    

    现在例如 id=4 包含一个空数组而不是 NULL 值 . 这可以通过使用 NULLIF 函数来实现 . 如果两个参数相等,则给出 NULL ,否则它给出第一个参数 .

    SELECT 
        id, type, master_guid as uuid,  
        NULLIF(
            array_remove(array_agg(id) OVER (PARTITION BY master_guid), id), 
            '{}'::int[]
        ) as mapped_ids 
    FROM table1
    ORDER BY id
    

    结果:

    | id | type |   uuid | mapped_ids |
    |----|------|--------|------------|
    |  1 |    a | uuid-1 |       10,6 |
    |  2 |    a | uuid-2 |    16,7,11 |
    |  3 |    a | uuid-3 |       8,12 |
    |  4 |    a | uuid-4 |     (null) |
    |  5 |    a | uuid-5 |     (null) |
    |  6 |    b | uuid-1 |       10,1 |
    |  7 |    b | uuid-2 |    16,2,11 |
    |  8 |    b | uuid-3 |       3,12 |
    |  9 |    b | uuid-6 |      15,13 |
    | 10 |    c | uuid-1 |        6,1 |
    | 11 |    c | uuid-2 |     16,2,7 |
    | 12 |    c | uuid-3 |        8,3 |
    | 13 |    c | uuid-6 |       15,9 |
    | 14 |    c | uuid-7 |     (null) |
    | 15 |    d | uuid-6 |       13,9 |
    | 16 |    d | uuid-2 |     2,7,11 |
    

  • 1

    试试这个:

    select
      t1.id, t1.type, t1.master_guid, array_agg (distinct t2.id)
    from
      table1 t1
      left join table1 t2 on
        t1.master_guid = t2.master_guid and
        t1.id != t2.id
    group by
      t1.id, t1.type, t1.master_guid
    

    我没有提出你列出的完全相同的结果,但我认为这是非常接近的,可能是你的方面有错误的期望,或者只是我的一个小错误...无论哪种方式,一个潜在的起点 .

    • 编辑 -

    对于方法#2,我认为您只需要向Table2添加内部联接以获取GUID:

    select
      t1.id, t1.type, t2.master_guid,
      array_agg (t2a.id)
    from
      table1 t1
      join table2 t2 on t1.id = t2.id
      left join table2 t2a on
        t2.master_guid = t2a.master_guid and
        t2a.id != t1.id
    where
      t1.type = 'a'
    group by
      t1.id, t1.type, t2.master_guid
    
评论

暂时没有评论!