Группировка по нескольким столбцам и выражениям
Составные ключи группировки и группировка по вычисляемым значениям.
«Группировка по нескольким столбцам и выражениям» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.
За пределами одного ключа группировки
В реальных отчётах редко выполняется группировка только по одному столбцу. Интервьюеры переходят от задачи «продажи по регионам» к задаче «продажи по регионам и месяцам», чтобы проверить, понимаете ли Вы составные ключи группировки.
Правило легко расширяется: перечисление дополнительных столбцов в GROUP BY создаёт одну строку для каждой уникальной комбинации этих столбцов.
Что означают несколько столбцов
Если написать GROUP BY region, product, ключом группы будет пара (регион, продукт). Каждая уникальная пара становится отдельной строкой результата.
- 5 регионов и 4 продукта дают до 20 групп.
- Комбинации, которые ни разу не встречаются, вообще не создают строк.
- Порядок столбцов в GROUP BY не меняет набор результатов, а иногда влияет только на план выполнения.
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY region, product;Правило SELECT по-прежнему действует
Основное ограничение не меняется. Каждый неагрегированный столбец SELECT должен присутствовать в GROUP BY. Для составных ключей нужно просто перечислить их все.
Забыть один столбец в группе с несколькими ключами — самая распространённая ошибка под давлением собеседования. Точно сопоставляйте список GROUP BY с неагрегированными столбцами SELECT.
-- Both region and product appear in GROUP BY
SELECT region, product,
COUNT(*) AS orders,
AVG(amount) AS avg_amount
FROM sales
GROUP BY region, product;Группировка по выражению
Группировать можно по вычисляемому значению, а не только по исходному столбцу. Часто требуется сгруппировать продажи по месяцам, то есть выполнить группировку по усечённой или извлечённой дате.
Выражение в GROUP BY должно совпадать с выражением в SELECT. СУБД выполняет группировку по вычисленному результату.
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS monthly_total
FROM sales
GROUP BY DATE_TRUNC('month', order_date);Группировка по категориям CASE
Мощный приём — группировать по выражению CASE, чтобы создавать собственные категории. Так можно получить сводку по «маленьким / средним / крупным заказам» без справочной таблицы.
Интервьюеры любят этот приём, потому что он одновременно проверяет логику CASE и группировку по производному значению.
SELECT CASE
WHEN amount < 50 THEN 'small'
WHEN amount < 200 THEN 'medium'
ELSE 'large'
END AS bucket,
COUNT(*) AS orders
FROM sales
GROUP BY CASE
WHEN amount < 50 THEN 'small'
WHEN amount < 200 THEN 'medium'
ELSE 'large'
END;Группировка по позиции столбца
Многие диалекты позволяют группировать по порядковому номеру: GROUP BY 1, 2 означает первый и второй столбцы SELECT. Это кратко, но ненадёжно.
Интервьюеры могут принять такой вариант, но обязательно отметьте риск: перестановка списка SELECT незаметно изменит группировку. В рабочем коде предпочтительнее явно указывать выражения.
-- 1 = month expression, 2 = region
SELECT DATE_TRUNC('month', order_date) AS month,
region, SUM(amount)
FROM sales
GROUP BY 1, 2;NULL образуют собственную группу
Один из нюансов обработки NULL при группировке: если столбец группировки содержит NULL, все строки с NULL объединяются в одну группу.
Это отличается от сравнений на равенство, где NULL никогда не равен NULL. При группировке значения NULL считаются «одинаковыми» и образуют одну категорию. Интервьюеры любят этот кажущийся парадокс.
-- Rows with region IS NULL all land in one group
SELECT region, COUNT(*) AS orders
FROM sales
GROUP BY region;GROUPING SETS для нескольких уровней
Чтобы получить несколько уровней детализации в одном запросе, используйте GROUPING SETS. СУБД вычисляет каждый перечисленный набор ключей и объединяет результаты.
Это показывает уровень специалиста от среднего до старшего: вместо трёх отдельных запросов с UNION один оператор возвращает одновременно данные по регионам, данные по продуктам и общие итоги.
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS ((region), (product), ());ROLLUP и CUBE
ROLLUP и CUBE — сокращённые записи для распространённых наборов группировки. ROLLUP(region, product) возвращает данные по сочетанию региона и продукта, промежуточные итоги по регионам и общий итог — это идеально подходит для иерархических отчётов.
CUBE создаёт все комбинации перечисленных столбцов. Знание этих возможностей отличает соискателя, который создавал реальные отчёты, от остальных кандидатов.
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product);Пример: ежемесячный отчёт по регионам
Объединим изученные идеи: вычислим общие и средние продажи по регионам и месяцам только для завершённых заказов и отсортируем результат для удобства чтения.
WHERE сначала сужает набор строк, составной ключ выполняет группировку по региону и выражению для месяца, а ORDER BY упорядочивает результат. Это типичный полный ответ на собеседовании аналитика.
SELECT region,
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS total,
AVG(amount) AS avg_order
FROM sales
WHERE status = 'completed'
GROUP BY region, DATE_TRUNC('month', order_date)
ORDER BY region, month;Как рассуждать вслух
Для любого вопроса о составной группировке явно назовите ключ: «Я выполняю группировку по сочетанию X и Y, поэтому получаю одну строку для каждой уникальной пары (X, Y)».
Затем подтвердите, что каждый неагрегированный столбец SELECT входит в этот ключ, и упомяните, что NULL объединяются в одну группу. Именно такую последовательность рассуждений ценят на собеседованиях для специалистов среднего уровня.
Быстрая проверка
Рассуждайте о составных ключах группировки.
Итоги
Составные ключи: перечисление нескольких столбцов создаёт одну строку для каждой уникальной комбинации; каждый неагрегированный столбец SELECT должен входить в ключ.
- Можно выполнять группировку по выражениям, категориям CASE или порядковым позициям (позиции ненадёжны).
- NULL в столбце группировки объединяются в одну группу.
GROUPING SETS,ROLLUPиCUBEсоздают несколько уровней группировки в одном запросе.- Переносите фильтры строк в WHERE до группировки.
Часто задаваемые вопросы
Урок «Группировка по нескольким столбцам и выражениям» бесплатный?
Да — полный текст урока «Группировка по нескольким столбцам и выражениям» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.
Чему я научусь в уроке «Группировка по нескольким столбцам и выражениям»?
Составные ключи группировки и группировка по вычисляемым значениям. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Interview Prep?
Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.
Сколько времени занимает урок «Группировка по нескольким столбцам и выражениям»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Interview Prep?
Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Правило GROUP BY для столбцов SELECT
- HAVING и WHERE
- Группировка по нескольким столбцам и выражениям
- Подсчёт и фильтрация групп