ROLLUP, CUBE и GROUPING SETS в SQL: подытоги в одном запросе

Короткий ответ: какой оператор нужен

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

Функция GROUPING(status) равна 1, если поле свёрнуто в текущем уровне, и 0 для обычной группы — даже если значение обычной группы NULL. Без этой проверки COALESCE(status, 'Итого') смешает отсутствие статуса в исходных данных с итогом. Опорная карта продвинутого SQL показывает, где подытоги стоят среди других приёмов.

Чтобы выбрать оператор, нарисуйте будущий отчёт на бумаге. Нужны только строки «платформа × статус» — достаточно обычного GROUP BY. Нужны те же строки плюс итог каждой платформы и общий итог — ROLLUP. Нужны ещё суммы каждого статуса через платформы — CUBE. Когда уровни не складываются в такую иерархию, перечислите их в GROUPING SETS. Этот порядок уберегает от привычки применять CUBE наугад и потом удалять половину вывода.

Зерно исходных строк и период

Берём app_events с 1 июля включительно до 4 июля исключительно, только search и purchase. Строка входа — событие, включая повторные доставки; count(*) считает события, count(DISTINCT client_id) — разных клиентов. Статус member означает заполненный member_id; NULL означает, что связи с программой лояльности нет в этой строке. Это не утверждение, что человек никогда не вступал в программу.

В периоде 21 049 событий от 11 366 клиентов. На android — 9 250 событий от 5 018 клиентов. На этой платформе 3 267 событий имеют статус member, а 5 983 — настоящий NULL. Наличие этого NULL проверяет, правильно ли мы подписали итог.

Срез по типу события выбран до группировки. Если забыть event_name IN (...), подытоги будут математически согласованы, но ответят на другой вопрос: в журнале есть открытия, ошибки и другие действия. Проверка общего итога против самостоятельного count(*) на отфильтрованном входе особенно полезна для подытогов: она сразу выявляет, что один из фильтров применён лишь к части отчёта.

ROLLUP: уровни и честная подпись NULL

CTE задаёт ровно две оси. В SELECT сначала проверяем GROUPING и только затем настоящий NULL. ORDER BY ставит детали до подытога платформы, а общий итог — последним. Получаются десять строк: по две детали и один подытог для каждой из трёх платформ плюс общий итог. В отчёте можно показывать текстовые подписи, но для контроля сохраняйте флаги gp и gs.

Итог 11 366 клиентов не равен сумме клиентов по статусам и платформам. Один человек мог сначала искать без member_id, затем покупать с ним. События суммируются, уникальные клиенты через уровни — нет.

На одном android сумма детальных событий равна подытогу: 3 267 + 5 983 = 9 250. Но детальные клиенты дают 1 784 + 3 389 = 5 173, что на 155 выше числа людей на платформе. Запрос намеренно держит count(*) и count(DISTINCT) рядом, чтобы различие аддитивной и неаддитивной метрики было видно в той же строке результата, а не только в сноске к дашборду.

WITH e AS (
  SELECT platform, CASE WHEN member_id IS NULL THEN NULL ELSE 'member' END AS status, client_id
  FROM app_events
  WHERE event_name IN ('search', 'purchase')
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-04'
)
SELECT CASE WHEN GROUPING(platform)=1 THEN 'все платформы' ELSE platform END AS platform_name,
       CASE WHEN GROUPING(status)=1 THEN 'все статусы'
            WHEN status IS NULL THEN 'без member_id' ELSE status END AS status_name,
       GROUPING(platform) AS gp, GROUPING(status) AS gs,
       count(*) AS events, count(DISTINCT client_id) AS clients
FROM e GROUP BY ROLLUP(platform, status)
ORDER BY GROUPING(platform), platform, GROUPING(status), status NULLS LAST;

CUBE: чего не было у ROLLUP

У CUBE появляются срезы по статусу без платформы. Их здесь два: member — 7 554 события, NULL из данных — 13 495. Плюс общий итог 21 049. Эти строки полезны, если нужно одновременно сравнить платформы и статус; для одной иерархии они лишь удлиняют таблицу. Две колонки дают четыре набора группировки, пять колонок — уже 32 набора ещё до числа групп внутри них.

Важно не читать строку platform=NULL, status=NULL как единственную: одна такая строка может быть общим итогом, а другая — агрегатом настоящих NULL. Флаги GROUPING показывают уровень без догадок по значению.

