问题

这个问题，是在一个PG群里有群友问到的。如下:

请教个问题
有个表 temp
数据如下(ids为array类型)
ids   no
{1,2}   10
{1,3}   20
{2,3}   30

现在希望得到这样的结果

id  no
1  30
2  40
3  50
这个group by应该怎么写？

建表

create table tep (ids int[], no int);

插入示例数据

insert into tep values (array[1,2], 10);
insert into tep values (array[1,3], 20);
insert into tep values (array[2,3], 30);

SQL

解决思路：因为他想要的是：

1=10+20
2=10+30
3=20+30

所以，可以将数组中的元素，拆分成行，然后group by 一下id, sum(no)，就可以了，即：

yang=# with cte as (select unnest(ids) as id, no from tep)  select id, sum(no) from cte  group by id order by id asc;
 id | sum
----+-----
  1 |  30
  2 |  40
  3 |  50
(3 rows)

yang=#


或

yang=# select unnest(ids) as id, sum(no) from tep group by id order by id asc;
 id | sum
----+-----
  1 |  30
  2 |  40
  3 |  50
(3 rows)

yang=#

不知道为什么，总喜欢使用CTE来做.感觉这样子更清晰点，哈哈.

使用CTE，无索引的情况，与不使用CTE对比.

yang=# explain analyze verbose with cte as (select unnest(ids) as id, no from tep)  select id, sum(no) from cte  group by id order by id asc;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=306537.78..306538.28 rows=200 width=8) (actual time=8093.771..8093.771 rows=7 loops=1)
   Output: cte.id, (sum(cte.no))
   Sort Key: cte.id
   Sort Method: quicksort  Memory: 25kB
   CTE cte
     ->  Seq Scan on public.tep  (cost=0.00..77148.14 rows=9175200 width=33) (actual time=0.076..3207.449 rows=7340032 loops=1)
           Output: unnest(tep.ids), tep.no
   ->  HashAggregate  (cost=229380.00..229382.00 rows=200 width=8) (actual time=8093.757..8093.759 rows=7 loops=1)
         Output: cte.id, sum(cte.no)
         Group Key: cte.id
         ->  CTE Scan on cte  (cost=0.00..183504.00 rows=9175200 width=8) (actual time=0.082..5851.946 rows=7340032 loops=1)
               Output: cte.id, cte.no
 Planning time: 0.175 ms
 Execution time: 8111.710 ms
(14 rows)

yang=# explain analyze verbose select unnest(ids) as id, sum(no) from tep group by id order by id asc;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3659321.29..3659323.04 rows=700 width=33) (actual time=5276.652..5276.653 rows=7 loops=1)
   Output: (unnest(ids)), (sum(no))
   Sort Key: (unnest(tep.ids))
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=3659284.66..3659288.21 rows=700 width=33) (actual time=5276.637..5276.639 rows=7 loops=1)
         Output: (unnest(ids)), sum(no)
         Group Key: unnest(tep.ids)
         ->  Seq Scan on public.tep  (cost=0.00..1858440.66 rows=360168800 width=33) (actual time=0.052..3203.487 rows=7340032 loops=1)
               Output: unnest(ids), no
 Planning time: 0.212 ms
 Execution time: 5276.723 ms
(11 rows)

yang=#

这情况下，不使用CTE好点.