GROUP_CONCAT is a function provided by MySQL. This function is used to concatenate values of specified fields in each group that is generated by using GROUP BY. This topic describes how to use a built-in function of MaxCompute to implement the capabilities that are provided by 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 Supported Supported None.
Delimiter Supported Supported 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 Not supported Supported The WM_CONCAT function does not support sorting for merged values. 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 Supported Supported The WM_CONCAT and GROUP_CONCAT functions are functions that 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 results are 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 results are 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.
    -- Remove the constraint on simultaneous execution of ORDER BY and LIMIT. 
    set odps.sql.validate.orderby.limit=false;
    select name, wm_concat(',', price) as price_new from (select name, price from price_total order by name, price desc) group by name;
    The following results are 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.
    -- Remove the constraint on simultaneous execution of ORDER BY and LIMIT. 
    set odps.sql.validate.orderby.limit=false;
    select name, wm_concat(',', price) as price_new from (select distinct name, price from price_total order by name, price asc) group by name;
    The following results are 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 results are 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  |
    +--------+-----------------+