Подстановка текстовой метки после расчёта — вопрос отображения. Если заменить NULL в исходных строках строкой «все статусы» до GROUP BY, появится новое значение данных, которое невозможно отделить от итога по одной подписи. Храните флаги уровня до конца расчёта, особенно когда результат экспортируется в BI: там фильтр по «все статусы» иначе легко захватит и детали, и итоги.

WITH e AS (
  SELECT platform, CASE WHEN member_id IS NULL THEN NULL ELSE 'member' END AS status, client_id
  FROM app_events
  WHERE event_name IN ('search', 'purchase')
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-04'
)
SELECT GROUPING(platform) AS gp, GROUPING(status) AS gs, status,
       count(*) AS events, count(DISTINCT client_id) AS clients
FROM e GROUP BY CUBE(platform, status)
HAVING GROUPING(platform)=1
ORDER BY GROUPING(status), status NULLS LAST;

GROUPING SETS: только нужные уровни

Если отчёту не нужны детали «платформа × статус», перечислите (platform), (status) и () явно. Получатся три платформы, два статуса и общий итог — шесть строк. Это понятнее, чем строить CUBE и затем фильтровать лишние уровни по битам. Тем же способом можно добавить нестандартный срез, который ROLLUP не выражает как простой префикс.

GROUPING SETS удобно проверять как независимые запросы GROUP BY, объединённые UNION ALL: состав строк должен совпасть. Но при ручном UNION нужно самому привести подписи и типы NULL, а добавление нового измерения повторит исходный фильтр в каждом запросе.

Для сверки достаточно сравнить не только общий итог, но и размерность вывода. Здесь набор (platform) даёт три строки, (status) — две, пустой набор — одну. Итого шесть. Если вышло больше, проверьте неожиданные значения категорий; если меньше, возможно, NULL-группа потеряна из-за фильтра или джойна. Такая арифметика уровней не доказывает правильность каждой метрики, но быстро показывает неверную форму отчёта.

WITH e AS (
  SELECT platform, CASE WHEN member_id IS NULL THEN NULL ELSE 'member' END AS status, client_id
  FROM app_events
  WHERE event_name IN ('search', 'purchase')
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-04'
)
SELECT GROUPING(platform) AS gp, GROUPING(status) AS gs, platform, status,
       count(*) AS events, count(DISTINCT client_id) AS clients
FROM e GROUP BY GROUPING SETS ((platform), (status), ())
ORDER BY gp, platform, gs, status NULLS LAST;

То же в pandas: groupby и margins

В ноутбуке можно собрать несколько уровней groupby и склеить их concat; pivot_table(margins=True) добавит строку и столбец All. У SQL строки итогов получают NULL вместо свёрнутого поля, у pandas здесь текст All. Перед pivot заполняем настоящий NULL отдельной меткой без member_id, иначе группа может исчезнуть из таблицы.

По pandas-сводке android/без member_id — 5 983 событий, общий итог — 21 049. Это те же численности событий, что SQL. Для разных клиентов снова нужен отдельный nunique() на каждом уровне: сумма групп 1 784 + 3 389 = 5 173 не равна 5 018 уникальным клиентам android.

Строка All в pivot_table — результат расчёта, а не значение исходной платформы. При объединении pandas-таблицы с SQL-отчётом не сравнивайте подписи буквально: в одном выводе стоит All, в другом флаг GROUPING и, возможно, пользовательская подпись. Сначала сопоставьте уровни, затем значения. Это избавляет от ложного несходства, которое возникает лишь из-за разных представлений итога.

import pandas as pd

e = pd.read_parquet('app_events.parquet', columns=['event_time', 'event_name', 'platform', 'member_id', 'client_id'])
e['event_time'] = pd.to_datetime(e['event_time'])
start, end = pd.Timestamp('2026-07-01'), pd.Timestamp('2026-07-04')
e = e.loc[e.event_name.isin(['search', 'purchase']) & e.event_time.ge(start) & e.event_time.lt(end)].copy()
e['status'] = e.member_id.notna().map({True: 'member', False: 'без member_id'})
detail = e.groupby(['platform', 'status']).agg(events=('client_id', 'size'), clients=('client_id', 'nunique'))
platform = e.groupby('platform').agg(events=('client_id', 'size'), clients=('client_id', 'nunique'))
wide = pd.pivot_table(e, values='client_id', index='platform', columns='status', aggfunc='count', margins=True)
print(int(detail.loc[('android', 'без member_id'), 'events']), int(platform.loc['android', 'clients']))
print(int(wide.loc['All', 'All']), wide.index[-1], wide.columns[-1])

