Удаление дубликатов из SQL таблицы без первичного ключа

Быстрый ответ

Для удаления дубликатов из SQL-таблицы можно использовать CTE (общее табличное выражение) и функцию ROW_NUMBER(). Это позволит присвоить уникальный порядковый номер каждой строке в группе дубликатов:

WITH CTE_Dupes AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY (SELECT NULL)) AS RowNum
    FROM ваша_таблица
)
DELETE FROM CTE_Dupes WHERE RowNum > 1;

В этом примере col1, col2 нужно заменить на столбцы, которые определяют дубликаты, а ваша_таблица — на имя вашей конкретной таблицы. Таким образом, будет сохранено только по одному экземпляру каждого дубликата.

Добавление уникального идентификатора: временное решение

Если в таблице отсутствует первичный ключ, можно временно присвоить каждой строке уникальный идентификатор:

ALTER TABLE ваша_таблица ADD id INT IDENTITY(1,1);

После удаления дубликатов созданный столбец можно убрать:

ALTER TABLE ваша_таблица DROP COLUMN id;

Такой подход позволяет обеспечить сохранность валидных данных при удалении дубликатов.

Удаление дубликатов без изменения структуры

Если у вас нет возможности менять структуру таблицы, примените самоприсоединение (self-join) по столбцам, которые определяют дубликаты:

DELETE t1
FROM ваша_таблица t1
INNER JOIN ваша_таблица t2 
WHERE t1.столбец_дубликата = t2.столбец_дубликата
AND t1.уникальный_столбец > t2.уникальный_столбец;

В данном примере столбец_дубликата и уникальный_столбец необходимо заменить на соответствующие столбцы вашей таблицы.

Специфические стратегии для СУБД

SQL Server

В SQL Server эффективной является стратегия удаления дубликатов с помощью функции ROW_NUMBER():

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Столбец_дубликата ORDER BY Уникальный_столбец) AS rn
  FROM ваша_таблица
)
DELETE FROM cte WHERE rn > 1;

Подробнее об этом расскажет наш спикер на видео

PostgreSQL и Oracle

Для PostgreSQL и Oracle можно использовать CTID или ROWID:

DELETE FROM ваша_таблица
WHERE ctid NOT IN (
  SELECT min(ctid)
  FROM ваша_таблица
  GROUP BY Столбец_дубликата
);

MySQL

В MySQL наиболее удобно использовать временные таблицы:

CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM ваша_таблица
GROUP BY Столбец_дубликата;

TRUNCATE ваша_таблица;

INSERT INTO ваша_таблица SELECT * FROM temp_table;

Целостность данных

Не забывайте проверять на целостность собственные данные:

SELECT столбцы, COUNT(*)
FROM ваша_таблица
GROUP BY столбцы
HAVING COUNT(*) > 1;

Данный запрос подтвердит отсутствие дубликатов в таблице.

Визуализация

Прежде и после вычистки дубликатов:

DELETE t1
FROM класс t1
INNER JOIN класс t2 
WHERE   
t1.имя = t2.имя
AND t1.rowid > t2.rowid;

После этой операции таблица будет очищена от дубликатов.

Будьте внимательны при работе с большими объёмами данных

При обработке больших объёмов данных или при работе с взаимосвязанными таблицами необходимо проявлять особую осторожность. Для предупреждения повторного появления дубликатов используйте ограничения или уникальные индексы:

CREATE UNIQUE INDEX idx_uniq_col1_col2 ON ваша_таблица(col1, col2);

Это предотвращает создание новых дубликатов в данных.

Полезные материалы

1. DeleteDups – Stack Overflow — советы по удалению дубликатов.
2. DeleteDuplicate Oracle FAQ — подробная инструкция по работе с Oracle.
3. MySQL Remove Dupe Rows — руководство по удалению дубликатов в MySQL.
4. PostgreSQL Dupe Rows — способы работы с дублирующимися данными в PostgreSQL.
5. SQL temp tables — использование временных таблиц в SQL.