sql - 从共享特定属性的 2 列中计算对的唯一组合

Question

给定 2 列事件参与者 ID 和事件：

id  |   event
1   |     A
2   |     A
3   |     A
1   |     B
4   |     B
2   |     C
3   |     C
1   |     D
4   |     D
1   |     E
2   |     E
4   |     E

我希望计算所有可能的、独特的组合共同事件参与者对的出现次数，这类似于：

pair    |    times_co_participate |  co_events
1, 2    |           2             |     A, E
1, 3    |           1             |     A
1, 4    |           3             |     B, D, E
2, 3    |           2             |     A, C
2, 4    |           1             |     E
3, 4    |           0             |     null

id 对可以在 id1 和 id2 的 2 个单独的列中，最终目标是找到具有最高共同参与发生率的对

标签： sqldatabasepostgresqlpsql

Answer 1

使用自联接和聚合：

select t1.id, t2.id, count(*), array_agg(event) as events
from t t1 join
     t t2
     on t1.event = t2.event and t1.id < t2.id
group by t1.id, t2.id