SQL Academy · Урок

ROLLUP для промежуточных итогов

Рассчитывайте промежуточные и общие итоги

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

Что такое ROLLUP?

При группировке данных с помощью GROUP BY Вы получаете одну строку для каждой группы. Однако часто нужны также промежуточные итоги для каждой группы и общий итог по всем строкам. Именно это делает ROLLUP.

ROLLUP — это расширение GROUP BY, которое автоматически добавляет в набор результатов дополнительные сводные строки: промежуточные итоги на каждом уровне иерархии группировки и общий итог в конце.

Подготовка исходных данных

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

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

INSERT INTO sales VALUES
  ('East',  'Electronics', 500),
  ('East',  'Electronics', 300),
  ('East',  'Clothing',    150),
  ('West',  'Electronics', 700),
  ('West',  'Clothing',    200),
  ('West',  'Clothing',    100);

Сначала — обычный GROUP BY

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

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

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

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

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

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

Результат ROLLUP содержит три вида строк:

Строки с деталями — по одной строке для каждого сочетания (регион, категория), как и при обычном GROUP BY.
Строки промежуточных итогов — по одной строке для каждого региона со значением category = NULL, содержащей итог по региону.
Строка общего итога — одна строка, в которой и region, и category имеют значение NULL, содержащая общий итог.

Значения NULL выступают в роли заполнителей и обозначают сводный уровень.

Использование COALESCE для обозначения значений NULL

Маркеры NULL могут сбивать конечных пользователей с толку. Оберните каждый столбец в COALESCE, чтобы заменить их понятными подписями, например 'Все категории' и 'Общий итог'.

SELECT
  COALESCE(region,   'Grand Total')    AS region,
  COALESCE(category, 'All Categories') AS category,
  SUM(amount)                          AS total
FROM   sales
GROUP  BY ROLLUP(region, category)
ORDER  BY region, category;

ROLLUP с одним столбцом

ROLLUP работает и всего с одним столбцом. Если написать ROLLUP(region), Вы получите по одной строке для каждого региона и одну строку общего итога в конце. Это самая простая форма, удобная для любого отчёта, в котором под строками с деталями должен располагаться итог.

SELECT
  COALESCE(region, 'Grand Total') AS region,
  SUM(amount)                     AS total
FROM   sales
GROUP  BY ROLLUP(region)
ORDER  BY region;

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

В ROLLUP можно передать три или больше столбцов, чтобы построить более глубокую иерархию. Конструкция ROLLUP(year, region, category) даст промежуточные итоги для каждой пары год+регион, итоги для каждого года и общий итог — каждый дополнительный столбец добавляет ещё один уровень свёртки.

-- Extend the demo with a year column
SELECT
  COALESCE(CAST(yr AS TEXT), 'All Years')     AS year,
  COALESCE(region,   'All Regions')            AS region,
  COALESCE(category, 'All Categories')         AS category,
  SUM(amount)                                  AS total
FROM (
  SELECT 2024 AS yr, region, category, amount FROM sales
  UNION ALL
  SELECT 2025 AS yr, region, category, amount FROM sales
) t
GROUP  BY ROLLUP(yr, region, category)
ORDER  BY yr, region, category;

Определение сводных строк с помощью GROUPING()

Функция GROUPING() возвращает 1, если значение столбца в строке является значением NULL, созданным ROLLUP в качестве заполнителя сводки, и 0, если это настоящее значение данных. Это позволяет отличить настоящие значения NULL в данных от значений NULL в сводных строках ROLLUP.

SELECT
  region,
  category,
  SUM(amount)        AS total,
  GROUPING(region)   AS is_region_summary,
  GROUPING(category) AS is_cat_summary
FROM   sales
GROUP  BY ROLLUP(region, category)
ORDER  BY region, category;

Фильтрация сводных строк

Поскольку ROLLUP добавляет строки после агрегации, отфильтровать их с помощью WHERE нельзя. Используйте HAVING или заключите весь запрос во вложенный запрос. В приведённом ниже примере сохраняются только промежуточные итоги по регионам — строки, где category является значением NULL, созданным ROLLUP, а region содержит настоящее значение.

SELECT region, SUM(amount) AS regional_total
FROM   sales
GROUP  BY ROLLUP(region, category)
HAVING GROUPING(category) = 1
   AND GROUPING(region)   = 0
ORDER  BY region;

ROLLUP и GROUPING SETS

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

-- Using ROLLUP (concise)
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),
  ()
);

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

Проверьте свои знания о ROLLUP с помощью этого вопроса.

Повторение урока

Отличная работа! Ниже приведено краткое изложение того, что Вы узнали о ROLLUP:

GROUP BY ROLLUP(a, b) автоматически создаёт промежуточные итоги для каждого уровня иерархии столбцов и общий итог по всем строкам.
Сводные строки отмечаются значением NULL в свёрнутых столбцах. Используйте COALESCE, чтобы присвоить им понятные подписи.
Функция GROUPING() возвращает 1 для значений NULL, созданных ROLLUP, и 0 для настоящих значений данных, поэтому их можно отличить друг от друга.
Используйте HAVING GROUPING(...) = 1, чтобы отфильтровать только сводные строки.
ROLLUP(a, b) — это сокращённая запись GROUPING SETS((a, b), (a), ()).

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

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

Урок «ROLLUP для промежуточных итогов» бесплатный?

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

Чему я научусь в уроке «ROLLUP для промежуточных итогов»?

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

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

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

Сколько времени занимает урок «ROLLUP для промежуточных итогов»?

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

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

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

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

← Назад к SQL Academy