JOIN и кардинальность: вопросы для собеседования (часть 3)
Что произойдёт при JOIN, если в правой таблице несколько строк на один ключ? Сколько строк вернёт LEFT JOIN, если совпадений нет? На собеседовании дают две таблицы и просят предсказать результат без выполнения запроса. Понимание кардинальности при JOIN — навык, который отличает аналитика, способного писать корректные запросы.
Вопросы 11–15 из 20
11Вы считаете «уникальные покупатели по бренду». Данные: `order_items(user_id, product_id)` и `products(product_id, brand)`. Пользователь может купить несколько товаров одного бренда. Какой расчёт на объединённых данных соответствует цели и устойчив к `duplication`?
A`COUNT(*)` по строкам после `JOIN`
B`SUM(user_id)` по строкам после `JOIN`
C`AVG(user_id)` по строкам после `JOIN`
D`COUNT(DISTINCT user_id)` по `brand` после `JOIN` `order_items` → `products`
Ответ: Для «уникальных покупателей» нужно считать `distinct` по `user_id`, иначе `one-to-many` покупки создадут `duplication` строк.
После `JOIN` одна покупка равна одной строке, но один пользователь может сделать много покупок в одном бренде. `COUNT(*)` покажет количество строк, а не пользователей. `COUNT(DISTINCT user_id)` по `brand` соответствует бизнес-вопросу и защищает метрику от раздувания при росте количества позиций.
12Нужен датасет на уровне `user_id`: выручка из `orders` и число сессий из `sessions`. В обеих таблицах по пользователю много строк (`one-to-many`). Какой подход минимизирует риск `join explosion`?
AСделать `JOIN` `orders` и `sessions` по `user_id`, а затем посчитать `SUM()` и `COUNT(*)`
BСделать `JOIN`, а потом применить `distinct` ко всем колонкам
CУдалить все строки кроме первой в `orders`, чтобы стало `one-to-one`
DСначала `pre-aggregate` `orders` до 1 строки на `user_id` и отдельно `pre-aggregate` `sessions`, затем соединить агрегаты
Ответ: Для метрик на уровне пользователя сначала делают `pre-aggregate` источников до нужной `cardinality`, и только потом соединяют.
Если соединить «сырые» `orders` и `sessions`, получится `many-to-many` по `user_id` и вы получите `duplication` строк. В результате денежные и счётные метрики будут завышены. Предварительная агрегация до одной строки на пользователя сохраняет правильный уровень данных и делает соединение ближе к `one-to-one`.
13У товара может быть несколько категорий в таблице `product_categories(product_id, category_id)`, а продажи лежат в `sales_lines(product_id, revenue)` (много строк на товар). Вы посчитали выручку по категориям после соединения. Что будет, если потом сложить выручку всех категорий в одну цифру?
AСумма будет равна общей выручке, потому что `SUM()` автоматически убирает `duplication`
BСумма будет меньше общей выручки, потому что категории фильтруют строки
CСумма может стать больше общей выручки, потому что `many-to-many` связь создаёт `duplication` выручки по товарам в нескольких категориях
DСумма станет равна числу товаров из-за `COUNT(*)`
Ответ: При `many-to-many` атрибуции один факт может попасть в несколько групп, и суммарные `SUM()` по группам перестают сходиться.
Если товар принадлежит двум категориям, его `revenue` попадёт в обе группы после `JOIN` и будет учтён дважды. Это не всегда «ошибка», но тогда нельзя ожидать, что сумма по категориям совпадёт с общей. Чтобы контролировать это, нужно заранее определить правило распределения и учитывать риск `duplication`.
14В `payments(order_id, amount, status)` для одного `order_id` может быть несколько попыток оплаты (`one-to-many`), например повторные списания или ошибки. Вы хотите посчитать выручку по заказам из `orders`. Что наиболее безопасно сделать перед соединением, чтобы `SUM()` не завысилась из-за `duplication`?
AСоединить как есть и потом применить `distinct` ко всем строкам
BСначала `pre-aggregate` `payments` до одной строки на `order_id`, затем соединить с `orders`
CСоединить `payments` с `orders` по `user_id`, так будет больше совпадений
DЗаменить `SUM()` на `COUNT(*)`
Ответ: При `one-to-many` по `order_id` деньги нужно сводить до одного факта на заказ через `pre-aggregate`, иначе появится `duplication` в `SUM()`.
Если у заказа несколько платежных строк, то `orders` начнёт дублироваться при `JOIN`, и каждая попытка попадёт в расчёт. В зависимости от бизнес-логики нужно выбрать правило: только успешные платежи, последний успешный или сумма успешных. После `pre-aggregate` соединение становится ближе к `one-to-one`, и денежные метрики становятся интерпретируемыми.
15Вы соединили `users` и `orders` (`one-to-many`) и посчитали `AVG(order_amount)`, интерпретируя это как «средняя выручка на пользователя». Почему это неверно и как правильно?
AНеверно, потому что `AVG()` считает только целые числа; правильно использовать `SUM()`
BВерно: `AVG(order_amount)` всегда равно выручке на пользователя
CНеверно, потому что `INNER JOIN` удаляет пользователей; нужно `LEFT JOIN` и тогда всё станет правильно
DНеверно, потому что `AVG()` считает среднее по заказам, а не по пользователям; нужно сначала `pre-aggregate` выручку до `user_id`, а затем взять `AVG()` по пользователям
Ответ: В `one-to-many` `JOIN` `AVG()` по полю заказа даёт среднее по заказам, а не по пользователям из-за разного `cardinality`.
Пользователь с 10 заказами будет «весить» в среднем в 10 раз больше пользователя с одним заказом, поэтому получается среднее по строкам заказов. Если цель — метрика на пользователя, сначала посчитайте выручку на `user_id` через `SUM()` по заказам, а уже потом усредняйте. Это базовое правило выбора уровня агрегации перед и после `JOIN`.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram