PostgreSQL表格：查找两列中的重复项，无论顺序如何

Question

我有一个描述两点之间线条的PostgreSQL表格。它包含两列A和B，这些整数表示另一个表格中点的id。

但是每条线在表格中都是重复的，因为从A到B的线与从B到A的线是相同的。

我想要去除重复项，但我找不到适用于两列的聚合函数，以重新组合AB和BA线，然后删除其中一条。

谢谢 :)

- Laurent Jégou

---

我可以想象在这种情况下至少有两种可能的重复方式。***1).*** Lines 表包含一个指向 point_id(1), point_id(2) 的记录，也包含一个指向 point_id(2), point_id(1) 的记录。***2.*** 当你查看 point 表时，这两条线都具有不同的 point_id 值，但是不同的 point_id 可以具有相同的坐标。你能举个例子来澄清吗？ - MatBailie

感谢您的评论。重复项在point_ids中，而不是坐标，因此这是您问题的第一种情况。此外，所有行都是重复的，对于每个AB线路，都有一个BA线路，这是表格创建算法的结果。 - Laurent Jégou

Answer 1

识别重复项：

select least(a,b), greatest(a,b), count(*)
from the_table
group by least(a,b), greatest(a,b)
having count(*) > 1

我认为您应该能够使用以下方式之一删除其中一个配对：

delete from the_table
where (least(a,b), greatest(a,b)) in (
                select least(a,b), greatest(a,b)
                from the_table
                group by least(a,b), greatest(a,b)
                having count(*) > 1);

（未经过测试！）

- user330315

---

嗯...现在看来，这将删除不止重复的记录。 - Andomar

@LaurentJégou - 这将删除具有重复项的每个行的所有记录。如果行作为“a，b”和“b，a”存在，则两个记录将被删除。它只需要有WHERE（a，b）IN（，然后它将仅删除其中a <b的行的实例。这就假定任何具有重复项的行都存在于a，b和b，a中，并且还假定没有一行将具有多个a，b条目*(因此删除所有b，a条目将足够)*。在这种情况下，它在功能上与我的答案非常相似，但稍微复杂一些。;) - MatBailie

我同意Dems的评论，我使用了“where (a, b) in”的版本，它只删除了重复项。 - Laurent Jégou

@LaurentJégou - 如果是这种情况，我认为这有点过于复杂了，因为它展示了与我的简单答案相同的行为（和假设）。虽然我没有测试过，但我预计这个答案也会比简单的答案更慢（更多的CPU，更多的读取）。 - MatBailie

Answer 2

我已经留下了评论，但现在我假设两个重复记录之间唯一的区别是它们具有相同的point_id值，但是顺序相反。

如果是这样，那么实际上做起来非常简单...

DELETE
  line
WHERE
  point_id_a > point_id_b
  AND EXISTS (SELECT *
                FROM line AS lookup
               WHERE lookup.point_id_a = line.point_id_b
                 AND lookup.point_id_b = line.point_id_a
             )

- MatBailie

---

+1 这个方法假设只有 a, b 互换的重复值。如果存在多行具有相同的 a,b，则无法正常工作。 - Andomar

@Andomar - 正确，这就是我提出这样一个假设的原因 :) 但有趣的是，被接受的答案似乎是错误的（删除所有出现的内容，而不仅仅是重复的），即使进行了更正，也会做出与我的答案几乎相同的假设。（请参见我对答案的评论。） - MatBailie