CUBE в PostgreSQL: как считать итоги по всем комбинациям групп

Представьте обычный разговор аналитика и разработчика.

Бизнес просит отчёт:

«Покажи продажи по регионам, по продуктам, по каждой паре регион + продукт, а ещё общий итог по всей таблице».

Новичок часто решает такую задачу через несколько запросов:

• один SELECT для продаж по регионам;
• второй SELECT для продаж по продуктам;
• третий SELECT для продаж по региону и продукту;
• четвёртый SELECT для общего итога;
• всё склеить через UNION ALL.

Работать будет, но запрос быстро превратится в простыню. Таблица читается несколько раз, фильтры приходится дублировать, а если завтра нужно добавить условие по дате — его надо не забыть поправить во всех блоках.

В PostgreSQL для таких отчётов есть CUBE. Это расширение GROUP BY, которое считает агрегаты сразу по всем комбинациям выбранных столбцов.

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

Зачем нужен CUBE

Обычный GROUP BY считает агрегаты на одном уровне детализации.

Например, продажи по регионам:

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

Или продажи по продуктам:

SELECT
    product,
    SUM(amount) AS total
FROM sales
GROUP BY product;

Или продажи по паре регион + продукт:

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

Но если нужен отчёт сразу на всех этих уровнях, обычного GROUP BY уже мало. Приходится либо писать несколько запросов, либо использовать специальные возможности PostgreSQL.

CUBE как раз создан для таких ситуаций.

SELECT
    region,
    product,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product);

Этот один запрос посчитает:

• продажи по каждой паре region + product;
• итоги по каждому region;
• итоги по каждому product;
• общий итог по всей таблице.

То есть вместо четырёх отдельных запросов мы получаем один аккуратный запрос.

Что именно считает CUBE

Возьмём запрос:

SELECT
    region,
    product,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product);

GROUP BY CUBE (region, product) разворачивается в четыре группировки:

(region, product)
(region)
(product)
()

Разберём каждую.

Группировка:

(region, product)

даёт детальные ячейки: сколько продали каждого продукта в каждом регионе.

Группировка:

(region)

даёт итог по региону: сколько продали в регионе по всем продуктам.

Группировка:

(product)

даёт итог по продукту: сколько продали продукта во всех регионах.

Группировка:

()

означает общий итог по всей таблице.

Пустая группировка выглядит непривычно, но смысл простой: «не группируй ни по одному столбцу, просто посчитай один общий результат».

Пример результата

Допустим, в таблице sales есть такие данные:

region | product | amount
-------+---------+-------
North  | Phone   | 100
North  | Laptop  | 300
South  | Phone   | 200
South  | Laptop  | 400

Запрос:

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

может вернуть примерно такой результат:

region | product | total
-------+---------+------
North  | Laptop  | 300
North  | Phone   | 100
North  | NULL    | 400
South  | Laptop  | 400
South  | Phone   | 200
South  | NULL    | 600
NULL   | Laptop  | 700
NULL   | Phone   | 300
NULL   | NULL    | 1000

Строки с обычными значениями — это детальные итоги.

Строка:

North | NULL | 400

означает: все продукты в регионе North.

Строка:

NULL | Laptop | 700

означает: продукт Laptop во всех регионах.

Строка:

NULL | NULL | 1000

означает общий итог.

Вот ради таких отчётов CUBE и любят: одна команда сразу строит полную матрицу итогов.

Почему в итогах появляются NULL

Когда CUBE считает итог по региону, столбец product для этой строки уже не имеет конкретного значения. Это итог по всем продуктам.

PostgreSQL показывает такое свёрнутое измерение через NULL.

Например:

North | NULL | 400

Здесь NULL в product не означает, что продукт неизвестен. Он означает: «эта строка собрана по всем продуктам».

А вот тут:

NULL | Phone | 300

NULL в region означает: «эта строка собрана по всем регионам».

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

И тогда глазами уже непонятно: это настоящий NULL из данных или служебный NULL, который появился из-за подытога.

Для этого есть функция GROUPING().

GROUPING: как отличить настоящий NULL от итога

GROUPING(column) показывает, был ли столбец свёрнут в итоговой строке.

Она возвращает:

• 1, если столбец был свёрнут;
• 0, если столбец участвует в группировке как обычное значение.

Пример:

SELECT
    GROUPING(region) AS g_region,
    GROUPING(product) AS g_product,
    region,
    product,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
ORDER BY g_region, region, g_product, product;

Если g_product = 1, значит, строка посчитана по всем продуктам.

Если g_region = 1, значит, строка посчитана по всем регионам.

Такой отчёт полезен для проверки, но пользователю или менеджеру лучше показывать не флаги 0 и 1, а понятные подписи.

