阿里云云原生大数据计算服务 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函数可实现案例中的如下需求：

• 需求一：将同组的价格（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  |
  +--------+-----------------+

相关文档

• 如果您需要在MaxCompute中实现某些Hive、MySQL、Oracle内建函数的功能，请参见与Hive、MySQL、Oracle内建函数对照表。

• 如果MaxCompute提供的内建函数无法满足您的业务需求时，您可自行开发自定义函数（UDF）以实现业务功能，请参见MaxCompute UDF概述。