Найти наиболее распространенные элементы в массиве с группой по

Question

У меня есть таблица строк со следующей структурой name TEXT, favorite_colors TEXT[], group_name INTEGER где в каждой строке есть список всех любимых цветов и группы, к которой принадлежит этот человек. Как я могу GROUP BY group_name и вернуть список наиболее распространенных цветов в каждой группе?

Не могли бы вы сделать комбинацию int[] && int[] установить для перекрытия, int[] & int[] чтобы получить пересечение, а затем что-то еще, чтобы сосчитать и оценить?

Answer 1

Быстро и грязно:

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;

Лучше с LATERAL JOIN

В Postgres 9.3 или новее это более чистая форма:

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;

Вышесказанное является сокращением для

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

И как с любым другим INNER JOIN, это исключило бы строки без цвета (favorite_colors IS NULL) - как и первый запрос.

Чтобы включить такие строки в результат, используйте вместо этого:

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;

На следующем шаге вы можете легко объединить "наиболее распространенные" цвета для каждой группы, но сначала вам нужно будет определить "наиболее распространенные цвета"...

Самые распространенные цвета

Согласно комментарию, выберите цвета с> 3 вхождений.

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;

Чтобы объединить верхние цвета в массиве (в порядке убывания):

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 демонстрирует все.