This function constructs a map by using two input fields as the key and value. The first field is used as the key of the map. All values of the second field that have the same key are constructed into an array as the value of the map. If the key is NULL, it is ignored.
Usage notes
MaxCompute V2.0 provides additional functions. If the functions that you use involve new data types, including TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY, you must execute a statement to enable the MaxCompute V2.0 data type edition:
Session level: Add
set odps.sql.type.system.odps2=true;before the SQL statement that you want to execute, and commit and execute them together.Project level: The project owner can enable the MaxCompute V2.0 data type edition for the project based on the project requirements. Statement:
setproject odps.sql.type.system.odps2=true;The configuration takes effect after 10 to 15 minutes.
For more information about
setproject, see Project operations. For more information about the precautions that you must take when you enable the MaxCompute V2.0 data type edition at the project level, see Data type editions.
If you use an SQL statement that includes multiple aggregate functions and the project resources are insufficient, memory overflow may occur. We recommend that you optimize the SQL statement or purchase computing resources based on your business requirements.
Syntax
map<K, array<V>> multimap_agg(K a, V b);Parameters
a: the key of the map.
b: Values with the same key are placed in the same array as the value of the map.
Return value
A new map is returned.
Examples
select multimap_agg(a, b) from
values (1L, 'apple'), (2L, 'hi'), (null, 'good'), (1L, 'pie') t(a, b);Sample result:
+----------------------------------+
| _c0 |
+----------------------------------+
| {"2":["hi"],"1":["apple","pie"]} |
+----------------------------------+Related functions
MULTIMAP_AGG is an aggregate function. For more information about other aggregate functions, see Aggregate functions.