MaxCompute中实现GROUP_CONCAT函数能力 - 云原生大数据计算服务 MaxCompute

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函数都属于合并行的函数，可以与合并列的函数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）进行合并且排序，不去重。

方式一：

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：实现GROUP_CONCAT函数能力案例

案例

解决措施

相关文档