JOIN и кардинальность: вопросы для собеседования (часть 4)
Что произойдёт при JOIN, если в правой таблице несколько строк на один ключ? Сколько строк вернёт LEFT JOIN, если совпадений нет? На собеседовании дают две таблицы и просят предсказать результат без выполнения запроса. Понимание кардинальности при JOIN — навык, который отличает аналитика, способного писать корректные запросы.
Вопросы 16–20 из 20
16У пользователя может быть несколько заказов в `orders` и несколько возвратов в `refunds`. Вы соединили `orders` и `refunds` по `user_id` и посчитали `SUM(refund_amount)`. Что наиболее вероятно произойдёт с суммой и почему?
AСумма не изменится, потому что `SUM()` устойчив к повторам строк и сам убирает дублирование значений по ключу `user_id`.
BСумма станет меньше, потому что `INNER JOIN` удалит часть возвратов у пользователей, у которых на одного пользователя приходится несколько заказов.
CСумма завысится: получится связь «многие ко многим» по `user_id`, и каждый возврат повторится для каждого заказа этого пользователя.
DСумма станет равна числу пользователей, потому что `COUNT(*)` и `SUM()` после `JOIN` по `user_id` дают эквивалентный результат.
Ответ: Когда обе стороны имеют несколько строк на `user_id`, `JOIN` становится «многие ко многим» и раздувает `SUM()` из-за повторов.
Внутри одного пользователя заказы и возвраты комбинируются, образуя пары «каждый с каждым». В результате один и тот же возврат попадает в несколько строк и учитывается несколько раз в `SUM(refund_amount)`. Чтобы исправить, нужно предварительно агрегировать одну из сторон до `user_id` либо соединять по более точному ключу, например `order_id`. Это классическая ошибка раздувания при `JOIN` по неполному ключу.
Подробный разбор → 17Когда предварительная агрегация до соединения таблиц является ошибкой? Вы хотите посчитать выручку по категории товара, имея order_items(order_id, product_id, item_revenue) и products(product_id, category).
AНи в одном случае: предварительная агрегация до JOIN ускоряет запрос и сохраняет верный результат при любой схеме
BКогда таблица products мала и помещается в оперативную память воркера при выполнении JOIN на больших данных
CКогда целевой уровень измерения это позиция заказа, и агрегировать по category нужно уже после соединения таблиц
DКогда в колонках item_revenue или category встречаются значения NULL, что может исказить итоговую сумму выручки
Ответ: Если целевая метрика на уровне позиции, `pre-aggregate` до `JOIN` может уничтожить нужную детализацию.
Категория — атрибут товара, поэтому без `JOIN` `order_items` с `products` вы не знаете, к какой категории относится выручка. Если заранее свернуть до уровня `order_id`, вы потеряете разрез по товарам и категориям. В таких задачах правильнее агрегировать после `JOIN` на нужном уровне, контролируя `cardinality` и риск `duplication`.
Подробный разбор → 18После JOIN метрика стала завышенной, и аналитик добавил DISTINCT ко всей таблице, чтобы убрать дубли. Почему это рискованный подход?
ADISTINCT замедляет выполнение запроса и не даёт использовать индекс, из-за чего база переключается на full scan по таблицам
BDISTINCT удаляет строки без проверки внешних ключей, что может нарушить ссылочную целостность итогового набора данных
CDISTINCT маскирует причину раздувания строк после JOIN и может убрать настоящие повторы в данных, занижая метрику
DDISTINCT не работает с агрегатными функциями SUM и COUNT и поэтому не способен повлиять на завышенный результат метрики
Ответ: DISTINCT поверх результата JOIN убирает симптом, а не причину — раздувание строк из-за связи многие-ко-многим. Корректный подход — дедуплицировать или агрегировать до соединения.
После JOIN с дублированием строк DISTINCT может схлопнуть как лишние комбинации, так и легитимные повторы (например, два одинаковых товара в одном заказе), что искажает метрику в обе стороны. Главная проблема — потеря понимания, на каком уровне детализации (order, item, user) считается метрика. Правильный путь: pre-aggregate до уровня нужной сущности или явно выбрать ключ группировки и считать SUM/COUNT по нему. Производительность тут вторична.
Подробный разбор → 19Хотите посчитать конверсию «пользователь посмотрел товар → пользователь купил» по `user_id`. Данные: таблица событий (много просмотров на пользователя) и таблица заказов (много заказов на пользователя). Что корректнее всего сделать, чтобы избежать искажения «многие-ко-многим»?
AСоединить таблицу событий и таблицу заказов по `user_id` и посчитать `COUNT(*)` от полученных строк
BСделать `DISTINCT` только по таблице событий, а заказы оставить как есть и соединить с просмотрами
CСначала свернуть оба источника до одного флага на пользователя, затем соединить их и считать конверсию
DЗаменить `SUM()` на `AVG()` в финальном запросе, чтобы среднее по группам нивелировало размножение строк
Ответ: Для конверсии на уровне пользователя нужно сначала свернуть события и заказы до одного факта на `user_id`, иначе соединение «многие-ко-многим» создаст дубликаты.
Если соединить сырые события и заказы, пользователь с 10 просмотрами и 2 заказами даст 20 строк — это размножение строк после соединения. Тогда и числитель, и знаменатель могут стать бессмысленными, а эффект — искусственным. Превратите каждый источник в один факт на пользователя, и только затем считайте конверсию.
Подробный разбор → 20Вы строите выручку по каналу: соединяете `sessions(user_id, channel)` и `orders(user_id, revenue)` по `user_id`, затем считаете `SUM(revenue)` по `channel`. Получившаяся выручка сильно больше бухгалтерской. Что наиболее вероятно и что делать?
AЭто many-to-many дублирование строк: выручка размножается на сессиях, нужна предварительная агрегация до уровня заказа
BЗаменить SUM(revenue) на AVG(revenue) для корректировки дублей выручки на сессиях пользователя в канале
CПрименить SELECT DISTINCT ко всему результирующему набору и пересчитать выручку поверх дедуплицированных строк
DПерейти на COUNT(*) вместо SUM(revenue) для устранения эффекта дублирования строк после соединения таблиц
Ответ: Соединение двух `one-to-many` источников по `user_id` даёт `many-to-many` и ломает денежные метрики из-за `duplication`.
Каждый заказ пользователя матчится на каждую его сессию, поэтому один и тот же `revenue` учитывается много раз в `SUM()`. `distinct` может случайно скрыть часть дублей и сломать данные по-другому, поэтому это плохой костыль. Правильный путь — определить целевой уровень данных, `pre-aggregate` до него и затем соединять.
Подробный разбор →