Find the most common elements in an array with a group

Question

I have a row table with the following structure name TEXT, favorite_colors TEXT[], group_name INTEGER , where each row has a list of all favorite colors and the group to which the person belongs. How can I GROUP BY group_name and return a list of the most common colors in each group?

Can you make a combination of int[] && int[] to set to overlap, int[] & int[] to get the intersection, and then something else to count and rank?

Answer 1

Quick and dirty:

 SELECT group_name, color, count(*) AS ct FROM ( SELECT group_name, unnest(favorite_colors) AS color FROM tbl ) sub GROUP BY 1,2 ORDER BY 1,3 DESC; 

Better with LATERAL JOIN

In Postgres 9.3 or later, this is a cleaner form:

 SELECT group_name, color, count(*) AS ct FROM tbl t, unnest(t.favorite_colors) AS color GROUP BY 1,2 ORDER BY 1,3 DESC; 

Above brief description for

 ... FROM tbl t JOIN LATERAL unnest(t.favorite_colors) AS color ON TRUE ... 

And as with any other INNER JOIN , it will exclude rows without color ( favorite_colors IS NULL ) - like the first request.

To include such lines in the result, use instead:

 SELECT group_name, color, count(*) AS ct FROM tbl t LEFT JOIN LATERAL unnest(t.favorite_colors) AS color ON TRUE GROUP BY 1,2 ORDER BY 1,3 DESC; 

You can easily aggregate the “most common” colors for each group in the next step, but first you need to determine the “most common colors” ...

Most common colors

According to the comment, select the colors s> 3 occurrences.

 SELECT t.group_name, color, count(*) AS ct FROM tbl t, unnest(t.favorite_colors) AS color GROUP BY 1,2 HAVING count(*) > 3 ORDER BY 1,3 DESC; 

To combine the top colors in an array (in descending order):

 SELECT group_name, array_agg(color) AS top_colors FROM ( SELECT group_name, color FROM tbl t, unnest(t.favorite_colors) AS color GROUP BY 1,2 HAVING count(*) > 3 ORDER BY 1, count(*) DESC ) sub GROUP BY 1; 

→ SQLfiddle showing everything.