Дубликаты строк: найти и удалить

Найти дубликаты строк, посчитать их и удалить лишние, оставив по одной копии.

Дубликаты — строки с одинаковыми значениями в значимых колонках. Сначала их находят группировкой, потом удаляют, сохраняя одну копию.

Найти дубликаты: GROUP BY + HAVING

Группируем по колонкам, которые определяют «одинаковость», и оставляем группы, где строк больше одной. Это базовый приём — он же отвечает на вопрос «как найти повторяющиеся email».

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT
);
INSERT INTO users (email) VALUES
    ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]');

SELECT email, COUNT(*) AS skolko
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY skolko DESC, email;

Вывод:

[email protected]|3
[email protected]|2

Дубликаты: [email protected] встречается 3 раза, [email protected] — 2 раза. [email protected] уникален и в результат не попал, потому что HAVING COUNT(*) > 1 его отсёк.

Какие строки лишние: ROW_NUMBER

Чтобы удалить дубли, надо решить, какую строку оставить. Обычно — с минимальным id. Помечаем строки номером внутри группы дубликатов; всё, что с номером > 1 — лишнее:

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users (email) VALUES
    ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]');

SELECT id, email,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS n
FROM users
ORDER BY email, id;

Вывод:

1|[email protected]|1
3|[email protected]|2
5|[email protected]|3
2|[email protected]|1
4|[email protected]|1

Внутри каждой группы email строки пронумерованы по id. Первая копия — n = 1 (её оставляем), остальные (n = 2, 3) — на удаление. Для [email protected] это строки с n=2 и n=3.

Удаление лишних копий

Теперь удалим всё, что не является «первой» копией. Удаляем строки, чей id не минимален в своей группе:

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users (email) VALUES
    ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]'), ('[email protected]');

DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

SELECT id, email FROM users ORDER BY id;

Вывод:

1|[email protected]
2|[email protected]
4|[email protected]

Осталось ровно по одной строке на email — с наименьшим id: [email protected] (id 1), [email protected] (id 2), [email protected] (id 4). Подзапрос MIN(id) GROUP BY email — это «id, которые надо сохранить», остальное удалено.

Как не допустить дублей впредь

После чистки добавляют UNIQUE-ограничение на колонку (CREATE UNIQUE INDEX или UNIQUE в схеме) — тогда СУБД сама не даст вставить повтор. Это правильный ответ на вопрос «как предотвратить дубликаты».

Итог

• Найти дубли: GROUP BY значимые_колонки HAVING COUNT(*) > 1.
• Пометить лишние: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY id), лишние — где номер > 1.
• Удалить: оставить MIN(id) в группе; предотвратить повтор — UNIQUE-индекс.