MAP_UNION_SUM
Returns a new map that is the union of all input maps. The output map sums the values of the matching keys in all input maps. If the value that corresponds to a key is null, the value is converted into 0.
Usage notes
MaxCompute V2.0 provides new extended functions. If the functions 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 commands. For more information about the precautions you must take when enabling the MaxCompute V2.0 data type edition at the project level, see Data type version guide.
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 additional computing resources as needed.
Syntax
map<K, V> map_union_sum(map<K, V> input);Parameters
input: the input maps.
The values in input maps must be of the BIGINT, INT, SMALLINT, TINYINT, FLOAT, DOUBLE, or DECIMAL data type.
Return value
A new map that is the union of all input maps is returned.
The values in the new map are of the BIGINT, DOUBLE, or DECIMAL type.
Examples
select map_union_sum(a) from values
(map('hi', 2L, 'apple', 3L, 'pie', 1L)), (map('apple', null, 'hi', 4L)), (null) t(a);The following result is returned:
+----------------------------+
| _c0 |
+----------------------------+
| {"apple":3,"hi":6,"pie":1} |
+----------------------------+Related functions
MAP_UNION_SUM is an aggregate function. For more information about the functions that are used to calculate the average value of multiple input records and to aggregate parameters, see Aggregate Functions Overview.