JOIN и кардинальность: вопросы для собеседования (часть 2)
Что произойдёт при JOIN, если в правой таблице несколько строк на один ключ? Сколько строк вернёт LEFT JOIN, если совпадений нет? На собеседовании дают две таблицы и просят предсказать результат без выполнения запроса. Понимание кардинальности при JOIN — навык, который отличает аналитика, способного писать корректные запросы.
Вопросы 6–10 из 20
6В каком случае `SUM(order_amount)` после `JOIN` скорее всего останется корректным, без эффекта `duplication`?
AСоединить `orders` с `order_items` по `order_id`, потому что это `one-to-many`
BСоединить `orders` с `payments` по `user_id`, потому что так проще
CСоединить `orders` с `users` по `user_id`, если в `users` `one-to-one` `cardinality` (одна строка на пользователя)
DСоединить `orders` с `product_tags` по `product_id`, потому что теги не влияют на деньги
Ответ: Атрибутный `JOIN` к таблице с `one-to-one` `cardinality` не размножает строки, поэтому `SUM()` не искажается.
Если правая таблица имеет максимум одну строку на ключ, то каждая строка `orders` найдёт не более одного совпадения. В таком случае `SUM(order_amount)` останется на том же уровне, просто добавятся атрибуты. А вот `JOIN` к `one-to-many` или `many-to-many` источнику часто создаёт `duplication` и завышает суммы.
7Вы соединяете `users` и `orders` по `user_id`, где у пользователя может быть много заказов (`one-to-many`). Как посчитать число пользователей, которые сделали хотя бы один заказ, чтобы избежать `duplication`?
AПосчитать `COUNT(*)` после `JOIN`
BПосчитать `COUNT(DISTINCT user_id)` после `JOIN`
CПосчитать `SUM(order_id)` после `JOIN`
DПосчитать `AVG(order_amount)` после `JOIN`
Ответ: В `one-to-many` соединении `COUNT(*)` считает строки заказов, поэтому для пользователей нужен `distinct` по `user_id`.
После `JOIN` каждая покупка создаёт отдельную строку, поэтому пользователи с несколькими заказами появляются несколько раз — это `duplication`. Чтобы получить число уникальных пользователей, нужно считать уникальные `user_id`, например `COUNT(DISTINCT user_id)`. Этот же принцип полезен и для других метрик, где единица анализа — пользователь.
8В таблице `users` 100 000 строк, в таблице `user_profiles` — ровно одна строка на каждого `user_id`. Вы делаете `INNER JOIN` по `user_id`. Что верно про число строк результата?
AОно не может быть больше 100 000; при `one-to-one` `cardinality` `INNER JOIN` может только уменьшить строки, если есть пропуски
BВсегда будет ровно 100 000 строк, потому что это `one-to-one`
CМожет вырасти до 100 000 * 100 000 из-за `join explosion`
DСтанет равно числу колонок в `user_profiles`
Ответ: `one-to-one` `cardinality` не размножает строки, а `INNER JOIN` может только отбросить несовпавшие ключи.
При `one-to-one` на каждый `user_id` с каждой стороны есть максимум одна строка, поэтому множителя нет. `INNER JOIN` вернёт только пары, где ключ есть в обеих таблицах. Если часть пользователей без профиля, строки уменьшатся; если профили есть для всех, получится 100 000.
9Для одного `order_id` в `order_items` есть 3 строки, а в `payments` есть 2 строки. Вы соединили всё в одну таблицу по `order_id` без предварительной агрегации. Сколько строк получится для этого заказа и почему?
A5 строк, потому что 3 + 2
B3 строки, потому что это `one-to-many`
C6 строк, потому что возникает `many-to-many` и `join explosion`: 3 * 2
D2 строки, потому что платежи «поглощают» позиции
Ответ: Когда две таблицы обе `one-to-many` к одному ключу, их `JOIN` превращается в `many-to-many` и даёт `join explosion`.
Внутри одного `order_id` позиции и платежи комбинируются между собой. Каждая из 3 позиций соединится с каждым из 2 платежей, поэтому получится 6 строк. Это типичный источник `duplication` в денежных метриках, если затем делать `SUM()`.
10Вы хотели посчитать средний чек по заказам как `AVG(order_total)`. Но перед этим соединили `orders` с `order_items` по `order_id` (`one-to-many`). Почему `AVG()` может измениться по сравнению с расчётом на таблице `orders`?
AПоявилась `duplication`: каждый `order_total` повторился по числу строк в `order_items`, и `AVG()` стал взвешен по количеству позиций
B`AVG()` всегда игнорирует строки после `JOIN`
CПотому что `one-to-one` `cardinality` делает среднее выше
DПотому что `distinct` автоматически удаляет большие чеки
Ответ: После `one-to-many` `JOIN` `AVG()` по полю заказа становится взвешенным из-за `duplication` строк.
В исходной таблице `orders` один заказ равен одной строке, поэтому `AVG(order_total)` — простой средний чек по заказам. После `JOIN` каждый заказ повторяется столько раз, сколько у него позиций, и влияет на среднее несколько раз. Чтобы избежать искажения, считайте `AVG()` на уровне заказа или `pre-aggregate` до `order_id` перед соединением.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram