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函數能力案例

案例

解決措施

相關文檔