如何在GROUP_CONCAT()中对值进行排序？

问题场景 #

示例数据：

customer order_id  status
ABC      1234      1
ABC      1235      5
DEF      1236      1
DEF      1237      5

原执行脚本：

SELECT customer, GROUP_CONCAT(CAST(status AS STRING), ', ') AS status
FROM your_table
GROUP BY customer;

得到的结果：

customer   status
 ABC        1, 5
 DEF        5, 1

期望结果：

customer   status
 ABC        1, 5
 DEF        1, 5

尝试的报错脚本：

GROUP_CONCAT(CAST(status AS STRING), ', ' ORDER BY status) AS status_ordered,

正确写法（分不同SQL引擎） #

不同数据库对GROUP_CONCAT类函数的排序语法支持有差异，以下是常见引擎的正确实现：

1. MySQL/MariaDB #

MySQL中GROUP_CONCAT的排序子句需放在函数内部，且需显式指定SEPARATOR关键字：

SELECT customer, 
       GROUP_CONCAT(CAST(status AS CHAR) ORDER BY status SEPARATOR ', ') AS status_ordered
FROM your_table
GROUP BY customer;

2. Hive/Spark SQL #

Hive和Spark不支持直接在GROUP_CONCAT中排序，需先收集排序后的数组再拼接：

-- 通用写法
SELECT customer,
       concat_ws(', ', collect_list(CAST(status AS STRING)) OVER (PARTITION BY customer ORDER BY status)) AS status_ordered
FROM your_table
GROUP BY customer;

-- Spark 2.0+简化写法
SELECT customer,
       concat_ws(', ', sort_array(collect_list(CAST(status AS STRING)))) AS status_ordered
FROM your_table
GROUP BY customer;

3. PostgreSQL #

PostgreSQL使用STRING_AGG替代GROUP_CONCAT，支持直接追加排序规则：

SELECT customer,
       STRING_AGG(CAST(status AS TEXT), ', ' ORDER BY status) AS status_ordered
FROM your_table
GROUP BY customer;

4. BigQuery #

BigQuery同样使用STRING_AGG，排序语法直接内嵌：

SELECT customer,
       STRING_AGG(CAST(status AS STRING), ', ' ORDER BY status) AS status_ordered
FROM your_table
GROUP BY customer;

报错原因 #

你尝试的写法不符合对应SQL引擎的语法规范：比如MySQL中必须显式写SEPARATOR而非直接放分隔符；Hive/Spark不支持GROUP_CONCAT内嵌ORDER BY，需借助数组排序函数实现。

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