Implement capabilities provided by the GROUP_CONCAT function - MaxCompute

GROUP_CONCAT is an aggregate function provided by MySQL. This function is used to concatenate column values of multiple rows in each group that is generated by using GROUP BY into an independent string. You can use this function when you want to combine multiple data records into one data record. You can use the WM_CONCAT function in MaxCompute to implement the same effect of the GROUP_CONCAT function.

Example

A table named price_total contains the following columns: name, price, and saleid. The table contains the following data:

+--------+------------+------------+
| 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          |
+--------+------------+------------+

In this example, all products are grouped by product name, which is the name column in the table. You can use the GROUP_CONCAT function in MySQL to meet the following requirements:

Requirement 1: Merge the values in the price column of the same group. The returned results contain duplicate values. Sample statement:
```
SELECT name, group_concat(price) FROM price_total GROUP BY name;
```
Requirement 2: Merge the values in the price column of the same group. The returned results do not contain duplicate values. Sample statement:
```
SELECT name, group_concat(distinct price) FROM price_total GROUP BY name;
```
Requirement 3: Merge and sort the values in the price column of the same group. The returned results contain duplicate values. Sample statement:
```
SELECT name, group_concat(price ORDER BY price desc) FROM price_total GROUP BY name;
```
Requirement 4: Merge and sort the values in the price column of the same group. The returned results do not contain duplicate values. Sample statement:
```
SELECT name, group_concat(distinct price ORDER BY price desc) FROM price_total GROUP BY name;
```
Requirement 5: Merge the values in the price column and the values in the saleid column of the same group. Sample statement:
```
SELECT name, group_concat(concat_ws(':', price, saleid)) FROM price_total GROUP BY name;
```

Solution

MaxCompute does not support the GROUP_CONCAT function. You can use the WM_CONCAT function to meet the requirements in this example.

Note that the WM_CONCAT function is not equivalent to the GROUP_CONCAT function. The following table describes the capabilities of the functions.

Capability	WM_CONCAT	GROUP_CONCAT	Description
Deduplication by using DISTINCT			None.
Delimiter			A delimiter can be used to concatenate the values that you want to merge. Take note of the following points when you include delimiters in the WM_CONCAT and GROUP_CONCAT functions: Delimiters cannot be omitted when you use the WM_CONCAT function. Delimiters can be omitted when you use the GROUP_CONCAT function. If delimiters are omitted, commas (,) are used as delimiters.
Sorting			If you want to sort values, use the following method: Sort the fields that you want to merge and then merge the fields. Use user-defined functions (UDFs). For more information about UDFs, see MaxCompute UDF.
Merging of multiple columns			The WM_CONCAT and GROUP_CONCAT functions are functions that are used to merge rows. The two functions can be used with the CONCAT function or the CONCAT_WS function that is used to merge columns to merge values of multiple columns in different groups.

You can use the WM_CONCAT function to meet the following requirements based on the function capabilities that are described in the preceding table:

Requirement 1: Merge the values in the price column of the same group. The returned results contain duplicate values.

SELECT name, wm_concat(',', price) AS price_new FROM price_total GROUP BY name;

The following result is returned:

+--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 50,100    |
| noodle | 2,2,5     |
| potato | 5,4       |
| sugar  | 20,10,50  |
+--------+-----------+

Requirement 2: Merge the values in the price column of the same group. The returned results do not contain duplicate values.

SELECT name, wm_concat(distinct ',', price) AS price_new FROM price_total GROUP BY name;

The following result is returned:

+--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 100,50    |
| noodle | 2,5       |
| potato | 4,5       |
| sugar  | 10,20,50  |
+--------+-----------+

Requirement 3: Merge and sort the values in the price column of the same group. The returned results contain duplicate values.

Method 1:

SELECT name, wm_concat(',', price) WITHIN GROUP (ORDER BY price desc) AS price_new 
FROM (SELECT name, price FROM price_total) GROUP BY name;

Method 2:

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;

The following result is returned:

+--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 100,50    |
| noodle | 5,2,2     |
| potato | 5,4       |
| sugar  | 50,20,10  |
+--------+-----------+

Requirement 4: Merge and sort the values in the price column of the same group. The returned results do not contain duplicate values.

Method 1:

SELECT name, wm_concat(',', price)  within GROUP (ORDER BY price asc)
FROM (SELECT DISTINCT name, price FROM price_total) GROUP BY name;

Method 2:

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;

The following result is returned:

+--------+-----------+
| name   | price_new |
+--------+-----------+
| bag    | 50,100    |
| noodle | 2,5       |
| potato | 4,5       |
| sugar  | 10,20,50  |
+--------+-----------+

Requirement 5: Merge the values in the price column and the values in the saleid column of the same group.

SELECT name, wm_concat(',', concat_ws(':',price,saleid)) AS price_new FROM price_total GROUP BY name;
-- The preceding statement is equivalent to the following statement: 
SELECT name, wm_concat(',', concat(price,':',saleid)) FROM price_total GROUP BY name;

The following result is returned:

+--------+-----------------+
| 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  |
+--------+-----------------+

References

For more information about how to implement the built-in functions of Hive, MySQL, and Oracle in MaxCompute, see Mappings between built-in functions of MaxCompute and built-in functions of Hive, MySQL, and Oracle.
If the built-in functions provided by MaxCompute cannot meet your business requirements, you can develop UDFs. For more information about UDFs, see Overview.