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