0Pricing
SQL Academy · Урок

CUBE для всех сочетаний

Получайте все сочетания группировки одновременно

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

Что такое CUBE?

Расширение CUBE в SQL создаёт все возможные сочетания группировки для списка столбцов. Если ROLLUP формирует иерархию, то CUBE создаёт все возможные сочетания промежуточных итогов, включая общий итог.

Представьте, что Вы просите: «Покажите все промежуточные итоги, которые можно вычислить по этим столбцам».

Подготовка таблицы

Мы будем использовать таблицу sales, в которой хранится выручка по годам, регионам и категориям товаров. Именно на таких многомерных данных CUBE особенно полезен.

CREATE TABLE sales (
  year     INT,
  region   VARCHAR(20),
  category VARCHAR(20),
  revenue  NUMERIC(10,2)
);

INSERT INTO sales VALUES
  (2023, 'North', 'Electronics', 12000),
  (2023, 'North', 'Clothing',     8000),
  (2023, 'South', 'Electronics',  9500),
  (2023, 'South', 'Clothing',     6000),
  (2024, 'North', 'Electronics', 14000),
  (2024, 'North', 'Clothing',     9500),
  (2024, 'South', 'Electronics', 11000),
  (2024, 'South', 'Clothing',     7200);

Ваш первый запрос с CUBE

Синтаксис прост: замените GROUP BY на GROUP BY CUBE(...) и перечислите внутри столбцы. SQL создаст все сочетания этих столбцов в виде наборов группировки.

SELECT
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

Сколько группировок создаёт CUBE?

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

Для 3 столбцов (год, регион, категория) это 2³ = 8 наборов группировки: каждый столбец по отдельности, каждая пара, все три вместе и ни одного столбца.

-- 3 columns => 8 grouping sets:
-- (year, region, category)
-- (year, region)
-- (year, category)
-- (region, category)
-- (year)
-- (region)
-- (category)
-- () <- grand total
SELECT COUNT(*) AS row_count
FROM (
  SELECT year, region, category, SUM(revenue)
  FROM sales
  GROUP BY CUBE(year, region, category)
) sub;

Значения NULL обозначают свёрнутое измерение

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

Используйте GROUPING(col), чтобы отличить настоящее значение NULL от маркера агрегации.

SELECT
  GROUPING(year)     AS g_year,
  GROUPING(region)   AS g_region,
  GROUPING(category) AS g_category,
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY g_year, g_region, g_category;

Понятные значения NULL с помощью COALESCE

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

SELECT
  COALESCE(CAST(year AS VARCHAR), 'ALL YEARS')     AS year,
  COALESCE(region,   'ALL REGIONS')                AS region,
  COALESCE(category, 'ALL CATEGORIES')             AS category,
  SUM(revenue)                                     AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

CUBE и ROLLUP — ключевое различие

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

CUBE(a, b, c) создаёт каждое подмножество, включая такие срезы, как (a,c) или один лишь (b), которые ROLLUP полностью пропускает. Используйте CUBE, когда нужен полноценный многомерный анализ без заранее заданной иерархии.

-- ROLLUP: 4 grouping sets
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, region);

-- CUBE: 4 grouping sets for 2 columns (same count here)
-- but adds the (region) subtotal that ROLLUP omits
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY CUBE(year, region);

Частичный CUBE

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

-- year is fixed; CUBE only over region and category
SELECT
  year,
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY year, CUBE(region, category)
ORDER BY year, region, category;

Фильтрация результатов CUBE с помощью HAVING

HAVING работает с результатом CUBE точно так же, как обычный GROUP BY. Можно отфильтровать строки группировки, агрегатное значение которых не достигает порогового значения, например оставить только строки, в которых общий доход превышает заданный минимум.

SELECT
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(region, category)
HAVING SUM(revenue) > 15000
ORDER BY total_revenue DESC;

Сочетание CUBE с оконными функциями

Запрос с CUBE можно обернуть в CTE, а затем применить оконные функции, чтобы ранжировать строки результата или сравнивать их между собой. Это мощный шаблон для построения панелей мониторинга для руководителей.

WITH cube_result AS (
  SELECT
    COALESCE(region,   'ALL') AS region,
    COALESCE(category, 'ALL') AS category,
    SUM(revenue) AS total_revenue
  FROM sales
  GROUP BY CUBE(region, category)
)
SELECT
  region,
  category,
  total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM cube_result
ORDER BY revenue_rank;

Когда выбирать CUBE

CUBE подходит, когда:

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

Избегайте CUBE, когда количество столбцов велико: уже 4 столбца дают 16 наборов группировки, а 5 — 32. Предпочитайте ROLLUP или явные GROUPING SETS, чтобы результирующие наборы оставались удобными для работы.

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

Сколько различных наборов группировки создаёт GROUP BY CUBE(a, b, c, d)?

Итоги урока

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

Основные выводы:

  • GROUP BY CUBE(a, b, c) создаёт 2n наборов группировки.
  • NULL в столбце результата означает, что по этому измерению выполнена агрегация; для его обнаружения используйте GROUPING().
  • COALESCE преобразует значения агрегации NULL в понятные подписи.
  • Частичный CUBE (например, GROUP BY year, CUBE(region, category)) фиксирует некоторые столбцы и применяет куб только к остальным.
  • Используйте CUBE для полного нерегламентированного анализа; предпочитайте ROLLUP или явные GROUPING SETS, когда иерархия известна или количество столбцов велико.

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

Урок «CUBE для всех сочетаний» бесплатный?

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

Чему я научусь в уроке «CUBE для всех сочетаний»?

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

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

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

Сколько времени занимает урок «CUBE для всех сочетаний»?

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

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

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

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

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