Ловушка 1: NULL подписали «Итого» через COALESCE

Неправильный фрагмент COALESCE(status, 'Итого') даст android две одинаковые подписи: 5 983 событий без member_id и 9 250 в подытоге. Исправление — CASE WHEN GROUPING(status)=1 THEN 'Итого' WHEN status IS NULL THEN 'без member_id' .... Это различие видно только если исходные данные действительно содержат NULL.

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

Ловушка 2: уникальных клиентов сложили по группам

Для android 1 784 клиента в группе member и 3 389 в группе без member_id. Их арифметическая сумма 5 173, но подытог count(DISTINCT client_id) — 5 018: 155 клиентов встретились в обоих состояниях. Исправление — считать DISTINCT на уровне подытога, а не суммировать готовые групповые числа.

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

Ловушка 3: порядок колонок в ROLLUP поменяли

ROLLUP(status, platform) создаст подытоги по статусам, а не по платформам. Исходные 21 049 событий останутся верны, но строки 9 250/6 274/5 525 исчезнут из результата и появятся 7 554/13 495. Исправление — ставить первым внешний уровень иерархии, если нужен подытог по нему.

Когда обе оси равноправны, проще CUBE или явный GROUPING SETS. Не полагайтесь на название оператора: выпишите получающиеся наборы скобками до запуска.

Ловушка 4: CUBE на все измерения без оценки размера

Два поля дают четыре группировки и 12 строк здесь. Пять полей дают 32 комбинации наборов группировки; у каждого набора может быть много групп. Неправильно добавлять поле в CUBE как обычную деталь: стоимость и объём результата растут быстрее, чем число полей.

Исправление — GROUPING SETS с нужными разрезами и контроль числа строк. В BI-экспорте отдельная таблица итогов часто яснее бесконечного списка комбинаций.

Ловушка 5: неполная неделя выглядит как падение

Журнал начинается 1 июля, а срез данных сделан 11 августа в 18:00. Если написать date_trunc('week', event_time) и сравнивать все недели подряд, первая и последняя будут неполными. Их меньшие суммы не доказывают падения спроса.

В нашем запросе период 1–3 июля намеренно назван трёхдневным, а не неделей. Для недельного отчёта явно ограничьте полные интервалы, например понедельник 6 июля включительно и понедельник 10 августа исключительно. Разбор рядов дат помогает не смешать закрытую и открытую границы.

Когда UNION ALL проще

Если уровней всего два и у них разные источники либо разные правила фильтрации, два самостоятельных SELECT с UNION ALL могут быть понятнее одного сложного CUBE. Сверьте, что оба возвращают одинаковые колонки и один и тот же тип подписи; итог нельзя снова агрегировать вместе с деталями, иначе 21 049 превратятся в 42 098.

Если фильтры одинаковы, ROLLUP уменьшает дублирование условий. Но это не гарантия одного физического прохода для любой СУБД и плана: оптимизацию смотрят через EXPLAIN, а не выводят из синтаксиса.

Сравнивая варианты, не ограничивайтесь временем одного запуска. Зафиксируйте одинаковый период, одинаковый фильтр событий и одинаковые определения count(DISTINCT client_id). Затем сравните число строк каждого уровня и контрольный итог 21 049 событий. Если UNION ALL собирает детали и общий итог, он не должен повторно агрегировать уже сгруппированные строки, иначе одно событие попадёт в результат дважды. Только после проверки семантики имеет смысл мерить план и скорость.

Частые вопросы

Чем ROLLUP отличается от CUBE? ROLLUP даёт префиксные подытоги в порядке колонок; CUBE — все комбинации уровней. Для двух полей это три против четырёх наборов группировки.

Почему в строке итога NULL? Поле в этом уровне свёрнуто. GROUPING(field)=1 отличает это от настоящего NULL в исходной группе.

Можно ли сложить count(distinct)? Только если группы гарантированно не пересекаются по клиентам. Здесь на android пересечение 155 клиентов.

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