sqlprestotrino

presto sql to generate dynamic dictionary column after group by

Have data like this:

id     timestamp          collection
1111    1721681210135     music
2222    1721681210140     weather
1111    1721681210100     art
1111    1721681210000     music
.........

need to aggregate to a dictionary like column after group by expected output is like:

id      collection_counts
1111    "music":2,"art":1
2222    "weather":1
............

Which function could be proper for this purpose? Presto doc is so easy to find examples.

Solution

  • You can use the histogram function:

    Returns a map containing the count of the number of times each input value occurs.

    select id, histogram(collection) as collection_counts
from data
group by id;

    Output:

    id collection_counts
    1111 {art=1, music=2}
    2222 {weather=1}