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

I would like something like the below:

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:

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