GROUPING: примеры (SQL)

Использование функции GROUPING в MS SQL Server на практике
Раздел: Агрегатные функции, Агрегатные
GROUPING(column_expression): int
типовые примерыGROUPING на mySQL, Oracle, PostgreSQL, SQLiteGROUPING изменения sqlпримеры расширенные

Функция GROUPING в MS SQL Server

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

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

Синтаксис:
GROUPING ( column_name | column_expression )

Аргументы:
column_name | column_expression — имя столбца или выражение, указанное в предложении GROUP BY. Функция проверяет, было ли это конкретное выражение агрегировано (свернуто в итог) в текущей результирующей строке.

Возвращаемое значение:
Тип данных tinyint (0 или 1).

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

Базовые примеры использования

Простой пример с ROLLUP для определения строк итогов.

SELECT
    Region,
    City,
    SUM(Sales) AS TotalSales,
    GROUPING(Region) AS GrpRegion,
    GROUPING(City) AS GrpCity
FROM SalesData
GROUP BY ROLLUP (Region, City)
ORDER BY Region, City;

Результат:

Region   | City     | TotalSales | GrpRegion | GrpCity
---------|----------|------------|-----------|--------
East     | Boston   | 1000       | 0         | 0
East     | New York | 1500       | 0         | 0
East     | NULL     | 2500       | 0         | 1  -- Подытог по региону East
West     | Seattle  | 1200       | 0         | 0
West     | NULL     | 1200       | 0         | 1  -- Подытог по региону West
NULL     | NULL     | 3700       | 1         | 1  -- Общий итог

Использование в выражении CASE для читаемой замены NULL.

SELECT
    CASE WHEN GROUPING(Region) = 1 THEN 'All Regions' ELSE Region END AS Region,
    CASE WHEN GROUPING(City) = 1 THEN 'All Cities' ELSE City END AS City,
    SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY ROLLUP (Region, City);

Похожие функции и операторы в MS SQL

Непосредственных функциональных аналогов GROUPING нет, но существуют связанные операторы и функции для формирования иерархических отчетов.

GROUPING_ID: Принимает список столбцов и возвращает битовую маску (целое число), которая кодирует комбинацию сгруппированных столбцов. Позволяет более компактно определить уровень агрегации для нескольких столбцов одновременно. Используется при сложных группировках с множеством GROUPING SETS.

SELECT
    Region, City, SUM(Sales),
    GROUPING_ID(Region, City) AS GrpID
FROM SalesData
GROUP BY ROLLUP (Region, City);

Операторы GROUP BY: ROLLUP, CUBE, GROUPING SETS — не являются функциями, но именно они создают строки, для идентификации которых нужна GROUPING. Без них функция возвращает 0 для всех строк.

Выбор инструмента: GROUPING применяется для проверки одного столбца. GROUPING_ID удобнее для анализа комбинации из нескольких столбцов или для сортировки результатов по уровням агрегации.

Типичные ошибки

1. Использование аргумента, не входящего в GROUP BY. Функция вызывает ошибку.

-- Ошибка: Column 'SomeColumn' is invalid in the GROUPING argument.
SELECT Region, SUM(Sales), GROUPING(SomeColumn)
FROM SalesData
GROUP BY ROLLUP(Region);

2. Попытка фильтрации по GROUPING в WHERE. Предложение WHERE выполняется до группировки, поэтому функция там недоступна. Для фильтрации итоговых строк используют HAVING.

-- Ошибка: An aggregate may not appear in the WHERE clause.
SELECT Region, City, SUM(Sales)
FROM SalesData
WHERE GROUPING(City) = 1
GROUP BY ROLLUP (Region, City);

-- Корректно через HAVING
SELECT Region, City, SUM(Sales)
FROM SalesData
GROUP BY ROLLUP (Region, City)
HAVING GROUPING(City) = 1; -- Покажет только подытоги и общий итог

3. Путаница между NULL от группировки и NULL из данных. Без функции GROUPING невозможно отличить эти случаи, что может привести к неверной интерпретации отчета.

Изменения в последних версиях

