Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 9
Часть 2. Продуктовая аналитика
Глава 7. JOIN, NOT EXISTS. Пользователи без покупок
ОглавлениеОписание задачи
Представьте: утро понедельника. Менеджер по маркетингу подходит с просьбой – «Дай список пользователей, которые зарегистрировались, но ничего не купили. Будем отправлять промокоды».
Задача простая. Но если сделать её правильно – маркетологи перестанут спамить всем подряд и начнут работать точечно.
Что нужно выгрузить:
– user_id
– user_name
– registered_at
– country
Только тех, у кого ноль покупок.
Основные SQL-конструкции
Для решения нам пригодятся JOIN и IS NULL (эти команды уже разобраны в главе 4), а также новый оператор NOT EXISTS. Сейчас с ним познакомимся.
NOT EXISTS проверяет, что подзапрос не вернул ни одной строки.
text
SELECT columns
FROM table_a a
WHERE NOT EXISTS (
SELECT 1
FROM table_b b
WHERE b.key = a.key
);
Как это работает
Берём строку из table_a. Проверяем, есть ли её ключ (WHERE b.key = a.key) в table_b. Ничего не находим – оставляем. Если находим – не выводим (так работает, потому что есть NOT).
Запомните: NOT EXISTS проверяет отсутствие. Если подзапрос вернул хотя бы одну строку – строка из внешнего запроса не попадёт в результат.
SQL-запросы с объяснением
Способ 1. LEFT JOIN + IS NULL
text
SELECT
u.user_id,
u.user_name,
u.registered_at,
u.country
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o. order_id IS NULL;
Как это работает
Запрос берёт всех пользователей из users. Присоединяет к ним заказы из orders по user_id.
Если у пользователя нет заказов – все колонки из orders становятся NULL.
Условие WHERE o. order_id IS NULL оставляет только таких пользователей.
Почему проверяем именно order_id? Потому что это первичный ключ таблицы orders. Если он NULL – значит, строки из orders нет вообще.
Способ 2. NOT EXISTS (рекомендуется)
text
SELECT
u.user_id,
u.user_name,
u.registered_at,
u.country
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.user_id
);
Как это работает
Для каждого пользователя запрос проверяет: есть ли в orders хотя бы одна запись с таким user_id?
Если есть – пользователь исключается. Если нет – попадает в результат.
Обратите внимание на SELECT 1 внутри подзапроса. EXISTS не смотрит на содержимое строк, ему важно только их наличие. Поэтому SELECT 1 – стандартное соглашение, оно работает быстрее, чем SELECT *.
Запомните: NOT EXISTS читается как «таких, для которых не существует записей в…».
Способ 3. NOT IN (с осторожностью)
text
SELECT
user_id,
user_name,
registered_at,
country
FROM users
WHERE user_id NOT IN (
SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);
Как это работает
Подзапрос собирает список всех user_id, которые есть в orders. Основной запрос выбирает пользователей, чей user_id отсутствует в этом списке.
Важное предупреждение: Если в подзапросе встретится хотя бы один NULL – NOT IN вернёт пустой результат. Именно поэтому мы добавили WHERE user_id IS NOT NULL.
На заметку: NOT IN работает, но требует осторожности. На собеседованиях часто спрашивают про этот нюанс с NULL.
Вопросы и ответы
В: А какой способ быстрее?
О: Для больших таблиц – NOT EXISTS. Он оптимизирован для проверки наличия строк и не требует сборки полного списка user_id.
В: А что, если у пользователя заказ, но он отменён?
О: В текущих запросах такой пользователь не попадёт в результат – ведь запись в orders есть.
Если нужны только успешные заказы, добавьте условие в подзапрос:
text
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.user_id AND o.status = ’completed’/*завершенные*/
)
В: Почему результат пустой, хотя я знаю, что есть пользователи без заказов?
О: Три самые частые причины:
– В таблице orders есть строки с user_id = NULL (для NOT IN это фатально)
– Нарушены внешние ключи – в orders есть user_id, которого нет в users
– Опечатка в названии таблиц или колонок
Что в итоге
Что мы узнали в этой главе:
– LEFT JOIN + IS NULL – поняли, как работает присоединение с проверкой на пустоту
– NOT EXISTS – освоили самый эффективный способ поиска отсутствующих записей
– NOT IN – узнали про подводный камень с NULL
Теперь вы умеете находить пользователей, которые зарегистрировались, но не купили. Задача, которая встречается в работе постоянно.
Если всё сделали правильно – переходите к главе 8.
* Задание со звёздочкой
Напишите запрос, который находит пользователей, зарегистрировавшихся в 2024 году, но не сделавших ни одного заказа со статусом ’completed’. Используйте NOT EXISTS.
