如何在Postgres中按数组列对结果进行分组？

Question

我有这么一张桌子

id SERIAL,
user_id INT,
community_id INT[],

这张桌子是这样填满的：

id | user_id | community_id 
1  |  1      |   {2, 4}
2  |  5      |   {2, 5} 
3  |  10     |   {2, 4}

我想了解每个社区的用户数量，community_id是数组用户，因为用户可以同时在几个社区中。

查询应该很简单，因为：

SELECT community_id, COUNT(user_id) FROM tbl GROUP BY community_id

结果应该是这样：

community_id  | user_count
2             |  3
4             |  2
5             |  1

我不知道如何使用GROUP BY数组列。有人能帮我吗？

Answer 1

您可以使用unnest()获取数据的规范化视图，以及聚合：

select community_id, count(*)
from (
  select unnest(community_id) as community_id
  from tbl 
) t
group by community_id
order by community_id;

但是你真的应该修正你的数据模型。

Answer 2

SELECT unnest(community_id) community_id ,
       count(user_id) user_count
FROM TABLE_NAME
GROUP BY 1 --community_id = 1 and user_count = 2 (index of a column in select query)
ORDER BY 1

取消嵌套(任意数组)：将数组展开为一组行

即select unnest(ARRAY[1,2])会给

   unnest
   ------
       1
       2