Делаем итоговые строки читаемыми

Вместо сырых NULL можно сразу вывести красивые подписи.

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN 'All regions'
        ELSE COALESCE(region, 'Unknown region')
    END AS region_label,
    CASE
        WHEN GROUPING(product) = 1 THEN 'All products'
        ELSE COALESCE(product, 'Unknown product')
    END AS product_label,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
ORDER BY
    GROUPING(region),
    region,
    GROUPING(product),
    product;

Здесь есть важная деталь.

Мы не пишем просто:

COALESCE(region, 'All regions')

Потому что так мы смешаем два разных случая:

• настоящий NULL в данных;
• итоговую строку по всем регионам.

Правильнее сначала проверить GROUPING(region).

Если GROUPING(region) = 1, это итог по всем регионам.

Если GROUPING(region) = 0, это обычное значение из данных, и только тогда можно заменить настоящий NULL на подпись Unknown region.

Так отчёт становится честным и понятным.

Фильтрация нужного среза через HAVING

Иногда CUBE считает много строк, а нам нужен только один тип итогов.

Например, хотим оставить только итоги по регионам: регион есть, продукт свёрнут.

Значит:

• region не свёрнут: GROUPING(region) = 0;
• product свёрнут: GROUPING(product) = 1.

Запрос:

SELECT
    region,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
HAVING GROUPING(region) = 0
   AND GROUPING(product) = 1;

Так мы получим только строки уровня «итог по региону».

Можно сделать наоборот — оставить только итоги по продуктам:

SELECT
    product,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
HAVING GROUPING(region) = 1
   AND GROUPING(product) = 0;

А можно оставить только общий итог:

SELECT
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
HAVING GROUPING(region) = 1
   AND GROUPING(product) = 1;

GROUPING() превращает большой куб в управляемый набор срезов.

CUBE с тремя столбцами

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

Но CUBE умеет работать и с большим количеством измерений.

Например:

SELECT
    region,
    product,
    channel,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product, channel);

Для трёх столбцов получится уже восемь группировок:

(region, product, channel)
(region, product)
(region, channel)
(product, channel)
(region)
(product)
(channel)
()

То есть PostgreSQL посчитает все возможные комбинации из трёх измерений.

Формула такая:

2 ^ n

Где n — количество столбцов внутри CUBE.

Для двух столбцов:

2 ^ 2 = 4

Для трёх:

2 ^ 3 = 8

Для четырёх:

2 ^ 4 = 16

Для пяти:

2 ^ 5 = 32

И вот здесь появляется важное ограничение: CUBE быстро разрастается. Чем больше столбцов, тем больше группировок и тем тяжелее запрос.

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

Реальный пример: отчёт по заказам

Допустим, у нас есть заказы и пользователи. Нужно посмотреть выручку по странам и статусам заказов.

SELECT
    CASE
        WHEN GROUPING(u.country) = 1 THEN 'All countries'
        ELSE COALESCE(u.country, 'Unknown country')
    END AS country_label,
    CASE
        WHEN GROUPING(o.status) = 1 THEN 'All statuses'
        ELSE o.status
    END AS status_label,
    COUNT(*) AS orders_count,
    SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY CUBE (u.country, o.status)
ORDER BY
    GROUPING(u.country),
    country_label,
    GROUPING(o.status),
    status_label;

Этот запрос сразу даст:

• выручку по каждой паре страна + статус;
• выручку по каждой стране;
• выручку по каждому статусу;
• общую выручку.

Например, в результате можно увидеть:

country_label | status_label | orders_count | revenue
--------------+--------------+--------------+--------
BR            | paid         | 120          | 54000
BR            | canceled     | 15           | 6000
BR            | All statuses | 135          | 60000
ES            | paid         | 80           | 42000
ES            | canceled     | 10           | 3000
ES            | All statuses | 90           | 45000
All countries | paid         | 200          | 96000
All countries | canceled     | 25           | 9000
All countries | All statuses | 225          | 105000

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

HR-пример: сотрудники по отделам и типу руководителя

CUBE полезен не только для продаж.

Допустим, есть таблица сотрудников:

id | dept    | manager_id | salary
---+---------+------------+-------
1  | Sales   | NULL       | 5000
2  | Sales   | 1          | 3000
3  | QA      | NULL       | 5500
4  | QA      | 3          | 3500

Хотим посчитать:

• численность и среднюю зарплату по отделу;
• численность и среднюю зарплату по признаку «есть руководитель или нет»;
• пересечение отдела и этого признака;
• общий итог по компании.

Можно написать так:

