How to count array elements occurrences in Presto?

user_13214631 0
Viewed 5092

I have an array in Presto and I'd like to count how many times each element occurs in it. For example, I have 

[a, a, a, b, b]

and I'd like to get something like

{a: 3, b: 2}
presto sql
3 Answers

We do not have a direct function for this, but you can combine UNNEST with histogram:

presto> SELECT histogram(x)
     -> FROM UNNEST(ARRAY[1111, 1111, 22, 22, 1111]) t(x);
     _col0
----------------
 {22=2, 1111=3}

You may want to file a new issue for a direct function for this.

user_654580 
SELECT
    TRANSFORM_VALUES(
        MULTIMAP_FROM_ENTRIES(
            TRANSFORM(ARRAY['a', 'a', 'a', 'b', 'b'], x -> ROW(x, 1))
        ),
        (k, v) -> ARRAY_SUM(v)
    )

Output: { "a": 3, "b": 2 }

user_52618830

You can use REDUCE if there is no support of ARRAY_SUM:

SELECT
    TRANSFORM_VALUES(
        MULTIMAP_FROM_ENTRIES(
            TRANSFORM(ARRAY['a', 'a', 'a', 'b', 'b'], x -> ROW(x, 1))
        ),
        (k, v) -> REDUCE(v, 0, (s, x) -> s + x, s -> s)
    )
user_19613540
Related