Удалите из таблицы подписчиков дубли по email, оставив запись с наименьшим id

ЗадачаMiddleИногда

Проверила команда СопроводОбновлено

Короткий ответ

Соедините таблицу саму с собой в DELETE … USING по email и удаляйте строку, если у нее есть пара с меньшим id: a.id > b.id. У самой ранней записи такой пары нет, и она остается. Перед удалением прогоните то же условие в SELECT и сверьте число строк. После чистки добавьте уникальный индекс, чтобы дубли не вернулись.

Почему этот вопрос задают

Дубли появляются в любой базе после импорта или ошибки в форме, а неудачный DELETE после COMMIT уже не откатить. Здесь видно, умеете ли вы удалять строки через соединение и проверять условие до запуска.

Условие задачи

Онлайн-школа английского хранит подписчиков рассылки в таблице subscribers(id, email). После переноса из двух форм часть адресов записалась несколько раз: anna@mail.test трижды, boris@mail.test дважды. Удалите дубли так, чтобы у каждого адреса осталась одна строка с наименьшим id. Затем выведите оставшиеся строки по возрастанию id.

idemail
1anna@mail.test
2boris@mail.test
3anna@mail.test
4vera@mail.test
5gleb@mail.test
6boris@mail.test
7anna@mail.test
8dina@mail.test
9egor@mail.test
10zhanna@mail.test

Как подойти к решению

  1. Уточните, какую строку считать оригиналом: в условии это запись с наименьшим id
  2. Добавьте вторую копию таблицы в USING и свяжите копии в WHERE по email
  3. Удаляйте строку, только если у ее пары id меньше: a.id > b.id
  4. Прогоните то же условие в SELECT и убедитесь, что уйдут три строки

Решение

Строка уходит, если в таблице есть другая с тем же адресом и меньшим id. У оригинала такой пары нет. Строка 7 находит две пары, 1 и 3, но удаляется один раз. Сравнение NULL = NULL не дает истину, поэтому подписчиков без адреса запрос не тронет. На большой таблице сначала создайте индекс по email.

SQL
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 почистить таблицу от дублей, сохранив самую раннюю запись?
  • Как оставить по одной записи у подписчиков, которые записались дважды?

Похожие вопросы

ЗадачаJuniorЧасто

Найдите email, которые встречаются в таблице пользователей больше одного раза

Сгруппируйте строки по email и оставьте группы, где строк больше одной: GROUP BY email HAVING count(*) > 1. Условие на count(*) пишется в HAVING, потому что WHERE работает до группировки и агрегатов еще не видит. Выведите рядом число повторов, чтобы было видно, насколько велика проблема.

  • GROUP BY
  • HAVING
  • дубликаты
260
ЗнаниеJuniorЧасто

Чем DELETE отличается от TRUNCATE и DROP?

DELETE удаляет строки, в том числе по условию WHERE, запускает триггеры и не трогает счетчик identity. TRUNCATE очищает таблицу целиком, без построчного прохода, и сразу освобождает место. DROP TABLE удаляет саму таблицу вместе с индексами и ограничениями. В PostgreSQL все три команды откатываются, пока транзакция не зафиксирована.

  • DELETE
  • TRUNCATE
  • DROP
239
ЗнаниеJuniorЧасто

Чем ROW_NUMBER отличается от RANK и DENSE_RANK?

Все три функции нумеруют строки окна в порядке из ORDER BY, а различаются на равных значениях. ROW_NUMBER дает каждой строке свой номер, даже при равенстве. RANK дает равным строкам одинаковый номер и пропускает следующие: 1, 2, 2, 4. DENSE_RANK тоже дает одинаковый номер, но без пропусков: 1, 2, 2, 3.

  • оконные функции
239
ЗнаниеJuniorЧасто

Что такое индекс и почему не стоит создавать его на каждый столбец?

Индекс — отдельная структура, в которой значения столбца хранятся упорядоченно вместе с адресами строк. По умолчанию в PostgreSQL это B-дерево: поиск по нему проходит несколько шагов вместо чтения всей таблицы. Индекс на каждый столбец вредит: вставка пишет во все индексы, обновление индексированного столбца тоже, индексы занимают место, а для условия на большую долю строк планировщик индекс даже не возьмет.

  • индексы
  • PostgreSQL
275

Частые вопросы по теме

Короткие ответы на частые вопросы по этой теме.

Напишите один способ целиком и назовите второй. DELETE … USING короткий и работает в PostgreSQL. Вариант с ROW_NUMBER переносится в другие базы и позволяет выбрать оригинал по любому правилу. Интервьюеру важнее, чтобы вы объяснили, какая строка остается и почему.

Писать не обязательно, скажите это вслух: на рабочей базе удаление идет внутри транзакции, число строк проверяют через RETURNING и только потом делают COMMIT. От middle ждут такой привычки, ведь восстановить данные из копии намного дороже.

Создайте таблицу на 10 строк с дублями и двумя строками без адреса. Удалите дубли тремя способами: USING, ROW_NUMBER и NOT IN с min(id). Посмотрите, какие способы задели строки без адреса, и объясните себе почему.

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

Расскажите, какие задачи хотите решать через несколько лет и чему для этого научитесь. Свяжите план с тем, что дает эта компания, и покажите первый шаг, который уже сделали. Точную должность называть не обязательно. Важнее, чтобы план был реальным и вел через эту работу, а не мимо нее.

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

Назовите одно достижение, близкое к задачам новой работы, и расскажите его как историю: какая была проблема, что сделали лично вы и что изменилось в цифрах. Сильнее всего звучит результат, который держится и после вас: новый порядок работы, правило, схема. Если результат общий, прямо скажите, какая часть в нем ваша.

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

Начните с того, кто вы сейчас и на какую работу пришли. Вместо стажа расскажите об учебе, практике и подработке, которые похожи на задачи вакансии, и назовите один результат с цифрой. Закончите тем, почему хотите начать карьеру именно здесь. Уложитесь в одну-две минуты и не пересказывайте резюме по порядку.

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

Назовите свое правило: что вы отдаете команде и что оставляете себе, и покажите его на одной задаче. Расскажите, кому и почему передали, какие полномочия и рамки дали, как проверяли без мелкого контроля. Закончите честным итогом: что получилось хуже или медленнее, чем у вас, и что вы выиграли.

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

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

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

Возьмите одного человека и покажите путь: каким он пришел, какие задачи вы ему давали по нарастающей и чем помогали, кем он стал и за какой срок. Назовите цифру роста: новая должность, своя зона, люди в подчинении. Скажите честно, где он вырос сам, а где сработали ваши решения.

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

Назовите цель с цифрой и честный итог. Главное в ответе — момент, когда вы поняли, что цель срывается, и по какой цифре это увидели. Дальше расскажите, кому и когда сказали, что пересобрали, чтобы спасти главное, и какой один вывод сделали для следующего плана.

  • цели
  • провал
241
Общий вопросРуководительЧасто

Как вы измеряете результат своей команды?

Назовите два-три показателя, которые отражают то, ради чего существует ваша команда, и их текущие цифры. Добавьте показатель качества, который не дает гнаться за одним числом. Скажите, как часто смотрите на цифры, кто их видит и какое решение вы приняли по ним в последний раз.

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

Возьмите одну цель компании с цифрой и сроком и покажите, как вы ее разбили: на какие части, по какому признаку, какие личные цели получил каждый сотрудник. Расскажите, как следили за ходом и что меняли по дороге. Закончите итогом план-факт честно, даже если цель выполнена не целиком.

  • цели
  • планирование
227