按组查找数组中最常见的元素

Question

我有一张表，其行结构如下：name TEXT, favorite_colors TEXT[], group_name INTEGER，其中每一行都有一个人最喜欢的颜色列表和该人所属的组。我应该如何使用GROUP BY group_name并返回每个组中最常见的颜色列表？

您可以使用int[] && int[]进行组合以设置重叠部分，使用int[] & int[]获取交集，然后使用其他方法进行计数和排名吗？

- mhkeller

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更好

在Postgres9.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 demonstrating all.

- Erwin Brandstetter

---

谢谢，您能解释一下如何通过这些整数进行分组以实现此目的吗？如果我想要使用 HAVING 条件来查询仅具有超过 3 次出现次数的颜色，我应该在哪里放置 HAVING 语句。我一直收到语法错误。 - mhkeller

@mhhkeller：请考虑一下附录和更新后的代码示例。 - Erwin Brandstetter