CODEWITHSUNDEEP
clickhouse
MHS
MHS

Reputation: 35

ClickHouse - How to sum nested map, with keys from another array?

I have the following table, where names is an array of names and data is a map:

names data
[['James', 'Scott'], ['James', 'Scott']] [[{'sales': 20.0}, {'sales': 20.0}], [{'sales': 25.0}, {'sales': 15.0}]]

What I am trying to do is sum each entry in the map with the corresponding name, and get the following result:

names data
['James', 'Scott'] [{'sales': 45.0}, {'sales': 35.0}] 
create table i_hate_people_who_answer_questions(
names Array(Array(String)),
data Array(Array(Map(String, Float64)))
)
Engine=Memory;

insert into i_hate_people_who_answer_questions
values( [['James', 'Scott'], ['James', 'Scott']], [[map('sales', 20.0), map('sales', 20.0)], [map('sales', 25.0), map('sales', 15.0)]] ) ;

I tried doing sumMap(names, data) but this does not work because data is another map and this function is expecting primitive data types, gives the following error: "Illegal type Map(String, Decimal(38, 2)) of argument for aggregate function sum."

Any help would be appreciated, thanks.

Upvotes: 0

Views: 1306

Answers (1)

Denny Crane
Denny Crane

Reputation: 13245

SELECT
    __names,
    sumMap(__data)
FROM
(
    SELECT
        __names,
        __data
    FROM
    (
        SELECT
            _names,
            _data
        FROM i_hate_people_who_answer_questions
        ARRAY JOIN
            names AS _names,
            data AS _data
    )
    ARRAY JOIN
        _names AS __names,
        _data AS __data
)
GROUP BY __names

┌─__names─┬─sumMap(__data)─┐
│ James   │ {'sales':45}   │
│ Scott   │ {'sales':35}   │
└─────────┴────────────────┘

SELECT
    groupArray(__names),
    groupArray(___data)
FROM
(
    SELECT
        __names,
        sumMap(__data) AS ___data
    FROM
    (
        SELECT
            __names,
            __data
        FROM
        (
            SELECT
                _names,
                _data
            FROM i_hate_people_who_answer_questions
            ARRAY JOIN
                names AS _names,
                data AS _data
        )
        ARRAY JOIN
            _names AS __names,
            _data AS __data
    )
    GROUP BY __names
)

┌─groupArray(__names)─┬─groupArray(___data)─────────┐
│ ['James','Scott']   │ [{'sales':45},{'sales':35}] │
└─────────────────────┴─────────────────────────────┘

Upvotes: 1

Related Questions