SQL Academy · Урок

Не только один GROUP BY

Выполняйте агрегацию на нескольких уровнях

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

Ограничения одного GROUP BY

Стандартное предложение GROUP BY позволяет агрегировать строки на одном конкретном уровне — например, вычислять общие продажи по регионам. Но что, если Вы хотите получить также итоги по категориям товаров и общий итог в одном запросе?

Повторить запрос три раза и использовать UNION ALL можно, но это многословно и медленно. SQL предоставляет три мощных расширения — GROUPING SETS, ROLLUP и CUBE, — которые элегантно решают эту задачу за один проход.

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Подготовка таблицы для примера

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

CREATE TABLE sales (
  id       SERIAL PRIMARY KEY,
  region   TEXT,
  category TEXT,
  amount   NUMERIC
);

INSERT INTO sales (region, category, amount) VALUES
  ('North', 'Electronics', 1200),
  ('North', 'Clothing',     800),
  ('South', 'Electronics',  950),
  ('South', 'Clothing',     600),
  ('East',  'Electronics', 1100),
  ('East',  'Clothing',     750);

Что такое GROUPING SETS?

GROUPING SETS позволяет определить несколько независимых уровней группировки в одном предложении GROUP BY. Каждый набор в списке формирует собственную группу строк — так же, как если бы Вы написали отдельные запросы и объединили их с помощью UNION ALL.

Синтаксис выглядит так: GROUP BY GROUPING SETS ( (col1, col2), (col1), () ). Пустой набор () представляет общий итог по всем строкам.

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

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

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

Например, строка из набора (region) будет содержать NULL в столбце category, показывая, что этот итог охватывает все категории данного региона. В строке общего итога (пустой набор) значения NULL находятся и в столбце region, и в столбце category.

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

Знакомство с ROLLUP

ROLLUP — это сокращение для распространённой иерархии наборов группировки. Для столбцов (A, B) конструкция ROLLUP(A, B) автоматически создаёт наборы: (A, B), (A) и ().

Это идеально подходит для иерархических данных, например для перехода от года к месяцу и дню или от региона к категории. Количество создаваемых наборов всегда равно n + 1, где n — количество перечисленных столбцов.

-- ROLLUP(region, category) is equivalent to:
-- GROUPING SETS ( (region, category), (region), () )

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

ROLLUP с тремя уровнями

Добавление третьего столбца в ROLLUP расширяет иерархию ещё на один уровень. Конструкция ROLLUP(A, B, C) создаёт четыре набора: (A, B, C), (A, B), (A) и ().

В примере ниже year — внешний уровень, а category — самый детализированный. Запрос создаёт промежуточные итоги на каждом шаге иерархии, а также одну строку общего итога.

SELECT
  year,
  region,
  category,
  SUM(amount) AS total
FROM (
  VALUES
    (2024, 'North', 'Electronics', 1200),
    (2024, 'North', 'Clothing',     800),
    (2024, 'South', 'Electronics',  950),
    (2025, 'North', 'Electronics', 1400),
    (2025, 'South', 'Clothing',     700)
) AS t(year, region, category, amount)
GROUP BY ROLLUP(year, region, category)
ORDER BY year NULLS LAST, region NULLS LAST, category NULLS LAST;

Знакомство с CUBE

CUBE ещё более универсален, чем ROLLUP. Для n столбцов он создаёт все 2^n возможные комбинации наборов группировки, включая общий итог.

Для CUBE(region, category) создаются наборы: (region, category), (region), (category) и () — всего четыре набора. Это удобно, когда нужны итоги по всем сочетаниям измерений, а не только по одной иерархии.

-- CUBE(region, category) produces:
-- GROUPING SETS ( (region,category), (region), (category), () )

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY CUBE(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

CUBE и ROLLUP — когда что использовать

Выбирайте конструкцию в зависимости от того, образуют ли Ваши измерения естественную иерархию:

Используйте ROLLUP, если столбцы образуют иерархию, например «страна — город — магазин». Промежуточные итоги строятся только по одному пути иерархии.
Используйте CUBE, если столбцы являются независимыми измерениями, например регионом и категорией, и Вам нужны все возможные срезы.
Используйте GROUPING SETS, если нужен полный контроль и ни ROLLUP, ни CUBE не соответствуют Вашим точным требованиям.

Функция GROUPING()

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

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

SELECT
  region,
  category,
  SUM(amount)        AS total,
  GROUPING(region)   AS is_region_subtotal,
  GROUPING(category) AS is_category_subtotal
FROM sales
GROUP BY CUBE(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

Использование GROUPING() для обозначения строк

Распространённый приём — объединить GROUPING() с выражением CASE, чтобы выводить понятные человеку подписи вместо необработанных значений NULL. Благодаря этому результат запроса с ROLLUP или CUBE становится гораздо удобнее читать в отчётах.

SELECT
  CASE GROUPING(region)
    WHEN 1 THEN 'Grand Total'
    ELSE region
  END AS region_label,
  CASE GROUPING(category)
    WHEN 1 THEN 'All Categories'
    ELSE category
  END AS category_label,
  SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category)
ORDER BY GROUPING(region), region, GROUPING(category), category;

Сочетание фиксированных столбцов с ROLLUP

В одном предложении можно сочетать обычные столбцы GROUP BY с ROLLUP или CUBE. Столбцы, перечисленные за пределами ROLLUP(...), всегда входят в каждый набор группировки — они никогда не сворачиваются.

В приведённом ниже запросе year — фиксированный столбец группировки, а region и category участвуют в свёртке. Поэтому промежуточные итоги формируются для каждого года, а не для всех лет вместе.

SELECT
  2024           AS year,
  region,
  category,
  SUM(amount)    AS total
FROM sales
GROUP BY 2024, ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

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

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

Повторение — не только один GROUP BY

В этом уроке Вы изучили три мощных расширения SQL, которые позволяют получать несколько уровней агрегации в одном запросе:

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

Вы также узнали, что GROUPING() отличает настоящие значения NULL от строк-заполнителей сверхагрегации, а конструкции вида CASE GROUPING(...) создают аккуратный и понятный вывод для отчётов.

Эти инструменты незаменимы, когда нужны многомерные сводки: на них активно основаны отчёты в стиле сводных таблиц, информационные панели и запросы к хранилищам данных.

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

Урок «Не только один GROUP BY» бесплатный?

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

Чему я научусь в уроке «Не только один GROUP BY»?

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

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

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

Сколько времени занимает урок «Не только один GROUP BY»?

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

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

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

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

← Назад к SQL Academy