Таблица Postgres: поиск дубликатов в двух столбцах независимо от порядка

у меня есть таблица PostgreSQL, описывающая линии между двумя точками. Он содержит два столбца, A и B, целые числа, представляющие идентификатор точки (описанной в другой таблице).

Но каждая строка в таблице дублируется, поскольку линия, идущая от А к Б, совпадает с линией, идущей от В к А.

Я хотел бы удалить дубликаты, но я не могу найти агрегатную функцию, работающую с двумя столбцами, чтобы перегруппировать строки AB и BA, а затем удалить одну.

Спасибо :)


postgresql sql duplicates aggregate-functions
person Laurent Jégou    schedule 12.06.2012    source источник
comment
Есть по крайней мере два возможных способа дублирования в этом случае. 1)., что таблица Lines содержит запись, указывающую на point_id(1), point_id(2), а также запись, указывающую на point_id(2), point_id(1). 2. Две линии имеют разные значения point_id, но при просмотре таблицы point разные point_id могут иметь одинаковые координаты. Не могли бы вы привести примеры для пояснения?   -  person MatBailie    schedule 12.06.2012
comment
Спасибо за ваш комментарий. Дубликаты находятся в point_ids, а не в координатах, поэтому это первый случай вашего вопроса. Более того, все строки дублируются, на каждую строку АВ приходится строка БА, это результат аглоритма построения таблицы.   -  person Laurent Jégou    schedule 12.06.2012

Ответы (2)


arrow_upward
7
arrow_downward

Идентификация дубликатов:

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);

(Не испытано!)

person a_horse_with_no_name    schedule 12.06.2012
comment
Хммм... теперь мне кажется, что это удалит больше, чем просто повторяющиеся записи - person Andomar; 12.06.2012
comment
@LaurentJégou — это удалит все записи для любой строки, в которой есть дубликаты; если строка существует как a,b И b,a, обе записи будут удалены. Ему нужно только иметь WHERE (a,b) IN (, тогда он удалит только экземпляры строки, где a<b. Затем предполагается, что любая строка с дубликатом существует как a,b, так и b,a, а также предполагается, что ни в одной строке не будет нескольких записей a,b (так что будет достаточно удалить все записи b,a). В этом случае он становится функционально очень похожим на мой ответ, но с небольшой дополнительной сложностью;) - person MatBailie; 12.06.2012
comment
Я согласен с комментарием Дема, я использовал where (a, b) в версии, и он удалил только дубликаты. - person Laurent Jégou; 12.06.2012
comment
@LaurentJégou - в этом случае я думаю, что это немного сложно, поскольку оно демонстрирует то же поведение (и предположения), что и мой более простой ответ. Я ожидал бы, хотя я не проверял, что этот ответ также будет медленнее (больше процессора, больше чтений), чем более простой ответ. - person MatBailie; 12.06.2012

arrow_upward
2
arrow_downward

Я оставил комментарий, но сейчас предположу, что единственная разница между двумя дублированными записями заключается в том, что они имеют одинаковые значения 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
             )
person MatBailie    schedule 12.06.2012
comment
+1 Это работает при условии, что единственные дубликаты поменялись местами a, b. Это не сработает, если есть несколько строк с одинаковыми a, b - person Andomar; 12.06.2012
comment
@Andomar - Правильно, вот почему я высказал такое предположение :) Но, что интересно, принятый ответ кажется неверным (удаляет все вхождения, а не только дубликаты), и даже после исправления будет делать практически то же предположение, что и мой ответ. (См. мой комментарий к ответу.) - person MatBailie; 12.06.2012