如何将多列聚合到一个新的列

基本信息

系统 : CentOS Linux release 7.4.1708 (Core)
产品 : OushuDB
模块 : DB
子模块 : -

描述详述

根据一列分组将另一列聚合到一起形成一个新的字段，一般根据需要可以采用 array_agg 或 string_agg

原因

xxx
xxx

解决方案

 name |      c       
------+--------------
 张三 | 红楼梦      
 张三 | 三国演义    
 张三 | 西游记      
 张三 | 西厢记      
 李四 | 凡人修仙录  
 李四 | 诛仙        
 李四 | 大唐双龙传  
 李四 | 诛仙        
(8 rows)

postgres=# select name,array_agg(distinct c) from aaa group by name;
 name |                           array_agg                           
------+---------------------------------------------------------------
 张三 | {"三国演义    ","红楼梦      ","西厢记      ","西游记      "}
 李四 | {"凡人修仙录  ","大唐双龙传  ","诛仙        "}
(2 rows)

postgres=# select name,array_agg( c order by name) from aaa group by name;
 name |                           array_agg                           
------+---------------------------------------------------------------
 张三 | {"西厢记      ","西游记      ","三国演义    ","红楼梦      "}
 李四 | {"诛仙        ","大唐双龙传  ","诛仙        ","凡人修仙录  "}
(2 rows)

postgres=# select name,array_agg(distinct c) from aaa group by name;
 name |                           array_agg                           
------+---------------------------------------------------------------
 张三 | {"三国演义    ","红楼梦      ","西厢记      ","西游记      "}
 李四 | {"凡人修仙录  ","大唐双龙传  ","诛仙        "}
(2 rows)

附件-完整 SQL 文件

drop table if exists aaa;
CREATE TABLE aaa (
    name text,
    c text
)
WITH (appendonly=true, orientation=orc, compresstype=lz4) DISTRIBUTED RANDOMLY;


insert into aaa select '张三','红楼梦      ';
insert into aaa select '张三','三国演义    ';
insert into aaa select '张三','西游记      ';
insert into aaa select '张三','西厢记      ';
insert into aaa select '李四','凡人修仙录  ';
insert into aaa select '李四','诛仙        ';
insert into aaa select '李四','大唐双龙传  ';
insert into aaa select '李四','诛仙        ';
select name,array_agg(distinct c) from aaa group by name;
select name,array_agg( c order by name) from aaa group by name;
select name,array_agg(distinct c) from aaa group by name;