使用PostgreSQL的listagg函数进行分组排序聚合操作，并且了解string_agg的兼容用法。包括行列转换和CSV构造等技巧。

string_agg 代替listagg，实现同样功能。

建表

postgres=# create table tbl1 (gid int, val text, ts timestamp default clock_timestamp());  
CREATE TABLE  

写入测试数据

postgres=# insert into tbl1 values (1,'a'),(1,'b'),(1,null),(2,'test'),(2,'a""b"c'),(3,'fw');  
INSERT 0 6  

数据

postgres=# select * from tbl1;  
 gid |  val   |             ts               
-----+--------+----------------------------  
   1 | a      | 2018-10-29 21:00:24.593859  
   1 | b      | 2018-10-29 21:00:24.593994  
   1 |        | 2018-10-29 21:00:24.593997  
   2 | test   | 2018-10-29 21:00:24.593998  
   2 | a""b"c | 2018-10-29 21:00:24.594  
   3 | fw     | 2018-10-29 21:00:24.594001  
(6 rows)  

逆向聚合，双引号作为quote字符，转义文本内的双引号，空值使用NULL表示。

postgres=# select gid, string_agg(coalesce('"'||replace(val,'"','\"')||'"','NULL'),',' order by ts desc) from tbl1 group by gid;  
 gid |     string_agg       
-----+--------------------  
   1 | NULL,"b","a"  
   2 | "a\"\"b\"c","test"  
   3 | "fw"  
(3 rows)  

正向聚合，双引号作为quote字符，转义文本内的双引号，空值使用NULL表示。

postgres=# select gid, string_agg(coalesce('"'||replace(val,'"','\"')||'"','NULL'),',' order by ts) from tbl1 group by gid;  
 gid |     string_agg       
-----+--------------------  
   1 | "a","b",NULL  
   2 | "test","a\"\"b\"c"  
   3 | "fw"  
(3 rows)  

正向聚合，不使用QUOTE，直接去除NULL值

postgres=# select gid, string_agg(val,',' order by ts) from tbl1 group by gid;  
 gid | string_agg    
-----+-------------  
   1 | a,b  
   2 | test,a""b"c  
   3 | fw  
(3 rows)  

order by 任意字段、表达式、转换

order by可以任意字段、表达式、类型转换

select gid, string_agg(val,',' order by xx::numeric) from tbl1 group by gid;

select gid, string_agg(val,',' order by abs(xxx)) from tbl1 group by gid;

select gid, string_agg(val,',' order by mod(x,5),xxxx) from tbl1 group by gid;

postgres=# create table tbl(id int, c1 text);
CREATE TABLE
postgres=# insert into tbl values (1,'1'),(2,'12'),(3,'2');
INSERT 0 3

postgres=# select string_agg(c1,',' order by c1::numeric) from tbl;
 string_agg 
------------
 1,2,12
(1 row)

postgres=# select string_agg(c1,',' order by c1) from tbl;
 string_agg 
------------
 1,12,2
(1 row)