GROUP_CONCAT为MySQL提供的函数，用于实现在GROUP BY产生的每一个分组中将指定字段的值连接起来。本文为您介绍如何通过MaxCompute的内建函数实现GROUP_CONCAT函数的能力。

案例

假设，现有一张名为price_total的表，表中包含商品名称（name）、价格（price）和商家ID（saleid）三列，具体数据如下。
+--------+------------+------------+
| name   | price      | saleid     |
+--------+------------+------------+
| bag    | 50         | 1          |
| sugar  | 20         | 3          |
| noodle | 2          | 4          |
| potato | 5          | 6          |
| bag    | 100        | 2          |
| sugar  | 10         | 4          |
| potato | 4          | 3          |
| sugar  | 50         | 7          |
| noodle | 2          | 5          |
| noodle | 5          | 1          |
+--------+------------+------------+
对于该表中的数据，现需要对所有商品按照商品名称（name）进行分组，实现如下需求：
  • 需求一：将同组的价格（price）进行合并，不去重。MySQL的GROUP_CONCAT函数实现代码如下。
    select name, group_concat(price) from price_total group by name;
  • 需求二：将同组的价格（price）进行合并，并去重。MySQL的GROUP_CONCAT函数实现代码如下。
    select name, group_concat(distinct price) from price_total group by name;
  • 需求三：将同组的价格（price）进行合并且排序，不去重。MySQL的GROUP_CONCAT函数实现代码如下。
    select name, group_concat(price order by price desc) from price_total group by name;
  • 需求四：将同组的价格（price）进行合并且排序，并去重。MySQL的GROUP_CONCAT函数实现代码如下。
    select name, group_concat(distinct price order by price desc) from price_total group by name;
  • 需求五：将同组的价格（price）、商家ID（saleid）进行合并。MySQL的GROUP_CONCAT函数实现代码如下。
    select name, group_concat(concat_ws(':', price, saleid)) from price_total group by name;

解决措施

在MaxCompute中，未提供GROUP_CONCAT函数，但您可以通过WM_CONCAT函数实现案例中的需求。

但需要注意的是WM_CONCAT函数并不等价于GROUP_CONCAT函数，二者的能力支持情况如下。

函数能力 WM_CONCAT GROUP_CONCAT 说明
去重（DISTINCT） 已开通 已开通 无
分隔符 已开通 已开通 分隔符用于连接待合并的值。二者的使用区别如下：
  • WM_CONCAT不能省略分隔符。
  • GROUP_CONCAT可以省略分隔符，省略时将英文逗号（,）做为分隔符。
排序 未开通 已开通 WM_CONCAT暂不支持对合并的值进行排序，如果涉及排序需求，您可以通过如下方式解决：
  • 先对待合并字段进行排序，然后再合并。
  • 自定义UDF。更多自定义UDF信息，请参见MaxCompute UDF。
合并多列 已开通 已开通 WM_CONCAT和GROUP_CONCAT函数都属于合并行的函数，可以与合并列的函数CONCAT或CONCAT_WS配合使用实现分组合并多列值。

基于上表所列区别，通过WM_CONCAT函数可实现案例中的如下需求：

  • 需求一：将同组的价格（price）进行合并，不去重。
    select name, wm_concat(',', price) as price_new from price_total group by name;
    返回结果如下。
    +--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 50,100    |
| noodle | 2,2,5     |
| potato | 5,4       |
| sugar  | 20,10,50  |
+--------+-----------+
  • 需求二：将同组的价格（price）进行合并，并去重。
    select name, wm_concat(distinct ',', price) as price_new from price_total group by name;
    返回结果如下。
    +--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 100,50    |
| noodle | 2,5       |
| potato | 4,5       |
| sugar  | 10,20,50  |
+--------+-----------+
  • 需求三：将同组的价格（price）进行合并且排序，不去重。
    --解除order by必须带limit的限制。
set odps.sql.validate.orderby.limit=false;
select name, wm_concat(',', price) as price_new from (select name, price from price_total order by name, price desc) group by name;
    返回结果如下。
    +--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 100,50    |
| noodle | 5,2,2     |
| potato | 5,4       |
| sugar  | 50,20,10  |
+--------+-----------+
  • 需求四：将同组的价格（price）进行合并且排序，并去重。
    --解除order by必须带limit的限制。
set odps.sql.validate.orderby.limit=false;
select name, wm_concat(',', price) as price_new from (select distinct name, price from price_total order by name, price asc) group by name;
    返回结果如下。
    +--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 50,100    |
| noodle | 2,5       |
| potato | 4,5       |
| sugar  | 10,20,50  |
+--------+-----------+
  • 需求五：将同组的价格（price）、商家ID（saleid）进行合并。
    select name, wm_concat(',', concat_ws(':',price,saleid)) as price_new from price_total group by name;
--等效于如下语句。
select name, wm_concat(',', concat(price,':',saleid)) from price_total group by name;
    返回结果如下。
    +--------+-----------------+
| name   | price_new       |
+--------+-----------------+
| bag    | 50:1,100:2      |
| noodle | 2:4,2:5,5:1     |
| potato | 5:6,4:3         |
| sugar  | 20:3,10:4,50:7  |
+--------+-----------------+