JOIN и кардинальность: вопросы для собеседования (часть 3)
Что произойдёт при JOIN, если в правой таблице несколько строк на один ключ? Сколько строк вернёт LEFT JOIN, если совпадений нет? На собеседовании дают две таблицы и просят предсказать результат без выполнения запроса. Понимание кардинальности при JOIN — навык, который отличает аналитика, способного писать корректные запросы.
Вопросы 11–15 из 20
11Вы считаете «уникальные покупатели по бренду». Данные: `order_items(user_id, product_id)` и `products(product_id, brand)`. Один пользователь может купить несколько товаров одного бренда. Какой расчёт на объединённых данных соответствует цели и устойчив к дублированию строк?
ACOUNT(DISTINCT user_id) с GROUP BY brand после соединения order_items и products по product_id
BCOUNT(*) после соединения order_items и products с GROUP BY brand и фильтром по статусу заказа
CCOUNT(DISTINCT product_id) после соединения order_items и products с GROUP BY brand для уникальных товаров
DCOUNT(user_id) с GROUP BY brand после соединения order_items и products без дедупликации записей
Ответ: Для «уникальных покупателей» нужно считать `COUNT(DISTINCT user_id)`, иначе связь «один ко многим» создаст дубликаты строк.
После `JOIN` одна покупка соответствует одной строке, но один пользователь может сделать много покупок в одном бренде. `COUNT(*)` посчитает строки, а не людей, и метрика будет расти при увеличении среднего числа позиций. `COUNT(DISTINCT user_id)` с группировкой по `brand` отвечает на бизнес-вопрос и не зависит от числа товаров в чеке. `SUM` или `AVG` от `user_id` бессмысленны как агрегаты по идентификатору.
Подробный разбор → 12Нужен набор данных на уровне `user_id`: выручка из `orders` и число сессий из `sessions`. В обеих таблицах по пользователю много строк (один-ко-многим). Какой подход минимизирует риск размножения строк в соединении?
AСделать `JOIN` таблиц `orders` и `sessions` по `user_id`, а затем посчитать `SUM(amount)` и `COUNT(*)` поверх результата
BСделать `JOIN` сырых таблиц по `user_id`, а потом применить `DISTINCT` ко всем колонкам результата
CУдалить все строки кроме первой в `orders` для каждого пользователя, чтобы соединение стало один-к-одному
DСначала свернуть `orders` до одной строки на `user_id` и отдельно свернуть `sessions`, затем соединить готовые агрегаты
Ответ: Для метрик на уровне пользователя сначала сворачивают источники до нужного уровня детализации, и только потом соединяют.
Если соединить «сырые» `orders` и `sessions`, получится связь многие-ко-многим по `user_id`, и строки будут размножаться. В результате денежные и счётные метрики будут завышены. Предварительная агрегация до одной строки на пользователя сохраняет правильный уровень данных и делает соединение ближе к один-к-одному. `DISTINCT` поверх раздутого результата не восстанавливает корректные суммы, а удаление лишних строк искажает данные.
Подробный разбор → 13У товара может быть несколько категорий в таблице `product_categories(product_id, category_id)`, а продажи лежат в `sales_lines(product_id, revenue)` (много строк на товар). Вы посчитали выручку по категориям после соединения. Что будет, если потом сложить выручку всех категорий в одну цифру?
AСумма будет равна общей выручке, потому что `SUM` автоматически убирает повторы по составному ключу строк
BСумма будет меньше общей выручки, потому что категории фильтруют часть строк по условиям соединения таблиц
CСумма может стать больше общей выручки, потому что связь «многие-ко-многим» дублирует выручку для товаров с несколькими категориями
DСумма станет равна числу товаров из-за `COUNT(*)` и потеряет смысл денежной величины полностью
Ответ: При связи «многие-ко-многим» один факт попадает в несколько групп, и суммарные `SUM` по группам перестают сходиться с общим итогом.
Если товар принадлежит двум категориям, его строка из `sales_lines` после `JOIN` попадёт в обе группы и будет учтена дважды. Это не всегда «ошибка», но тогда нельзя ожидать, что сумма по категориям совпадёт с общей выручкой. Чтобы контролировать это, нужно заранее определить правило распределения (например, разделить выручку поровну между категориями или выбрать одну основную). Иначе будет задвоение, и сводные `SUM` по группам перестанут сходиться с итогом.
Подробный разбор → 14В таблице `payments(order_id, amount, status)` для одного `order_id` может быть несколько попыток оплаты (один-ко-многим), например повторные списания или ошибки. Вы хотите посчитать выручку по заказам из `orders`. Что наиболее безопасно сделать перед соединением, чтобы `SUM()` не завысилась из-за дублирования?
AСоединить таблицы как есть и затем применить `DISTINCT` ко всем строкам результата, чтобы убрать повторяющиеся записи
BСначала свернуть `payments` до одной строки на `order_id` (предварительная агрегация), затем соединить с `orders` через `JOIN`
CСоединить `payments` с `orders` по полю `user_id`, поскольку так совпадений будет больше и выручка не потеряется
DЗаменить `SUM()` на `COUNT(*)`, так как количество строк всегда даёт более устойчивую оценку выручки по заказам
Ответ: При связи один-ко-многим по `order_id` деньги нужно сводить до одного факта на заказ через предварительную агрегацию, иначе появится дублирование в `SUM()`.
Если у заказа несколько платёжных строк, то `orders` начнёт дублироваться при `JOIN`, и каждая попытка попадёт в расчёт. В зависимости от бизнес-логики нужно выбрать правило: только успешные платежи, последний успешный или сумма успешных. После предварительной агрегации соединение становится ближе к один-к-одному, и денежные метрики становятся интерпретируемыми.
Подробный разбор → 15Вы соединили таблицы `users` и `orders` (один ко многим) и посчитали `AVG(order_amount)`, интерпретируя это как «средняя выручка на пользователя». Почему это неверно и как правильно?
AНеверно, потому что `AVG()` считает только по целым числам и пропускает дробные суммы; правильно использовать `SUM()` и затем поделить на пользователей
BВерно: `AVG(order_amount)` всегда равно средней выручке на пользователя при условии, что у каждого пользователя есть хотя бы один заказ в выборке
CНеверно, потому что `INNER JOIN` удаляет пользователей без заказов; нужно `LEFT JOIN` и тогда средняя выручка на пользователя посчитается корректно из коробки
DНеверно, потому что `AVG()` считает среднее по строкам заказов, а не по пользователям; нужно сначала агрегировать выручку до `user_id` через `SUM()`, а затем взять `AVG()` по пользователям
Ответ: При связи один ко многим `AVG()` по полю заказа даёт среднее по заказам, а не по пользователям, из-за разной мощности связи между таблицами.
Пользователь с десятью заказами будет весить в среднем в десять раз больше пользователя с одним заказом, поэтому получается среднее по строкам заказов. Если цель — метрика на пользователя, сначала посчитайте выручку на `user_id` через `SUM()` по заказам, а уже потом усредняйте. Это базовое правило выбора уровня агрегации перед `JOIN` и после него. Версии с `LEFT JOIN` или ограничением по типам данных задачу не решают, так как смещение возникает из-за разной мощности связи.
Подробный разбор →