如何将SQL表中分隔字符串拆分为两个新列及处理string_agg问题

我使用如下SQL进行聚合查询：

SELECT something, string_agg(other, ';') FROM table
GROUP BY something HAVING COUNT(*)>1;

但无法将string_agg的结果拆分为两个新列，系统无法识别该聚合结果为列。

原始数据表结构：

期望得到的表结构：

解决方案 #

场景1：直接拆分原始字段（无需聚合） #

从原始数据和期望结果来看，每条记录的other字段本身就是逗号分隔的两个值，这种情况不需要先聚合，直接用split_part函数拆分即可：

SELECT 
  something,
  split_part(other, ', ', 1) AS other,
  split_part(other, ', ', 2) AS new
FROM your_table;

split_part函数说明：按指定分隔符（这里是, ，注意逗号加空格）拆分字符串，第三个参数指定取拆分后的第N个部分。

场景2：先聚合再拆分（针对确实需要聚合的场景） #

如果业务逻辑必须先通过string_agg聚合，再拆分结果列，需要给聚合结果设置别名，在外层查询中基于别名拆分：

SELECT 
  something,
  split_part(aggregated_col, ';', 1) AS other,
  split_part(aggregated_col, ';', 2) AS new
FROM (
  SELECT 
    something, 
    string_agg(other, ';') AS aggregated_col
  FROM your_table
  GROUP BY something 
  HAVING COUNT(*) > 1
) AS agg_subquery;

原理：子查询完成聚合并为结果列命名，外层查询就能识别该列并进行拆分操作。

内容的提问来源于stack exchange，提问作者GJO