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

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

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

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

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

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT
);
INSERT INTO users (email) VALUES
    ('a@x.ru'), ('b@x.ru'), ('a@x.ru'), ('c@x.ru'), ('a@x.ru'), ('b@x.ru');

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

Вывод:

a@x.ru|3
b@x.ru|2

Дубликаты: a@x.ru встречается 3 раза, b@x.ru — 2 раза. c@x.ru уникален и в результат не попал, потому что HAVING COUNT(*) > 1 его отсёк.

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

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

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users (email) VALUES
    ('a@x.ru'), ('b@x.ru'), ('a@x.ru'), ('c@x.ru'), ('a@x.ru');

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

Вывод:

1|a@x.ru|1
3|a@x.ru|2
5|a@x.ru|3
2|b@x.ru|1
4|c@x.ru|1

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

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

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

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users (email) VALUES
    ('a@x.ru'), ('b@x.ru'), ('a@x.ru'), ('c@x.ru'), ('a@x.ru'), ('b@x.ru');

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

SELECT id, email FROM users ORDER BY id;

Вывод:

1|a@x.ru
2|b@x.ru
4|c@x.ru

Осталось ровно по одной строке на email — с наименьшим id: a@x.ru (id 1), b@x.ru (id 2), c@x.ru (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-индекс.