JOIN и кардинальность: вопросы для собеседования (часть 2)
Что произойдёт при JOIN, если в правой таблице несколько строк на один ключ? Сколько строк вернёт LEFT JOIN, если совпадений нет? На собеседовании дают две таблицы и просят предсказать результат без выполнения запроса. Понимание кардинальности при JOIN — навык, который отличает аналитика, способного писать корректные запросы.
Вопросы 6–10 из 20
6В каком случае `SUM(order_amount)` после `JOIN` скорее всего останется корректным, без эффекта размножения строк?
AСоединение `orders` с `order_items` по `order_id`: связь один-ко-многим размножает строки заказов
BСоединение `orders` с `payments` по `user_id`: ключ соединения шире, чем `order_id`, и может дублировать платежи
CСоединение `orders` с `users` по `user_id` при одной строке на пользователя справа: атрибутный `JOIN` без размножения
DСоединение `orders` с `product_tags` по `product_id`: связь многие-ко-многим через теги создаёт дубликаты
Ответ: Атрибутный `JOIN` к таблице с одной строкой на ключ не размножает строки, поэтому `SUM()` по сумме заказа не искажается.
Если правая таблица имеет максимум одну строку на ключ, каждая строка заказов найдёт не более одного совпадения. В таком случае `SUM(order_amount)` останется на том же уровне, просто к строкам добавятся атрибуты. А вот `JOIN` к источнику с отношением «один-ко-многим» или «многие-ко-многим» часто создаёт дубликаты строк и завышает суммы. Поэтому перед агрегацией важно проверять связность ключа справа.
Подробный разбор → 7Вы соединяете таблицы пользователей и заказов по `user_id`, где у одного пользователя может быть много заказов (связь «один ко многим»). Как посчитать число пользователей, которые сделали хотя бы один заказ, и не получить дубли?
ACOUNT(*) после соединения таблиц пользователей и заказов по user_id
BCOUNT(DISTINCT user_id) после соединения таблиц пользователей и заказов по user_id
CCOUNT(user_id) после соединения с фильтром WHERE order_id IS NOT NULL
DCOUNT(DISTINCT order_id) после соединения с группировкой GROUP BY user_id
Ответ: В соединении «один ко многим» `COUNT(*)` считает строки заказов, поэтому для пользователей нужен `COUNT(DISTINCT user_id)`.
После соединения каждый заказ создаёт отдельную строку, поэтому пользователи с несколькими заказами появляются в результате несколько раз — это и есть дублирование. Чтобы получить число уникальных пользователей, нужно считать уникальные `user_id`, например через `COUNT(DISTINCT user_id)`. Тот же принцип полезен и для других метрик, где единица анализа — пользователь, а не строка таблицы.
Подробный разбор → 8В таблице `users` 100 000 строк, в таблице `user_profiles` — ровно одна строка на каждого `user_id`. Вы делаете `INNER JOIN` по `user_id`. Что верно про число строк результата?
AВсегда будет ровно 100 000 строк, потому что соответствие «один к одному» гарантирует совпадение ключей в обеих таблицах независимо от пропусков
BМожет вырасти до 100 000 × 100 000 строк из-за размножения строк при `JOIN`, даже если справа на каждый `user_id` по одной записи
CСтанет равно числу колонок в таблице `user_profiles`, потому что `INNER JOIN` соединяет столбцы и приводит результат к ширине справочника
DНе может быть больше 100 000: при соответствии «один к одному» `INNER JOIN` может только уменьшить число строк, если у каких-то пользователей нет профиля
Ответ: При связи «один к одному» `INNER JOIN` не размножает строки, а может только отбросить несовпавшие ключи.
При связи «один к одному» на каждый `user_id` с каждой стороны есть максимум одна строка, поэтому множителя нет. `INNER JOIN` вернёт только пары, где ключ есть в обеих таблицах. Если часть пользователей без профиля, число строк уменьшится; если профили есть для всех, получится 100 000. Поэтому варианты «всегда ровно 100 000» или «может вырасти до 100 000 × 100 000» некорректны для такой структуры данных.
Подробный разбор → 9Для одного `order_id` в `order_items` есть 3 строки, а в `payments` — 2 строки. Вы соединили обе таблицы по `order_id` без предварительной агрегации. Сколько строк получится для этого заказа и почему?
A5 строк, потому что просто складываются 3 позиции и 2 платежа в общий список без какого-либо умножения комбинаций.
B3 строки, потому что соединение по ключу заказа всегда даёт связь «один к многим» и сохраняет именно число позиций.
C2 строки, потому что записи о платежах «поглощают» позиции при соединении и в результате остаётся только число платежей.
D6 строк, потому что внутри одного `order_id` возникает связь «многие ко многим» и происходит размножение строк: 3 × 2 = 6.
Ответ: Когда две таблицы обе `one-to-many` к одному ключу, их `JOIN` превращается в `many-to-many` и даёт `join explosion`.
Внутри одного `order_id` позиции и платежи комбинируются между собой. Каждая из 3 позиций соединится с каждым из 2 платежей, поэтому получится 6 строк. Это типичный источник дублирования в денежных метриках, если затем делать `SUM()` по такому соединению. Чтобы избежать размножения строк, обычно агрегируют одну из сторон до соединения.
Подробный разбор → 10Вы хотели посчитать средний чек по заказам как `AVG(order_total)`. Но перед этим соединили `orders` с `order_items` по `order_id` (связь один-ко-многим). Почему `AVG()` может измениться по сравнению с расчётом на таблице `orders`?
AПосле соединения `AVG()` пересчитывается по новой формуле и игнорирует строки, у которых `order_total` совпадает с уже учтёнными заказами
BПосле соединения связь становится один-к-одному, поэтому каждый `order_total` учитывается ровно один раз и среднее всегда оказывается выше прежнего
CПосле соединения каждый `order_total` повторился по числу строк в `order_items`, и `AVG()` стал взвешен по числу позиций в заказе
DПосле соединения `DISTINCT` автоматически убирает крупные `order_total` и оставляет только типичные суммы, поэтому среднее заметно меняется
Ответ: После соединения один-ко-многим `AVG()` по полю заказа становится взвешенным из-за дублирования строк.
В исходной таблице `orders` один заказ равен одной строке, поэтому `AVG(order_total)` — простой средний чек по заказам. После соединения с `order_items` каждый заказ повторяется столько раз, сколько у него позиций, и влияет на среднее несколько раз. Чтобы избежать искажения, считайте `AVG()` на уровне заказа или предварительно агрегируйте `order_items` до `order_id` перед соединением.
Подробный разбор →