sql - 获取postgres中数组元素相互出现的次数

Question

我正在尝试获取两个元素在数组中一起出现的次数。我的表如下所示：

id    districts
---------------------------------------
1     {district1, district2}
2     {district1, district2, district3}
3     {district5, district2, district4}
4     {district2, district3, district1}
5     {district1, district5, district3}
...

我想获得多个地区一起在同一个数组中的次数。所以这个例子的结果是：

district1, district2, 2
district1, district3, 1
district1, district3, 3
...

对于它们的所有组合，我都需要它。关于如何获得这样的任何结果的任何建议都会有很大的帮助

标签： sqlarrayspostgresql

Answer 1

取消嵌套、连接和聚合：

with d as (
      select t.id, district
      from d cross join lateral
           unnest(districts) district
     )
select d1.district, d2.district, count(*) as cooccurrences
from d d1 join
     d d2
     on d1.id = d2.id and d1.district < d2.district
group by d1.district, d2.district