Практика SQL
Прикладной SQL для аналитика — агрегация, группировка и фильтр HAVING.
15 вопросов
JuniorТеорияОчень частоINNER, LEFT и FULL OUTER JOIN — что каждый возвращает для строк без совпадения?
INNER, LEFT и FULL OUTER JOIN — что каждый возвращает для строк без совпадения?
INNER JOIN оставляет только строки, совпавшие с обеих сторон. LEFT JOIN сохраняет каждую левую строку, а несовпавшие правые столбцы заполняет NULL. FULL OUTER JOIN сохраняет несовпавшие строки с обеих сторон, дополняя NULL. Только INNER отбрасывает несовпавшие строки.
Типичные ошибки
- ✗Думать, что LEFT JOIN может отбросить левые строки при отсутствии правого совпадения
- ✗Считать, что INNER JOIN дополняет несовпавшие строки NULL, а не отбрасывает их
- ✗Путать, какую сторону сохраняет FULL OUTER
Уточняющие вопросы
- →Как условие WHERE по правой таблице меняет LEFT JOIN?
- →Когда выбрать FULL OUTER вместо UNION двух LEFT JOIN?
MiddleКодОчень частоДедуплицировать таблицу, оставив одну строку на пользователя — самую свежую по updated_at
Дедуплицировать таблицу, оставив одну строку на пользователя — самую свежую по updated_at
Ранжируют строки внутри пользователя и берут верхнюю: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn, затем фильтр rn = 1. DISTINCT не выберет свежую; обычный GROUP BY user_id требует агрегат на каждый прочий столбец.
Типичные ошибки
- ✗Ждать, что DISTINCT выберет свежую строку на пользователя
- ✗Считать, что MAX() тянет прочие столбцы из той же строки
- ✗Группировать по user_id, выбирая негруппированные столбцы
Уточняющие вопросы
- →Как разрешить ничьи при равных updated_at?
- →Когда DISTINCT ON проще, чем ROW_NUMBER, здесь?
MiddleДебаггингОчень частоLEFT JOIN незаметно ведёт себя как INNER JOIN — фильтр правой таблицы в WHERE. Исправьте.
LEFT JOIN незаметно ведёт себя как INNER JOIN — фильтр правой таблицы в WHERE. Исправьте.
Условие WHERE o.status = 'completed' идёт после join и убирает NULL-дополненные строки, поэтому LEFT JOIN схлопывается в INNER JOIN. Перенесите фильтр правой таблицы в join: LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'completed'.
Типичные ошибки
- ✗Фильтровать правую таблицу в WHERE после LEFT JOIN
- ✗Винить слово LEFT JOIN, а не момент WHERE
- ✗Думать, что ON ссылается лишь на столбцы ключа join
Уточняющие вопросы
- →Когда фильтр правой таблицы в WHERE всё же уместен?
- →Чем проверка IS NULL в WHERE отличается от переноса в ON?
JuniorТеорияЧастоCOUNT(*), COUNT(col) и COUNT(DISTINCT col) по столбцу с NULL — почему три числа?
COUNT(*), COUNT(col) и COUNT(DISTINCT col) по столбцу с NULL — почему три числа?
COUNT(*) считает все строки, включая NULL. COUNT(col) считает только строки, где col не NULL. COUNT(DISTINCT col) считает различные не-NULL значения. По столбцу с NULL три числа разные — NULL поднимают только COUNT(*), а дубли — только COUNT(col).
Типичные ошибки
- ✗Думать, что COUNT(col) считает строки с NULL, как COUNT(*)
- ✗Считать, что COUNT(DISTINCT col) держит корзину для NULL
- ✗Верить, что три счётчика всегда совпадают
Уточняющие вопросы
- →Как явно посчитать NULL в этом столбце?
- →Когда COUNT(DISTINCT col) равен COUNT(col)?
JuniorТеорияЧастоЧтобы найти клиентов с более чем одним заказом в какой-то день прошлого месяца, куда поставить фильтр по счёту?
Чтобы найти клиентов с более чем одним заказом в какой-то день прошлого месяца, куда поставить фильтр по счёту?
Фильтр по агрегату ставят в HAVING, а не в WHERE — WHERE не может ссылаться на COUNT(). Надо группировать и по клиенту, и по дню, затем оставить группы с более чем одним заказом: GROUP BY customer_id, order_date HAVING COUNT(order_id) > 1, а условие диапазона дат — в WHERE.
Типичные ошибки
- ✗Ставить COUNT() в WHERE вместо HAVING
- ✗Группировать только по клиенту, теряя требование «в один день»
- ✗Принимать растущий id заказа за счёт заказов
Уточняющие вопросы
- →Почему WHERE не может ссылаться на агрегат вроде COUNT()?
- →Что меняет группировка и по клиенту, и по дню?
JuniorКодЧастоВывести сотрудников с максимальной зарплатой
Вывести сотрудников с максимальной зарплатой
Считают максимум зарплаты один раз и выбирают всех, кто ему равен. CTE делает это чисто: WITH m AS (SELECT MAX(salary) s FROM employees) SELECT first_name FROM employees JOIN m ON salary = m.s. Эквивалентно WHERE salary = (SELECT MAX(salary) FROM employees).
Типичные ошибки
- ✗Смешивать MAX() с обычным столбцом без GROUP BY
- ✗Использовать LIMIT 1, теряя сотрудников с равным максимумом
- ✗Путать «выше среднего» с максимумом
Уточняющие вопросы
- →Как заодно вернуть только нанятых в этом году?
- →Почему LIMIT 1 может дать неверный ответ здесь?
JuniorКодЧастоТоп-5 продуктов по выручке, с проданным количеством и детерминированным добором ничьих
Топ-5 продуктов по выручке, с проданным количеством и детерминированным добором ничьих
Агрегируют по продукту, затем сортируют и ограничивают: SELECT product_id, SUM(quantity) AS qty, SUM(quantity*unit_price) AS revenue FROM order_items GROUP BY product_id ORDER BY revenue DESC, product_id LIMIT 5. Вторичный ключ product_id делает ничьи детерминированными.
Типичные ошибки
- ✗Выбирать product_id без GROUP BY по нему
- ✗Ранжировать по количеству вместо выручки
- ✗Использовать LIMIT без добора ничьих, и запуски расходятся
Уточняющие вопросы
- →Как вернуть ничьи на 5-м месте, а не обрезать их?
- →Что меняется, если unit_price может быть NULL?
JuniorТеорияЧастоUNION против UNION ALL — в чём разница и почему UNION дороже?
UNION против UNION ALL — в чём разница и почему UNION дороже?
UNION ALL склеивает оба входа и оставляет все строки. UNION делает то же, но затем убирает строки-дубли, а это требует сортировки или хеширования всего результата — именно дедупликация и есть лишняя цена. UNION ALL берут, когда дубли допустимы; он быстрее.
Типичные ошибки
- ✗Путать, какая форма дедуплицирует результат
- ✗Думать, что UNION ALL дороже UNION
- ✗Ждать, что UNION уберёт только внутренние дубли входа
Уточняющие вопросы
- →Как заметить UNION, который должен был быть UNION ALL?
- →Нужны ли входам совпадающие типы столбцов для любой формы?
MiddleДебаггингЧастоВыручка удвоилась после join заказов к таблице shipments «один-ко-многим». Найдите и исправьте.
Выручка удвоилась после join заказов к таблице shipments «один-ко-многим». Найдите и исправьте.
Join к shipments «один-ко-многим» повторяет строку заказа на каждую отгрузку, поэтому SUM(o.amount) складывает сумму заказа много раз — fan-out. Сверните shipments в строку на заказ (CTE), затем join, либо SUM лишь по столбцу уровня отгрузки.
Типичные ошибки
- ✗Суммировать родительский столбец через join один-ко-многим
- ✗Винить cross join вместо законного fan-out
- ✗Пытаться усреднением убрать дублированные строки
Уточняющие вопросы
- →Как COUNT(DISTINCT o.order_id) помогает найти fan-out?
- →Когда пред-агрегация shipments лучше, чем SUM(DISTINCT)?
MiddleКодЧастоНайти пользователей, что зарегистрировались, но не покупали — и почему NOT IN даёт ноль строк
Найти пользователей, что зарегистрировались, но не покупали — и почему NOT IN даёт ноль строк
Анти-join: SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.user_id IS NULL, либо NOT EXISTS. NOT IN (SELECT user_id FROM orders) даёт ноль строк при NULL в подзапросе, ведь x NOT IN (…, NULL) равно UNKNOWN.
Типичные ошибки
- ✗Доверять NOT IN, когда в подзапросе может быть NULL
- ✗Брать INNER JOIN, который полностью отбрасывает несовпавших
- ✗Фильтровать o.user_id IS NULL после inner join
Уточняющие вопросы
- →Как NOT EXISTS обходит проблему с NULL?
- →Починит ли NOT IN отсев NULL из подзапроса?
JuniorТеорияИногдаВ каком порядке выполняются клаузы SQL и почему WHERE не видит алиас из SELECT, а ORDER BY видит?
В каком порядке выполняются клаузы SQL и почему WHERE не видит алиас из SELECT, а ORDER BY видит?
Логический порядок — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE идёт до SELECT, поэтому алиас из SELECT ещё не существует и недоступен. ORDER BY идёт после SELECT, так что алиас уже определён. Тот же порядок объясняет, почему агрегаты фильтрует HAVING.
Типичные ошибки
- ✗Считать, что клаузы идут в написанном порядке (сперва SELECT)
- ✗Думать, что WHERE запрещает алиасы синтаксисом, а не порядком
- ✗Ждать, что WHERE фильтрует агрегаты, как HAVING
Уточняющие вопросы
- →Куда в этом порядке встаёт оконная функция?
- →Почему GROUP BY видит алиас из SELECT в некоторых СУБД?
MiddleКодИногдаПосчитать медиану суммы заказа в диалекте без функции MEDIAN
Посчитать медиану суммы заказа в диалекте без функции MEDIAN
Берут ordered-set агрегат PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount), что интерполирует центральное значение. Без него нумеруют строки по amount и усредняют центральную позицию(и). Обычный AVG даёт среднее, а не медиану.
Типичные ошибки
- ✗Возвращать AVG (среднее) вместо медианы
- ✗Брать одну центральную строку, игнорируя чётный случай
- ✗Считать, что среднее равно медиане после отсева NULL
Уточняющие вопросы
- →Чем PERCENTILE_DISC отличается от PERCENTILE_CONT здесь?
- →Как посчитать медиану по группам?
MiddleДебаггингРедко«column must appear in GROUP BY» чинят добавлением всех столбцов — строки взрываются. Почему?
«column must appear in GROUP BY» чинят добавлением всех столбцов — строки взрываются. Почему?
Ошибка значит, что выбранный столбец (order_date) не сгруппирован и не в агрегате. Добавление всех столбцов в GROUP BY меняет гранулярность на группу на строку, поэтому ничего не сворачивается. Чините группировкой по ключу (customer_id), оборачивая лишние в MAX().
Типичные ошибки
- ✗Добавлять все столбцы в GROUP BY, чтобы убрать ошибку
- ✗Думать, что больше столбцов в GROUP BY уменьшат результат
- ✗Хвататься за DISTINCT вместо правки гранулярности
Уточняющие вопросы
- →Когда группировка по двум столбцам — действительно то, что нужно?
- →Почему SELECT * с GROUP BY редко имеет смысл?
MiddleТеорияРедкоПодытоги по городу, по стране и общий итог за один проход — GROUPING SETS, ROLLUP или CUBE?
Подытоги по городу, по стране и общий итог за один проход — GROUPING SETS, ROLLUP или CUBE?
GROUPING SETS перечисляет точные комбинации группировки за один проход. ROLLUP(country, city) — краткая запись иерархических наборов {(country,city),(country),()}. CUBE(country, city) даёт все подмножества. GROUPING() помечает, какие столбцы свёрнуты в строке.
Типичные ошибки
- ✗Думать, что ROLLUP и CUBE — псевдонимы обычного GROUP BY
- ✗Верить, что GROUPING SETS не выражает общий итог
- ✗Считать ROLLUP симметричным, как CUBE
Уточняющие вопросы
- →Как отличить строку подытога от значения NULL в данных?
- →Когда CUBE уместнее ROLLUP?
