JOIN и операции множеств: вопросы для собеседования (часть 7)
JOIN-ы — фундамент аналитического SQL. INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN, а также UNION, INTERSECT, EXCEPT — на собеседовании нужно не просто знать синтаксис, но и понимать, что происходит при дубликатах ключей и NULL-значениях. Задачи на JOIN встречаются на каждом собеседовании без исключения.
Вопросы 31–35 из 40
31В таблице `events(user_id, event_name)` один пользователь может иметь много событий каждого типа. Нужно получить список пользователей, у которых есть и событие установки, и событие покупки, при этом каждый пользователь должен быть в результате один раз. Какой запрос лучше подходит?
A`SELECT e1.user_id FROM events e1 INNER JOIN events e2 ON e1.user_id = e2.user_id WHERE e1.event_name = 'install' AND e2.event_name = 'purchase'`
B`SELECT user_id FROM events WHERE event_name IN ('install','purchase') GROUP BY user_id`
C`SELECT user_id FROM events WHERE event_name = 'install' INTERSECT SELECT user_id FROM events WHERE event_name = 'purchase'`
D`SELECT user_id FROM events WHERE event_name = 'install' UNION ALL SELECT user_id FROM events WHERE event_name = 'purchase'`
Ответ: `INTERSECT` вернёт пересечение пользователей и не создаст мультипликацию строк, как self-`JOIN`.
Соединение `events` с самим собой по `user_id` сильно размножает строки: если у пользователя 3 установки и 5 покупок, вы получите 15 строк. Это можно править через `DISTINCT`, но проще выразить задачу на уровне множеств: взять пользователей с установкой и пересечь с пользователями с покупкой через `INTERSECT`. Так вы сразу получите уникальный список пользователей, прошедших оба шага воронки. Вариант с `IN` и группировкой выберет всех, кто сделал хотя бы одно событие любого из двух типов, а не пересечение, а `UNION ALL` просто склеит два списка с дублями.
Подробный разбор → 32Нужно сгенерировать матрицу A/B-тестов: 4 варианта заголовка × 3 варианта кнопки × 2 цвета фона. Каждый параметр хранится в отдельной таблице. Как получить все 24 комбинации?
AИспользовать `INNER JOIN` без условия `ON`: соединение создаст все возможные комбинации строк трёх таблиц
BИспользовать `UNION ALL` между таблицами параметров: вертикальное объединение строк в один общий набор
CИспользовать `FULL OUTER JOIN` без условия `ON`: соединение создаёт комбинации с обработкой `NULL` по обеим сторонам
DИспользовать `CROSS JOIN` между таблицами параметров: соединение явно создаст все возможные комбинации строк
Ответ: `CROSS JOIN` двух и более таблиц генерирует полное декартово произведение: 4 × 3 × 2 = 24 строки.
Цепочка `CROSS JOIN`: `SELECT * FROM titles CROSS JOIN buttons CROSS JOIN colors` создаст 24 строки — все комбинации параметров. Это законное применение декартова произведения: количество результатов контролируемо и нужно именно все сочетания. `INNER JOIN` без `ON` тоже даёт декартово произведение, но `CROSS JOIN` явно показывает намерение и читается лучше. `UNION ALL` складывает строки вертикально, а `FULL OUTER JOIN` без условия объединяет строки, а не перемножает их.
Подробный разбор → 33Вы соединяете агрегаты `daily_orders(day, platform, orders)` и `daily_sessions(day, platform, sessions)` для расчёта конверсии. Сделали `JOIN` только по `day` и заметили странные значения. В чём типичная ошибка?
AВ `JOIN` нельзя использовать колонку `day`, поэтому соединять следует по `id` и фильтровать дату отдельно
BПроизошло умножение строк: каждый `day` соединил все значения `platform` между собой, в условии не хватает `platform`
CИз-за `JOIN` пропали строки и нужен `FULL JOIN`, чтобы вернуть все комбинации дат и платформ
DНичего страшного: `JOIN` сам сопоставит правильные значения `platform` по совпадающему дню
Ответ: Если не включить все ключи (например, `day` и `platform`) в условие `ON`, агрегаты могут соединиться «крест-накрест» и раздуть метрики.
Обе таблицы агрегированы по двум измерениям: `day` и `platform`. Если соединить только по `day`, то строки разных платформ внутри одного дня начнут сочетаться друг с другом, создавая лишние комбинации и искажая значения `orders` и `sessions`. Правильное условие должно включать оба поля ключа, например `ON o.day = s.day AND o.platform = s.platform`.
Подробный разбор → 34Нужно получить одну строку на пользователя: `users(user_id)` плюс время последнего события из `events(user_id, created_at)`. Что лучше всего сделать, чтобы `JOIN` не вернул несколько строк на одного пользователя?
AСвернуть `events` через `MAX(created_at)` и `GROUP BY user_id`, затем `LEFT JOIN` к `users`
BСделать обычный `LEFT JOIN` `users` к `events` по `user_id` без агрегации правой таблицы
CЗаменить `JOIN` на `UNION` по `user_id` для схлопывания повторов в одну запись на пользователя
DПрименить `FULL JOIN` `users` и `events` по `user_id` для устранения дублей по ключу
Ответ: Если справа несколько событий на пользователя, обычный `JOIN` даст дубли; типичный приём — заранее агрегировать правую таблицу до нужного уровня.
Таблица `events` обычно содержит много строк на одного пользователя. При обычном `JOIN` каждая из них соединится с одной строкой из `users`, и вы получите дубликаты пользователей. Чтобы сохранить гранулярность «одна строка на пользователя», сначала посчитайте последнее время события на уровне `user_id` через `MAX(created_at)` с `GROUP BY user_id`, а затем соединяйте с `users`. `UNION` вообще не делает соединение, а `FULL JOIN` сам по себе дубли не убирает — поэтому эти варианты неверны.
Подробный разбор → 35Нужно посчитать количество уникальных пользователей, которые совершили событие либо в `events_web(user_id)`, либо в `events_app(user_id)`. Какой запрос посчитает корректно, если один и тот же пользователь мог быть в обоих источниках?
A`SELECT COUNT(*) FROM (SELECT user_id FROM events_web UNION ALL SELECT user_id FROM events_app) t`
B`SELECT COUNT(*) FROM (SELECT user_id FROM events_web UNION SELECT user_id FROM events_app) t`
C`SELECT COUNT(*) FROM events_web w INNER JOIN events_app a ON w.user_id = a.user_id`
D`SELECT COUNT(*) FROM (SELECT user_id FROM events_web INTERSECT SELECT user_id FROM events_app) t`
Ответ: `UNION` объединяет наборы и сразу убирает дубли — это даёт количество уникальных пользователей хотя бы в одном источнике.
Если использовать объединение со всеми повторами (`UNION ALL`), пользователь, встретившийся в обоих источниках, попадёт в результат дважды и будет посчитан два раза. `UNION` возвращает объединение множеств без дублей, поэтому `COUNT(*)` по результату даст количество уникальных пользователей, которые были хотя бы в одном источнике. `INTERSECT` посчитает только тех, кто был в обоих, а `INNER JOIN` — пары совпадений, что не равно искомому числу.
Подробный разбор →