文档

实现GROUP_CONCAT函数能力案例

更新时间:

GROUP_CONCAT为MySQL提供的聚合函数,用于在GROUP BY产生的每一个分组中将多个行的列值连接成一个单独的字符串,在需要将多个记录合并为一条记录时使用。如果您在MaxCompute中需要类似功能,可使用WM_CONCAT函数实现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可以省略分隔符,省略时将英文逗号(,)作为分隔符。

排序

已开通

已开通

如果涉及排序需求,您还可以通过如下方式解决:

  • 先对待合并字段进行排序,然后再合并。

  • 自定义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)进行合并且排序,不去重 。

    • 方式一:

      SELECT name, wm_concat(',', price) WITHIN GROUP (ORDER BY price desc) AS price_new 
      FROM (SELECT name, price FROM price_total) GROUP BY name;
    • 方式二:

      SELECT name, wm_concat(',', price) AS price_new 
      FROM (SELECT name, price FROM price_total ORDER BY name, price desc LIMIT 100) GROUP BY name;
    • 返回结果:

      +--------+-----------+
      | name   | price_new |
      +--------+-----------+
      | bag    | 100,50    |
      | noodle | 5,2,2     |
      | potato | 5,4       |
      | sugar  | 50,20,10  |
      +--------+-----------+
  • 需求四:将同组的价格(price)进行合并且排序,并去重。

    • 方式一:

      SELECT name, wm_concat(',', price)  within GROUP (ORDER BY price asc)
      FROM (SELECT DISTINCT name, price FROM price_total) GROUP BY name;
    • 方式二:

      SELECT name, wm_concat(',', price) AS price_new 
      FROM (SELECT DISTINCT name, price FROM price_total ORDER BY name, price asc LIMIT 100) 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  |
    +--------+-----------------+

相关文档