PostgreSQL：查询数组元素的共现次数

实现思路 #

要统计文本数组中每个元素与其他元素的共现次数，分三步处理即可：

1. 将数组拆分为单个元素，并关联到原数据行；
2. 匹配同一行内的不同元素，统计两两组合的出现次数；
3. 将每个元素对应的共现结果聚合为指定格式。

完整SQL查询 #

假设你的表名为your_table，请替换为实际表名：

WITH expanded AS (
  -- 给每行数据分配唯一标识，同时拆分数组为单个元素
  SELECT
    row_number() OVER () AS row_id,
    unnest(items) AS item
  FROM your_table
),
co_occur_counts AS (
  -- 统计同一行内不同元素的共现次数
  SELECT
    e1.item AS item,
    e2.item AS co_item,
    COUNT(*) AS count
  FROM expanded e1
  JOIN expanded e2 
    ON e1.row_id = e2.row_id 
    AND e1.item != e2.item  -- 排除元素与自身的匹配
  GROUP BY e1.item, e2.item
)
-- 聚合每个元素的共现结果为指定格式
SELECT
  item,
  '{' || string_agg(co_item || ':' || count, ',') || '}' AS co_occurrences
FROM co_occur_counts
GROUP BY item
ORDER BY item;

关键说明 #

• 如果你的表有主键（比如id列），建议用主键替换row_number() OVER () AS row_id，性能更优且结果更可靠；
• string_agg函数用于将多个co_item:count字符串拼接为一个整体，再用{}包裹得到期望的格式；
• 自连接时通过e1.row_id = e2.row_id确保只统计同一数组内的元素共现，e1.item != e2.item避免统计元素自身。

测试结果 #

用你提供的示例数据运行查询，会得到如下结果：

item|co_occurrences
-------------------
a   |{c:4,b:2}
b   |{a:2,c:2}
c   |{a:4,b:2}

内容的提问来源于stack exchange，提问作者mhkeller