Функция GROUPING была введена в MS SQL Server 2005 вместе с поддержкой операторов ROLLUP, CUBE и GROUPING SETS. С тех пор ее синтаксис и поведение остаются стабильными и неизменными во всех последующих версиях, включая SQL Server 2019 и 2022.

Изменения касались в основном оптимизации производительности выполнения запросов с этими операторами, но не самой функции. Использование GROUPING полностью совместимо между версиями, начиная с 2005.

Расширенные примеры

1. Сортировка по уровням агрегации. Чтоб итоги выводились после детальных данных.

Пример sql
SELECT
    CASE WHEN GROUPING(Region)=1 THEN 'ZZ-TOTAL' ELSE Region END AS Region,
    City,
    SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY ROLLUP (Region, City)
ORDER BY GROUPING(Region), Region, GROUPING(City), City;

2. Фильтрация определенного уровня подытога. Например, оставить только строки с общим итогом и детальные данные, убрав промежуточные подытоги по регионам.

Пример sql
SELECT Region, City, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY ROLLUP (Region, City)
HAVING GROUPING(City) = 0 OR (GROUPING(Region)=1 AND GROUPING(City)=1);

3. Использование с GROUPING SETS. Определение, какая именно комбинация группировки применена.

Пример sql
SELECT
    Region, City, Category,
    SUM(Sales) AS TotalSales,
    GROUPING(Region) AS GrpR,
    GROUPING(City) AS GrpC,
    GROUPING(Category) AS GrpCat
FROM SalesData
GROUP BY GROUPING SETS (
    (Region, City),
    (Region, Category),
    (City),
    () -- общий итог
);

4. Динамическое формирование заголовка. Комбинирование нескольких полей в одном итоге.

Пример sql
SELECT
    CASE
        WHEN GROUPING(Region)=1 AND GROUPING(City)=1 THEN 'Grand Total'
        WHEN GROUPING(City)=1 THEN Region + ' Region Total'
        ELSE City + ', ' + Region
    END AS Description,
    SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY ROLLUP (Region, City);

5. Совместное использование GROUPING и GROUPING_ID. Для компактного кодирования уровня.

Пример sql
SELECT
    Region, City, SUM(Sales),
    GROUPING_ID(Region, City) AS GID,
    CASE GROUPING_ID(Region, City)
        WHEN 0 THEN 'Detail'
        WHEN 1 THEN 'Region Subtotal'
        WHEN 3 THEN 'Grand Total'
    END AS LevelName
FROM SalesData
GROUP BY ROLLUP (Region, City);

Аналоги в других СУБД и языках

Oracle: Имеет одноименную функцию GROUPING с идентичной семантикой. Синтаксис и использование совпадают.

-- Oracle
SELECT Region, City, SUM(Sales),
       GROUPING(Region) GrpReg
FROM SalesData
GROUP BY ROLLUP (Region, City);

PostgreSQL: Предлагает функцию GROUPING (начиная с версии 9.5) и аналогичные операторы ROLLUP, CUBE. Поведение аналогично стандарту SQL.

-- PostgreSQL
SELECT Region, City, SUM(Sales),
       GROUPING(Region, City) AS grp
FROM SalesData
GROUP BY ROLLUP (Region, City);

MySQL: Поддерживает оператор WITH ROLLUP, но не предоставляет функцию GROUPING. Для определения итоговых строк полагаются на проверку NULL в сгруппированных столбцах, что может привести к путанице, если в исходных данных есть настоящие NULL.

-- MySQL
SELECT Region, City, SUM(Sales)
FROM SalesData
GROUP BY Region, City WITH ROLLUP;
-- Итоговые строки помечаются NULL.

SQLite: Не поддерживает ни ROLLUP/CUBE, ни функцию GROUPING. Агрегация на нескольких уровнях требует выполнения нескольких отдельных запросов с UNION ALL.

Sybase ASE / SQL Anywhere: Поддерживают функцию GROUPING с поведением, аналогичным MS SQL.

Таким образом, функция GROUPING является частью стандарта SQL и реализована в большинстве крупных СУБД, кроме MySQL и SQLite.

MS SQL GROUPING function comments

En
GROUPING Indicates whether a specified column expression in a GROUP BY list is aggregated