Удалите из таблицы подписчиков дубли по email, оставив запись с наименьшим id
Проверила команда Сопровод · Обновлено
Короткий ответ
Соедините таблицу саму с собой в DELETE … USING по email и удаляйте строку, если у нее есть пара с меньшим id: a.id > b.id. У самой ранней записи такой пары нет, и она остается. Перед удалением прогоните то же условие в SELECT и сверьте число строк. После чистки добавьте уникальный индекс, чтобы дубли не вернулись.
Почему этот вопрос задают
Дубли появляются в любой базе после импорта или ошибки в форме, а неудачный DELETE после COMMIT уже не откатить. Здесь видно, умеете ли вы удалять строки через соединение и проверять условие до запуска.
Условие задачи
Онлайн-школа английского хранит подписчиков рассылки в таблице subscribers(id, email). После переноса из двух форм часть адресов записалась несколько раз: anna@mail.test трижды, boris@mail.test дважды. Удалите дубли так, чтобы у каждого адреса осталась одна строка с наименьшим id. Затем выведите оставшиеся строки по возрастанию id.
Как подойти к решению
- Уточните, какую строку считать оригиналом: в условии это запись с наименьшим
id - Добавьте вторую копию таблицы в
USINGи свяжите копии вWHEREпоemail - Удаляйте строку, только если у ее пары
idменьше:a.id > b.id - Прогоните то же условие в
SELECTи убедитесь, что уйдут три строки
Решение
Строка уходит, если в таблице есть другая с тем же адресом и меньшим id. У оригинала такой пары нет. Строка 7 находит две пары, 1 и 3, но удаляется один раз. Сравнение NULL = NULL не дает истину, поэтому подписчиков без адреса запрос не тронет. На большой таблице сначала создайте индекс по email.
DELETE FROM subscribers a
USING subscribers b
WHERE a.email = b.email
AND a.id > b.id;
SELECT id, email
FROM subscribers
ORDER BY id;id | email ----+------------------ 1 | anna@mail.test 2 | boris@mail.test 4 | vera@mail.test 5 | gleb@mail.test 8 | dina@mail.test 9 | egor@mail.test 10 | zhanna@mail.test (7 rows)
Что спросят следом
Как удалить те же дубли через
ROW_NUMBER?Адреса
Anna@Mail.testиanna@mail.testсчитать дублем?В таблице 50 млн строк, и база работает под нагрузкой. Как будете удалять?
Доступно по подписке
Ответы на эти вопросы
Частые ошибки
«Удалю строки, у которых есть пара с тем же адресом:
a.id <> b.id»Условие
<>верно и для оригинала: у строки 1 тоже есть пара. Так уйдут все копии вместе с первой. Пишитеa.id > b.id, тогда пара найдется только у поздних строк«Перенесу уникальные строки через
SELECT DISTINCTв новую таблицу»У дублей разные
id, иDISTINCTпо всей строке их не склеит. Если пересобираете таблицу, беритеDISTINCT ON (email)сORDER BY email, idи помните про индексы и внешние ключи«Запущу
DELETEи посмотрю, сколько строк удалилось»Сначала выполните то же условие в
SELECTили удалите внутри транзакции сRETURNINGи проверьте строки доCOMMIT. Удаление без проверки выглядит как риск для рабочей базы«Оставлю
min(id)каждой группы, остальное удалю черезNOT IN»Запрос рабочий, но
GROUP BYскладывает все пустые адреса в одну группу, и из подписчиков без email останется один. ИсключитеNULLявно или возьмите вариант сUSING
Сигналы для интервьюера
Сильный ответ
Спрашивает, ссылаются ли на подписчиков другие таблицы, и предлагает до удаления перевесить эти ссылки на оригинал
Тревожный сигнал
Теряется, когда интервьюер просит оставить самую свежую запись вместо самой ранней, и переписывает запрос с нуля
Красный флаг
Предлагает удалять копии вручную по списку id из выгрузки и не думает, откуда они берутся
Как еще могут спросить
- Как удалить повторяющиеся строки из таблицы и оставить одну из них?
- Напишите
DELETE, который уберет дубликаты по одному столбцу - Как в PostgreSQL почистить таблицу от дублей, сохранив самую раннюю запись?
- Как оставить по одной записи у подписчиков, которые записались дважды?