SELECT
    CASE
        WHEN GROUPING(dept) = 1 THEN 'All depts'
        ELSE COALESCE(dept, 'Unknown dept')
    END AS dept_label,
    CASE
        WHEN GROUPING(manager_id IS NULL) = 1 THEN 'All levels'
        WHEN manager_id IS NULL THEN 'Top level'
        ELSE 'Has manager'
    END AS level_label,
    COUNT(*) AS headcount,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY CUBE (dept, (manager_id IS NULL))
ORDER BY
    GROUPING(dept),
    dept_label,
    GROUPING(manager_id IS NULL),
    level_label;

Обратите внимание на выражение внутри CUBE:

(manager_id IS NULL)

Мы группируем не по самому manager_id, а по логическому признаку: есть руководитель или нет.

Это удобно. Вместо отдельных групп по каждому руководителю мы получаем понятные категории:

Top level
Has manager

Так один запрос строит полноценный матричный HR-отчёт.

CUBE вместо UNION ALL

Без CUBE отчёт по регионам и продуктам пришлось бы писать примерно так:

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

UNION ALL

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

UNION ALL

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

UNION ALL

SELECT NULL AS region, NULL AS product, SUM(amount) AS total
FROM sales;

Это длинно и неудобно.

Проблема даже не только в длине. Представьте, что нужно добавить фильтр:

WHERE created_at >= DATE '2026-01-01'

Его придётся добавить во все четыре SELECT. Если в одном месте забыть — отчёт станет неправильным.

С CUBE запрос короче:

SELECT
    region,
    product,
    SUM(amount) AS total
FROM sales
WHERE created_at >= DATE '2026-01-01'
GROUP BY CUBE (region, product);

Фильтр написан один раз. Логика агрегации написана один раз. Ошибиться сложнее.

CUBE, ROLLUP и GROUPING SETS

В PostgreSQL рядом с CUBE часто встречаются ещё две конструкции: ROLLUP и GROUPING SETS.

Все они расширяют GROUP BY, но решают немного разные задачи.

CUBE считает все комбинации.

GROUP BY CUBE (region, product)

Это то же самое, что:

GROUP BY GROUPING SETS (
    (region, product),
    (region),
    (product),
    ()
)

ROLLUP считает иерархию слева направо.

GROUP BY ROLLUP (year, month, day)

Это похоже на:

GROUP BY GROUPING SETS (
    (year, month, day),
    (year, month),
    (year),
    ()
)

То есть ROLLUP хорошо подходит для естественных иерархий: год → месяц → день, страна → город → район, категория → подкатегория → товар.

А GROUPING SETS позволяет явно перечислить только нужные группировки:

GROUP BY GROUPING SETS (
    (region, product),
    (product),
    ()
)

Здесь мы просим:

• детализацию по региону и продукту;
• итоги по продукту;
• общий итог.

Но не просим итоги по региону.

Чем CUBE отличается от ROLLUP

Разница между CUBE и ROLLUP особенно хорошо видна на двух столбцах.

GROUP BY CUBE (region, product)

даёт:

(region, product)
(region)
(product)
()

А:

GROUP BY ROLLUP (region, product)

даёт:

(region, product)
(region)
()

ROLLUP не даст отдельный итог по product, потому что он двигается по иерархии.

Для ROLLUP порядок столбцов важен. Запись:

GROUP BY ROLLUP (region, product)

означает: сначала детализация по региону и продукту, потом итог по региону, потом общий итог.

А CUBE порядок использует меньше: ему нужны все комбинации.

Запомнить можно так:

ROLLUP — лестница итогов.

CUBE — полная матрица итогов.

GROUPING SETS — ручной список нужных итогов.

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

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

Например:

• регион и продукт;
• страна и статус заказа;
• отдел и тип сотрудника;
• канал продаж и тариф;
• платформа и тип события.

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

SELECT
    country,
    status,
    SUM(amount) AS revenue
FROM orders
GROUP BY CUBE (country, status);

Это хороший сценарий для CUBE.

Когда лучше не использовать CUBE

CUBE не стоит использовать просто потому, что он красивый.

Если вам нужна строгая иерархия, лучше взять ROLLUP.

Например:

GROUP BY ROLLUP (year, month, day)

Здесь отдельный итог по day без года и месяца обычно не имеет смысла. День 15 сам по себе бесполезен: непонятно, какого месяца и года.

А CUBE (year, month, day) посчитает в том числе такие странные комбинации:

(day)
(month)
(month, day)

Для календарной иерархии это часто лишний шум.

Если вам нужны только некоторые срезы, лучше явно написать GROUPING SETS.

GROUP BY GROUPING SETS (
    (year, month),
    (year),
    ()
)

Так запрос будет понятнее и дешевле.

Производительность: почему CUBE может быть тяжёлым

CUBE удобен, но не бесплатен.

На n столбцов он создаёт 2^n группировок. Это быстро растёт.

