Take average of map values in Trino-CodePudding

I have a column of Map(String(), Double()).

I want to take the average of the (non-null) values for each key that appears in the column.

SELECT 
    AVG(ELEMENT_AT(action_to_score, 'x')) as x,
    AVG(ELEMENT_AT(action_to_score, 'y')) as y
FROM (
    VALUES
        (map_from_entries(ARRAY[('x', 1), ('y', 1)])),
        (map_from_entries(ARRAY[('y', 2)])),
        (map_from_entries(ARRAY[('x', 3)]))
) AS t(action_to_score)

This above query returns

  x    y
2.0  1.5

I want to make the query without hardcoding the keys (namely x and y) in the query. Thanks!

CodePudding user response：

AFAIK it is not possible, cause Trino does not support dynamic pivoting, the only "dynamic" option is to transform data into key-value columns by flattening map with unnest (I use succinct syntax, which allows to skip writing CROSS JOIN explicitly) and then performing the needed aggregation over group by:

SELECT k key,
    AVG(v) avg
FROM (
    VALUES
        (map_from_entries(ARRAY[('x', 1), ('y', 1)])),
        (map_from_entries(ARRAY[('y', 2)])),
        (map_from_entries(ARRAY[('x', 3)]))
) AS t(action_to_score),
unnest(action_to_score) as ut(k, v)
group by k;

Output:

key	avg
y	1.5
x	2.0