Удаление дубликатов строк в SQLite

Проблема дублирующихся записей в базе данных SQLite встречается часто: при импорте данных, повторных вставках или ошибках в ETL-процессах. Встроенной команды DELETE DUPLICATES в SQLite нет, но существуют эффективные способы очистки таблиц. Разберём основные методы удаления дубликатов построчно.

Как найти дубликаты в SQLite?

Прежде чем удалять, нужно определить критерии дублирования. Чаще всего дубликатами считаются строки, у которых совпадают значения в одном или нескольких столбцах (кроме первичного ключа). Для поиска используйте GROUP BY и HAVING COUNT(*) > 1.

SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1;

Способ 1: Удаление дубликатов с помощью ROWID

Если в таблице нет явного первичного ключа, SQLite автоматически создаёт скрытый столбец ROWID. Это уникальный идентификатор каждой строки. Метод подходит, когда нужно оставить одну запись из группы дубликатов (например, с минимальным ROWID).

DELETE FROM table_name WHERE rowid NOT IN (SELECT MIN(rowid) FROM table_name GROUP BY column1, column2);

Этот запрос удаляет все строки, кроме одной с наименьшим ROWID для каждой группы дубликатов. Замените column1, column2 на столбцы, по которым определяете уникальность.

Способ 2: Использование временной таблицы

Более надёжный метод - создать временную таблицу с уникальными записями, затем переименовать её. Это особенно полезно, если нужно сохранить порядок или выполнить сложную дедупликацию.

CREATE TABLE temp_table AS SELECT DISTINCT * FROM original_table; DROP TABLE original_table; ALTER TABLE temp_table RENAME TO original_table;

Важно: после переименования все индексы и триггеры придётся создать заново. Если таблица большая, процесс может занять время, но он гарантирует полное удаление дубликатов.

Способ 3: Дедупликация с агрегатными функциями

Если нужно не просто удалить дубликаты, а объединить данные (например, взять максимальное значение из дублирующихся строк), используйте GROUP BY с агрегатными функциями (MAX, MIN, SUM). Затем результат вставляется в новую таблицу.

CREATE TABLE clean_table AS SELECT id, MAX(price) AS price, name FROM dirty_table GROUP BY id, name;

Этот подход часто применяется при очистке логов или финансовых данных, где важна последняя версия записи.

Советы по профилактике дубликатов

• Добавьте UNIQUE-индекс на столбцы, которые должны быть уникальными: CREATE UNIQUE INDEX idx_unique ON table_name(col1, col2);
• Используйте INSERT OR IGNORE или INSERT OR REPLACE для предотвращения вставки дублей.
• Регулярно проверяйте данные с помощью скриптов на GROUP BY.

Заключение

Хотя в SQLite нет одной команды «удалить дубликаты», комбинация методов с ROWID, временными таблицами и агрегатными функциями позволяет эффективно очистить любую таблицу. Выбирайте способ в зависимости от структуры данных и требований к производительности. Для больших баз данных рекомендуется сначала протестировать запрос на копии.