2 columns -> 4 grouping sets
3 columns -> 8 grouping sets
4 columns -> 16 grouping sets
5 columns -> 32 grouping sets

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

Поэтому практическое правило такое: не кладите в CUBE всё подряд.

Лучше сначала спросить себя:

• какие срезы реально нужны пользователю отчёта;
• есть ли естественная иерархия, где лучше подойдёт ROLLUP;
• можно ли перечислить только нужные уровни через GROUPING SETS;
• достаточно ли предварительно отфильтровать данные в WHERE.

Например, лучше так:

SELECT
    region,
    product,
    SUM(amount) AS total
FROM sales
WHERE created_at >= DATE '2026-01-01'
GROUP BY CUBE (region, product);

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

Сортировка итоговых строк

Сортировка результатов с CUBE требует внимания, потому что в итоговых строках появляются NULL.

Можно сортировать через GROUPING():

SELECT
    region,
    product,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product)
ORDER BY
    GROUPING(region),
    region,
    GROUPING(product),
    product;

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

Если вы выводите подписи через CASE, можно сортировать по тем же флагам, а не только по тексту. Иначе строка All regions может оказаться в неожиданном месте просто из-за алфавитного порядка.

Диалекты: PostgreSQL, MySQL и ClickHouse

В PostgreSQL есть CUBE, ROLLUP, GROUPING SETS и GROUPING(). Это мощный набор для многоуровневых отчётов.

В MySQL ситуация другая. Там есть GROUP BY ... WITH ROLLUP, но нет полноценного CUBE и явного GROUPING SETS в стиле PostgreSQL. Если нужна полная матрица итогов, часто приходится собирать её вручную через UNION ALL или переносить часть логики в отчётный слой.

В ClickHouse есть похожая возможность через модификатор WITH CUBE.

Например:

SELECT
    region,
    product,
    SUM(amount) AS total
FROM sales
GROUP BY region, product
WITH CUBE;

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

Частые ошибки с CUBE

Первая ошибка — не отличать итоговый NULL от настоящего NULL.

Плохо:

SELECT
    COALESCE(region, 'All regions') AS region_label,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region);

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

Лучше:

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN 'All regions'
        ELSE COALESCE(region, 'Unknown region')
    END AS region_label,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region);

Вторая ошибка — использовать CUBE там, где нужен ROLLUP.

Если у вас год → месяц → день, полная матрица часто не нужна. Нужна иерархия.

Третья ошибка — добавлять слишком много столбцов.

GROUP BY CUBE (a, b, c, d, e)

Это уже 32 группировки. Иногда это оправданно, но чаще стоит остановиться и выписать нужные срезы через GROUPING SETS.

Четвёртая ошибка — забыть одинаковые фильтры при замене старого UNION ALL.

Если вы переписываете отчёт на CUBE, проверьте, что WHERE, JOIN и условия периода совпадают с исходной логикой.

Практический шаблон для отчёта

Вот хороший шаблон, от которого можно отталкиваться:

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN 'All regions'
        ELSE COALESCE(region, 'Unknown region')
    END AS region_label,
    CASE
        WHEN GROUPING(product) = 1 THEN 'All products'
        ELSE COALESCE(product, 'Unknown product')
    END AS product_label,
    COUNT(*) AS rows_count,
    SUM(amount) AS total_amount
FROM sales
WHERE created_at >= DATE '2026-01-01'
GROUP BY CUBE (region, product)
ORDER BY
    GROUPING(region),
    region_label,
    GROUPING(product),
    product_label;

В нём есть всё важное:

• фильтр по периоду;
• CUBE по двум измерениям;
• понятные подписи для итогов;
• защита от настоящих NULL;
• предсказуемая сортировка;
• несколько агрегатов сразу.

Такой запрос уже похож не на учебный пример, а на нормальный отчёт для бизнеса.

Главное из статьи

CUBE в PostgreSQL — это расширение GROUP BY, которое считает агрегаты по всем комбинациям указанных столбцов.

SELECT
    region,
    product,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, product);

Для двух столбцов CUBE создаёт четыре группировки:

(region, product)
(region)
(product)
()

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

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

SELECT
    GROUPING(region) AS g_region,
    region,
    SUM(amount) AS total
FROM sales
GROUP BY CUBE (region);

CUBE стоит использовать, когда вам нужна полная матрица отчёта по независимым измерениям. Если нужна иерархия, чаще подходит ROLLUP. Если нужны только отдельные срезы, лучше явно написать GROUPING SETS.

Главное помнить про масштаб: на n столбцов CUBE создаёт 2^n группировок. На двух-трёх измерениях это удобно и читаемо, а на пяти-шести уже может стать тяжёлым отчётом, который лучше проектировать аккуратнее.