Поиск повторяющихся данных в Oracle

У меня есть таблица с более чем 500 000 записей и полями для идентификатора, имени, фамилии и адреса электронной почты. Что я пытаюсь сделать, так это найти строки, в которых имя и фамилия являются дубликатами (поскольку у одного и того же человека есть два отдельных идентификатора, адреса электронной почты или что-то еще, они находятся в таблице более одного раза). Я думаю, что знаю, как найти дубликаты с помощью GROUP BY, вот что у меня есть:

SELECT first_name, last_name, COUNT(*)
FROM person_table
GROUP BY first_name, last_name
HAVING COUNT(*) > 1

Проблема в том, что мне нужно переместить всю строку с этими повторяющимися именами в другую таблицу. Есть ли способ найти дубликаты и получить всю строку? Или хотя бы получить идентификаторы? Я попытался использовать самосоединение, но получил больше строк, чем было в таблице с самого начала. Будет ли это лучшим подходом? Любая помощь будет принята с благодарностью.


database duplicates oracle oracle11g
person Ben Hubbard    schedule 14.10.2011    source источник
comment
Лучше проверьте, действительно ли это дубликаты одного и того же человека. Знаете, в мире нет ни одного John Smith :)   -  person ypercubeᵀᴹ    schedule 14.10.2011

Ответы (4)


arrow_upward
2
arrow_downward

Индекс на (first_name, last_name) или на (last_name, first_name) поможет:

SELECT t.*
FROM 
    person_table t
  JOIN      
      ( SELECT first_name, last_name
        FROM person_table
        GROUP BY first_name, last_name
        HAVING COUNT(*) > 1
      ) dup
    ON  dup.last_name = t.last_name
    AND dup.first_name = t.first_name

or:

SELECT t.*
FROM person_table t
WHERE EXISTS
      ( SELECT *
        FROM person_table dup
        WHERE dup.last_name = t.last_name
          AND dup.first_name = t.first_name
          AND dup.ID <> t.ID
      )
person ypercubeᵀᴹ    schedule 14.10.2011
comment
Это соединение, которое я искал. Так просто, я поражен, что не мог понять это. Также добавил индекс, который очень помог. - person Ben Hubbard; 15.10.2011

arrow_upward
3
arrow_downward

Наиболее эффективный способ удаления повторяющихся строк — это самообъединение:

DELETE FROM person_table a
 WHERE a.rowid > 
       ANY (SELECT b.rowid
              FROM person_table b
             WHERE a.first_name = b.first_name
               AND a.last_name  = b.last_name);

Это удалит все дубликаты, даже если существует более одной повторяющейся строки.

Подробнее об удалении дубликатов и различных методах см. здесь: http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm

Надеюсь, поможет...

РЕДАКТИРОВАТЬ: согласно вашим комментариям, если вы хотите выбрать все дубликаты, кроме одного, тогда

SELECT *
  FROM person_table a
 WHERE a.rowid > 
       ANY (SELECT b.rowid
              FROM person_table b
             WHERE a.first_name = b.first_name
               AND a.last_name  = b.last_name);
person Ollie    schedule 14.10.2011
comment
На самом деле это могло бы сработать, если бы я немного изменил его. Проблема в том, что я не пытаюсь их удалить, а копирую в другую таблицу. Если я выберу вместо удаления, используя это, я получу все, кроме первой строки каждого дублированного имени, верно? - person Ben Hubbard; 14.10.2011
comment
Хорошо, не беспокойтесь, рад, что это помогло. Кстати, использование ROWID — это самый быстрый метод доступа к таблице, даже более быстрый, чем использование первичного ключа таблицы. - person Ollie; 14.10.2011
comment
Пришлось изменить свой комментарий, я думаю, что мне не хватает одной строки для каждого набора дубликатов. Извините, что так переключаюсь, но спасибо за отзыв. - person Ben Hubbard; 14.10.2011
comment
Да, заявление как таковое удалит все дубликаты, кроме одного. Если вы измените его на SELECT, он выберет все дубликаты, кроме одного. - person Ollie; 14.10.2011
comment
почему бы не «a.rowid != ANY»? Недавно у меня была ситуация, когда 'a.rowid ›' не удалось (не все дубликаты были удалены). Изменение на «a.rowid != ANY» помогло... - person rtbf; 24.03.2014

arrow_upward
1
arrow_downward

Это даст вам идентификатор, который вы хотите переместить/удалить/и т. д. Обратите внимание, что это не работает, если count(*) > 2, так как вы получаете только 1 идентификатор (в этих случаях вы можете повторно запустить свой запрос).

SELECT max(ID), first_name, last_name, COUNT(*)
FROM person_table
GROUP BY first_name, last_name
HAVING COUNT(*) > 1

Изменить: вы можете использовать COLLECT, чтобы получить все идентификаторы одновременно (но будьте осторожны, так как вы хотите переместить/удалить только все, кроме одного)

person michael667    schedule 14.10.2011
comment
Спасибо, Майкл. Быстро проследив, я понял, что в любом случае получаю каждое имя, которое дублируется один раз, тогда как мне нужно иметь возможность получить их все. Некоторые дублировались более 40 раз (странно, я знаю). Есть ли более простой способ сделать это, чем повторный запуск запроса? - person Ben Hubbard; 14.10.2011
comment
Большое спасибо, функция сбора дает мне именно то, что мне нужно. - person Ben Hubbard; 14.10.2011

arrow_upward
1
arrow_downward

Чтобы добавить еще один параметр, я обычно использую этот для удаления дубликатов:

delete from person_table
where rowid in (select rid
                  from (select rowid rid, row_number() over
                         (partition by first_name,last_name order by rowid) rn
                          from person_table
                        )
                 where rn <> 1 )
person Aitor    schedule 16.10.2011