0Pricing
SQL Academy · Урок

Объяснение GROUPING SETS

Выбирайте EXACTLY те группировки, которые Вам нужны

«Объяснение GROUPING SETS» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое GROUPING SETS

Когда Вы записываете предложение GROUP BY, Вы определяете один набор столбцов для группировки. Но иногда в одном запросе требуется несколько разных группировок — без многократного запуска запроса и использования UNION ALL.

GROUPING SETS позволяет точно указать нужные группировки и получить их за один проход по данным. Каждый набор в списке создаёт собственные агрегированные строки в результате.

Пример таблицы: продажи

На протяжении всего урока мы будем использовать таблицу продажи. В ней записаны операции с указанием региона, категории продукта и суммы.

Запустите код, чтобы создать и заполнить таблицу и проследить за каждым примером.

CREATE TABLE sales (
  region   TEXT,
  category TEXT,
  amount   NUMERIC
);

INSERT INTO sales VALUES
  ('East',  'Electronics', 500),
  ('East',  'Clothing',    200),
  ('West',  'Electronics', 300),
  ('West',  'Clothing',    400),
  ('North', 'Electronics', 150),
  ('North', 'Clothing',    250);

Старый способ: UNION ALL

До появления GROUPING SETS получение итогов на нескольких уровнях требовало написания отдельных запросов и их объединения с помощью UNION ALL. Такой подход повторяется, хуже читается и несколько раз просматривает таблицу.

Приведённый ниже пример возвращает итоги по региону AND итоги по категории, используя три отдельных оператора SELECT.

SELECT region, NULL AS category, SUM(amount) AS total
FROM sales
GROUP BY region

UNION ALL

SELECT NULL, category, SUM(amount)
FROM sales
GROUP BY category

UNION ALL

SELECT NULL, NULL, SUM(amount)
FROM sales;

Синтаксис GROUPING SETS

Предложение GROUPING SETS размещается внутри обычного GROUP BY или вместо него. Каждую нужную группировку следует указать как список столбцов в скобках. Пустой набор () означает общий итог — полное отсутствие группировки.

Этот единственный запрос делает ровно то же, что и приведённый выше трёхчастный UNION ALL, но записан короче и просматривает таблицу только один раз.

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

Чтение результата

Каждая строка результата относится ровно к одному набору группировки. Если столбец не входит в активный набор группировки, его значение равно NULL.

  • Строка с region = 'East' и category = NULL относится к набору (region).
  • Строка с region = NULL и category = 'Electronics' относится к набору (category).
  • Строка, в которой оба значения равны NULL, содержит общий итог из набора ().

Эти значения NULL имеют структурный смысл: они обозначают «все значения» данного измерения, а не отсутствие данных.

Наборы группировки по нескольким столбцам

Каждый набор группировки может содержать более одного столбца. Набор (region, category) группирует данные одновременно по обоим столбцам, как обычный GROUP BY region, category.

Сочетание такого набора с отдельными наборами и общим итогом даёт сводку на четырёх уровнях в одном запросе.

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  (category),
  ()
);

Функция GROUPING()

Поскольку NULL может означать либо отсутствие столбца в группировке, либо действительно отсутствующие данные, SQL предоставляет функцию GROUPING(), которая позволяет различать эти случаи.

GROUPING(col) возвращает 1, если столбец исключён из текущего набора группировки (то есть его NULL имеет структурный смысл), и 0, если столбец входит в группировку (или содержит настоящее значение NULL).

SELECT
  region,
  category,
  SUM(amount)      AS total,
  GROUPING(region) AS is_region_total,
  GROUPING(category) AS is_category_total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

Замена подписей NULL

Распространённый приём — заменить структурные значения NULL описательной подписью, чтобы отчёты было проще читать. Используйте CASE WHEN GROUPING(...) = 1 THEN 'Все ...' ELSE столбец END, чтобы подставлять подпись только для строк промежуточных итогов.

SELECT
  CASE WHEN GROUPING(region)   = 1 THEN 'All Regions'    ELSE region   END AS region,
  CASE WHEN GROUPING(category) = 1 THEN 'All Categories' ELSE category END AS category,
  SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

GROUPING SETS и ROLLUP

ROLLUP(a, b) — это сокращённая запись, раскрывающаяся в наборы группировки (a, b), (a), (): она всегда добавляет промежуточные итоги по иерархии и общий итог.

GROUPING SETS предоставляет полный контроль: Вы точно выбираете, какие комбинации должны появиться. Если полная иерархия не нужна, уровни можно опустить. Два приведённых ниже запроса дают одинаковый результат.

-- Using ROLLUP
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category);

-- Equivalent explicit GROUPING SETS
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
);

GROUPING SETS и CUBE

CUBE(a, b) создаёт все возможные комбинации перечисленных столбцов: (a, b), (a), (b), (). Он полезен для анализа по пересечениям измерений, но создаёт много строк.

GROUPING SETS позволяет выбрать только нужные комбинации, сохраняя результат целенаправленным, а запрос — быстрым.

-- CUBE produces 4 grouping sets for 2 columns
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY CUBE(region, category);

-- GROUPING SETS: omit the (category)-only set if not needed
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
);

Практический пример: панель продаж

Для настоящей панели мониторинга часто нужны детализация на уровне строк, промежуточные итоги по отделам и общий итог — всё в одном результирующем наборе. GROUPING SETS делает это без труда, без агрегации на стороне приложения и без нескольких обращений к базе данных.

Этот запрос возвращает подробные строки по регионам и категориям, промежуточные итоги только по регионам и одну строку общего итога — всё отсортировано так, чтобы общий итог оказался последним.

SELECT
  COALESCE(region,   'TOTAL')      AS region,
  COALESCE(category, 'ALL')        AS category,
  SUM(amount)                      AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
)
ORDER BY
  GROUPING(region),
  region NULLS LAST,
  GROUPING(category),
  category NULLS LAST;

Быстрая проверка

Проверьте, насколько хорошо Вы поняли GROUPING SETS.

Итоги урока

Вот что Вы узнали о GROUPING SETS:

  • GROUPING SETS позволяет определить несколько комбинаций группировки в одном запросе, заменяя повторяющиеся конструкции с UNION ALL.
  • Каждый набор указывается как список столбцов в скобках внутри GROUP BY GROUPING SETS (...). Пустой набор () создаёт общий итог.
  • Столбцы, отсутствующие в активном наборе, отображаются в этой строке как NULL; для обнаружения таких структурных значений NULL используйте GROUPING(col).
  • ROLLUP и CUBE — удобные сокращённые записи распространённых шаблонов, но GROUPING SETS предоставляет полный и точный контроль над включаемыми группировками.
  • Сочетайте их с COALESCE или CASE WHEN GROUPING(...), чтобы заменять NULL понятными подписями в отчётах.

Часто задаваемые вопросы

Урок «Объяснение GROUPING SETS» бесплатный?

Да — полный текст урока «Объяснение GROUPING SETS» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Объяснение GROUPING SETS»?

Выбирайте EXACTLY те группировки, которые Вам нужны Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Объяснение GROUPING SETS»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Не только один GROUP BY
  2. ROLLUP для промежуточных итогов
  3. CUBE для всех сочетаний
  4. Объяснение GROUPING SETS
← Назад к SQL Academy