sql - 分组后如何比较所有列？

我有一个PostgreSQL表

date      | c1    | c2    | count
----------+-------+-------+------
2015-12-22  A       B       1
2015-12-30  C       D       2
2015-12-31  A       B       3
2015-12-31  A       C       5
2016-01-01  A       B       1
2016-01-01  A       D       7
2016-01-01  B       C       1
2016-01-03  B       D       3
2016-01-03  C       D       1

我想得到的是一个表，其中显示了两个人在某一天相互交流的频率：

date       |AB |AC |AD |BC |BD |CD
-----------+---+---+---+---+---+--
2015-12-22  1   0   0   0   0   0
2015-12-30  0   0   0   0   0   2
2015-12-31  3   5   0   0   0   0
2016-01-01  1   0   7   1   0   0
2016-01-03  0   0   0   0   3   1

我已经按姓名对这些人进行了排序（c1 < c2），但我不知道如何比较所有可能的人并将其选为新列。

最佳答案

您可以通过条件聚合来实现这一点。假设：

select date,
       sum(case when c1 = 'A' and c2 = 'B' then cnt else 0 end) as AB,
       sum(case when c1 = 'A' and c2 = 'C' then cnt else 0 end) as AC,
       sum(case when c1 = 'A' and c2 = 'D' then cnt else 0 end) as AD,
       sum(case when c1 = 'B' and c2 = 'C' then cnt else 0 end) as BC,
       sum(case when c1 = 'B' and c2 = 'D' then cnt else 0 end) as BD,
       sum(case when c1 = 'C' and c2 = 'D' then cnt else 0 end) as CD
from t
group by date
order by date;

关于sql - 分组后如何比较所有列？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44456330/