16.10.2009 Поиск и удаление дубликатов строк
Удаление дубликатов строк в таблице – популярный вопрос на собеседованиях, ответ на который необходимо знать каждому разработчику, несмотря на то, что в правильно спроектированной базе данных такая задача возникать не должна в принципе. Этот популярный вопрос может поставить в тупик даже некоторых опытных разработчиков именно вследствие того, что задача редко возникает в реальных системах.
Постановка задачи:
Существует таблица с данными, в которой существуют повторяющиеся строки по значениям некоторых столбцов. Необходимо получить список повторяющихся строк. Затем необходимо удалить повторяющиеся строки, оставив только по одной уникальной строке. Как вариант необходимо оставить только самые поздние строки. По возможности, привести несколько вариантов решений.
Решение:
В условии задачи может быть указана структура таблицы, в которой необходимо удалить дубликаты, в нашем случае этого нет, поэтому предложим структуру тестовой таблицы. Намеренно усложним задачу, предложив в таблице два поля, комбинация которых может быть неуникальной, а также создадим таблицу без первичного ключа.
CREATE TABLE DUP
( VALUE_1 NUMBER,
VALUE_2 NUMBER
);
( VALUE_1 NUMBER,
VALUE_2 NUMBER
);
Заполним таблицу случайно сгенерированными данными:
INSERT
INTO DUP
SELECT ROUND(DBMS_RANDOM.VALUE * 100),
ROUND(DBMS_RANDOM.VALUE * 100)
FROM ALL_OBJECTS
WHERE ROWNUM<=1000;
COMMIT;
INTO DUP
SELECT ROUND(DBMS_RANDOM.VALUE * 100),
ROUND(DBMS_RANDOM.VALUE * 100)
FROM ALL_OBJECTS
WHERE ROWNUM<=1000;
COMMIT;
Теперь нам необходимо узнать, есть ли в этой таблице строки-дубликаты по паре значений столбцов VALUE_1, VALUE_2. Вывод списка значений дубликатов можно получить при помощи следующего запроса:
SELECT VALUE_1,
VALUE_2,
COUNT(*) AS CNTR
FROM DUP
GROUP BY VALUE_1, VALUE_2
HAVING COUNT(*)>1;
VALUE_2,
COUNT(*) AS CNTR
FROM DUP
GROUP BY VALUE_1, VALUE_2
HAVING COUNT(*)>1;
Смысл этого запроса получить список сгруппированных значений полей VALUE_1, VALUE_2 в том случае, если количество этих групп превышает единицу, для чего в запросе используется агрегатное условие HAVING. В итоге мы получим некоторый список значений, который будет нам показывать, что для каждой пары значений VALUE_1, VALUE_2 в списке есть строки с такой же парой значений. Количество повторений мы видим в столбце CNTR. В этом запросе нам помогают знание группировки в запросе GROUP, и знание агрегатных условий HAVING.
Раз есть дубликаты, их необходимо удалить. Стандартное решение:
DELETE DUP
WHERE ROWID NOT IN (SELECT MAX(ROWID)
FROM DUP
GROUP BY VALUE_1, VALUE_2
);
WHERE ROWID NOT IN (SELECT MAX(ROWID)
FROM DUP
GROUP BY VALUE_1, VALUE_2
);
Для этого примера можно и нужно отметить, что для большого объема данных работать удаление будет долго вследствие выполнения группировка в подзапросе.
В Oracle существует малоизвестная конструкция exception into при включении ограничения на первичный ключ, которая может решить эту задачу намного быстрее. Для выполнения этого способа необходимо создать таблицу исключений, скрипт для создания которой можно найти в
$ORACLE_HOME/rdbms/admin/utlexcpt.sql
CREATE TABLE exceptions
(row_id ROWID,
owner VARCHAR2(30),
table_name VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
(row_id ROWID,
owner VARCHAR2(30),
table_name VARCHAR2(30),
CONSTRAINT VARCHAR2(30)
);
Создав эту таблицу пытаемся создать первичный ключ по нашей паре значений VALUE_1, VALUE_2:
ALTER TABLE DUP
ADD CONSTRAINT DUP_PK
PRIMARY KEY (VALUE_1, VALUE_2)
exceptions INTO EXCEPTIONS;
ADD CONSTRAINT DUP_PK
PRIMARY KEY (VALUE_1, VALUE_2)
exceptions INTO EXCEPTIONS;
При попытке создать этот первичный ключ мы получим следующую ошибку:
ORA-02437: cannot validate (OWNER.DUP_PK) – primary key violated
Это естественно, у нас есть дубликаты строк. Но в таблице EXCEPTIONS у нас будут сохранены ROWID все строк-дубликатов, в чем можно сразу же убедиться:
SELECT D.VALUE_1,
D.VALUE_2
FROM DUP D,
EXCEPTIONS E
WHERE D.ROWID=E.ROW_ID;
D.VALUE_2
FROM DUP D,
EXCEPTIONS E
WHERE D.ROWID=E.ROW_ID;
В результате выполнения этого запроса мы увидим список всех ROWID, которые имеют дубликаты. Далее нам необходимо удалить из таблицы EXCEPTIONS ненужные нам записи-ссылки. Здесь мы оставим только ссылки на строки – дубликаты, удалив ссылки на записи, которые нам в итоге необходимо оставить в основной таблице.
DELETE EXCEPTIONS
WHERE ROW_ID IN (SELECT MAX(E.ROW_ID)
FROM DUP D,
EXCEPTIONS E
WHERE D.ROWID=E.ROW_ID
GROUP BY D.VALUE_1, D.VALUE_2
);
WHERE ROW_ID IN (SELECT MAX(E.ROW_ID)
FROM DUP D,
EXCEPTIONS E
WHERE D.ROWID=E.ROW_ID
GROUP BY D.VALUE_1, D.VALUE_2
);
Удаление записей из EXCEPTIONS на большом объеме данных будет работать очень быстро, несмотря на подзапрос с группировками в основной таблице, так как объем обрабатываемых в основной таблице записей будет ограничен списком ссылок в EXCEPTIONS.
Далее мы просто удалим из основной таблицы строки-дубликаты:
DELETE DUP
WHERE ROWID IN ( SELECT ROW_ID
FROM EXCEPTIONS
);
WHERE ROWID IN ( SELECT ROW_ID
FROM EXCEPTIONS
);
Результат этого способа будет аналогичен результату первого способа удаления строк-дубликатов.
На взгляд автора необходимо четко помнить решения задачи первым способом и знать принцип решения задачи вторым способом. Нет необходимости на память помнить структуру таблицы EXCEPTIONS, важно запомнить, что при попытке создания первичного ключа с опцией exceptions into в указанную таблицу выгружаются ROWID всех записей, не удовлетворяющих условию заданного первичного ключа. Иными словами выгружаются ссылки на записи с интересующими нас дубликатами. Написать все остальные запросы дело техники, это может даже и не потребоваться на собеседовании, главное знать про этот способ и указать, что он значительно быстрее на больших объемах данных и почему происходит выигрыш в скорости.
Смотрите также:
Комментарии читателей
Оставьте свой комментарий
Вы должны быть авторизированны, чтобы оставить комментарий.
Забавно:
exceptions_clause
This clause is valid only if the partitioned table has been defined with a UNIQUE constraint, and that constraint must be in DISABLE VALIDATE state
Решение:
create table dup2 as select distinct value_1, value_1 from dup;
drop table dup PURGE;
alter table dup2 rename to dup;