PostgreSQL - 检查反向值行的存在的最佳方法

Question

我知道我没有找到合适的标题。

为了论证，我有这张表：

sender|receiver
   a  |   b
   c  |   d
   d  |   e
   b  |   a
   f  |   q
   q  |   f
   t  |   u

我想计算表格中有多少行是反向的。例如，行a|b在表格中的反向是b|a。同样，行f|q在表格中的反向是q|f。因此，对于这个表格，我希望得到“2”的答案。

我计算方法如下：

CREATE TABLE #temptab 
(
  sender     VARCHAR,
  receiver   VARCHAR
);

CREATE TABLE #temptab2 
(
  receiver   VARCHAR,
  sender     VARCHAR
);

INSERT INTO #temptab
(
  sender,
  receiver
)
SELECT DISTINCT sender,
       receiver
FROM table

INSERT INTO #temptab2
(
  receiver,
  sender
)
SELECT DISTINCT receiver,
       sender
FROM table

SELECT COUNT(sender)
FROM (SELECT sender,receiver FROM #temptab INTERSECT SELECT receiver,sender FROM #temptab2

有没有更快的方法可以做到这件事？

- Berkay Çakır

Answer 1

我会直接这么做：

我只会这样做：

select count(*)
from #temptab t
where t.sender < t.receiver and
      exists (select 1
              from #temptab tt
              where tt.sender = t.receiver and tt.receiver = t.sender
             );

这应该在Postgres上运行得很好。我不确定在Amazon Redshift上的性能如何。

另一种方法将使用两个聚合：

select count(*)
from (select least(sender, receiver) as x1, greatest(sender_receiver) as x2,
             count(distinct sender) as cnt
      from #temptab
      group by x1, x2
     ) t
where cnt = 2;

然而，你使用 intersect 的版本可能更快。

- Gordon Linoff

---

非常感谢。第一个减少了我的查询时间从1分钟到约35秒。 - Berkay Çakır

Answer 2

通常最快的方法是使用join（特别是如果您在两个列上有索引）：

select count(*)/2
from sr as t1 join sr as t2 on t2.sender=t1.receiver and t2.receiver=t1.sender;

如果您没有sender=receiver的行，您也可以使用以下方法：

select count(*)
from sr as t1 join sr as t2 on t2.sender=t1.receiver and t2.receiver=t1.sender
where t1.sender < t1.receiver; 

在这两种情况下，将“sr”替换为您的表的名称。

- RafaelCaballero