Relational Data - Aggregate Functions - Reference - Summap

Totals a value array according to the keys specified in the key array. Returns a tuple of two arrays: keys in sorted order, and values summed for the corresponding keys without overflow.

Syntax

sumMap(key <Array>, value <Array>) Array type.
sumMap(Tuple(key <Array>, value <Array>)) Tuple type.

Alias: sumMappedArrays.

Arguments

key: Array of keys.
value: Array of values.

Passing a tuple of key and value arrays is a synonym to passing separately an array of keys and an array of values.

The number of elements in key and value must be the same for each row that is totaled.

Returned Value

Returns a tuple of two arrays: keys in sorted order, and values summed for the corresponding keys.

Example

First we create a table called sum_map, and insert some data into it. Arrays of keys and values are stored separately as a column called statusMap of Nested type, and together as a column called statusMapTuple of tuple type to illustrate the use of the two different syntaxes of this function described above.

Query:

CREATE TABLE sum_map(
    date Date,
    timeslot DateTime,
    statusMap Nested(
        status UInt16,
        requests UInt64
    ),
    statusMapTuple Tuple(Array(Int32), Array(Int32))
) ENGINE = Log;

INSERT INTO sum_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 10, 10], ([1, 2, 3], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10], ([3, 4, 5], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 10, 10], ([4, 5, 6], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', [6, 7, 8], [10, 10, 10], ([6, 7, 8], [10, 10, 10]));

Next, we query the table using the sumMap function, making use of both array and tuple type syntaxes:

Query:

SELECT
    timeslot,
    sumMap(statusMap.status, statusMap.requests),
    sumMap(statusMapTuple)
FROM sum_map
GROUP BY timeslot

Result:

┌────────────timeslot─┬─sumMap(statusMap.status, statusMap.requests)─┬─sumMap(statusMapTuple)─────────┐
│ 2000-01-01 00:00:00 │ ([1,2,3,4,5],[10,10,20,10,10])               │ ([1,2,3,4,5],[10,10,20,10,10]) │
│ 2000-01-01 00:01:00 │ ([4,5,6,7,8],[10,10,20,10,10])               │ ([4,5,6,7,8],[10,10,20,10,10]) │
└─────────────────────┴──────────────────────────────────────────────┴────────────────────────────────┘

See Also