If I have a table like below, how do I get the map of the count of unique values in the arrays in column2?

IDColumn1Column2
110[a, a, b, c]
212[a, a, a]

I would like something like the below:

IDColumn1Column2
110{a: 2, b: 1, c: 1}
212{a: 3}

I tried to use Presto's [histogram][1] for this. But it is an aggregate function that requires group by. I need to use the histogram for each row and not the entire table.

For example,

SELECT distinct ID, histogram(column1) AS column1, column2 FROM table

returns '"ID"' must be an aggregate expression or appear in GROUP BY clause

1 Answer

You can use unnest to expand your array into a column and then use histogram over this new column:

WITH dataset AS ( SELECT * FROM ( VALUES (1, 10, ARRAY['a', 'a', 'b', 'c']), (2, 12, ARRAY['a', 'a', 'a']) ) AS t (ID, Column1, Column2)) SELECT ID, Column1, histogram(Col2) as Column2 FROM dataset CROSS JOIN unnest(Column2) as t(Col2) GROUP BY ID, Column1

Result:

IDColumn1Column2
110{a=2, b=1, c=1}
212{a=3}

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobXBuYmt%2Fc4OOoqVmqKKawLW7jKyopWWYpMRuwM5mmqudkamybq2MppipZZ%2BbeqK%2B0ZqwZq6RocKmv4yapZ1lmanAbq%2FOrqWt