如何使用SQL或Spark SQL实现多列参与的分组聚合运算

SQL完全支持这类跨列取值后分组拼接的聚合需求，不需要额外借助外部计算工具。

问题回顾 #

现有源表结构及数据如下：

Id col1 col2
1  a    1   
1  b    2   
1  c    3   
2  a    1   
2  e    3   
2  f    4  

期望按Id分组，将每组的col1和col2逐行拼接后合并为单个字符串，输出结果如下：

Id col3
1  a1b2c3
2  a1e3f4

核心实现思路 #

整个计算拆成两个环节完成：

• 行内预处理：把同一行的col1、col2字段值拼接成单个短字符串片段，数值类型的字段会在拼接时自动转换为字符串格式
• 分组聚合：按Id字段分组，将同组下所有短字符串片段按指定顺序拼接为完整的长字符串，就是最终需要的col3结果

注意：不同数据库的字符串聚合函数语法存在差异，以下是主流数据库可直接运行的实现示例，假设源表表名为source_table。

MySQL 5.x/8.x 实现 #

使用MySQL内置的GROUP_CONCAT函数完成聚合，空字符串作为片段间的分隔符：

SELECT
  Id,
  GROUP_CONCAT(CONCAT(col1, col2) ORDER BY col2 SEPARATOR '') AS col3
FROM source_table
GROUP BY Id;

如果需要调整组内拼接顺序，修改ORDER BY后的字段即可。

PostgreSQL 实现 #

使用STRING_AGG聚合函数，支持通过ORDER BY指定拼接顺序：

SELECT
  Id,
  STRING_AGG(CONCAT(col1, col2), '' ORDER BY col2) AS col3
FROM source_table
GROUP BY Id;

SQL Server 实现 #

SQL Server 2017及以上版本支持标准的STRING_AGG语法：

SELECT
  Id,
  STRING_AGG(CONCAT(col1, col2), '') WITHIN GROUP (ORDER BY col2) AS col3
FROM source_table
GROUP BY Id;

2017以下的低版本可以通过FOR XML PATH的兼容写法实现：

SELECT
  t1.Id,
  (
    SELECT CONCAT(col1, col2)
    FROM source_table t2
    WHERE t2.Id = t1.Id
    ORDER BY col2
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)') AS col3
FROM source_table t1
GROUP BY t1.Id;

Oracle 实现 #

使用Oracle内置的LISTAGG函数完成字符串聚合：

SELECT
  Id,
  LISTAGG(col1 || col2, '') WITHIN GROUP (ORDER BY col2) AS col3
FROM source_table
GROUP BY Id;

如果运行时遇到类型转换报错，可以手动把数值型的col2显式转为字符串类型再拼接，比如MySQL中写CONCAT(col1, CAST(col2 AS CHAR))即可。

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