PostgreSQL 实现多行数据与一条数据的互相转换

遇到一个场景是要把分组后的某个字段的多行数据组装成一个列表，开始我的做法是先查出全部数据，再遍历组装成一个 {key: list} 的结构来使用。但这样就比较繁琐，所以寻找了一种更加简便的做法。

补充：如何把一个数组内的内容展开成多行，方便做 group by 去统计数组中元素的个数。（见场景三）

使用

以下表为例：

class	gender	name
1	M	LiLei
1	F	HanMM
1	M	Jim
2	F	Kate
2	M	Peter

场景一

根据需求希望输出以下数据：

class	names
1	LiLei,HanMM,Jim
2	Kate,Peter

使用 string_agg 指定分隔符把某个字段的所有行拼接成字符串：

SELECT class, string_agg(name, ',') AS names FROM students GROUP BY class;

如果想 names 是以数组形式输出可以使用 array_agg ：

SELECT class, array_agg(name) AS names FROM students GROUP BY class;

SQLAlchemy 下可以这样使用：

db.session.query(
    Students.class,
    func.string_agg(Students.name, ',').label('names')
).group_by(
    Students.class
).all()

db.session.query(
    Students.class,
    func.array_agg(Students.name).label('names')
).group_by(
    Students.class
).all()

场景二

另外一种常见需求，如下所示：

class	count_m	count_f
1	2	1
2	1	1

可以使用以下语句：

SELECT class
,SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS count_m
,SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS count_f
FROM students
GROUP BY class;

场景三（unnest 将数组展开成多条记录）

以下表为例：

post	category
post_1	{Python,SQL,Redis}
post_2	{Python,Kafka}
post_3	{SQL}

展开数组：

SELECT unnest(category) AS cate FROM table_name;

输出：

cate
Python
SQL
Redis
Python
Kafka
SQL

统计各个分类的个数：

SELECT unnest(category) AS cate, count(*) AS cate_count 
FROM table_name 
GROUP BY cate;

输出：

cate	cate_count
Kafka	1
SQL	2
Python	2
Redis	1