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函数都属于合并行的函数,可以与合并列的函数CONCATCONCAT_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  |
    +--------+-----------------+