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

Функция GROUPING_ID в SQL Server для работы с итогами
Раздел: Агрегатные функции, Агрегатные
GROUPING_ID(column_expression [, n]): int
типовые примерыGROUPING ID на mySQL, Oracle, PostgreSQL, SQLiteGROUPING ID изменения sqlпримеры расширенные

Описание функции GROUPING_ID

Функция GROUPING_ID в MS SQL Server является вспомогательным инструментом для работы с операторами GROUP BY ROLLUP, CUBE или GROUPING SETS. Она вычисляет числовой идентификатор, который указывает на уровень агрегации для каждой строки в результирующем наборе данных.

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

Аргументы функции: GROUPING_ID ( column_name [ ,...n ] ). В качестве аргументов передаются столбцы из списка GROUP BY в том же порядке, в котором они указаны в предложении. Количество аргументов может быть от одного до нескольких.

Возвращаемое значение: Функция возвращает целое число типа int. Это число является битовой маской (битовой картой), где каждый бит соответствует одному аргументу. Бит равен 1, если столбец в текущей строке является итоговым (т.е. для него использовалась агрегация, и в строке присутствует значение NULL, представляющее собой итог). Бит равен 0, если столбец участвует в группировке на текущем уровне. Значение функции вычисляется по формуле: GROUPING_ID(c1, c2, ..., cn) = GROUPING(c1) * 2^(n-1) + GROUPING(c2) * 2^(n-2) + ... + GROUPING(cn) * 2^0.

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

Простой пример с одним столбцом группировки.

SELECT 
    DepartmentID,
    SUM(Salary) AS TotalSalary,
    GROUPING_ID(DepartmentID) AS GrpID
FROM Employees
GROUP BY ROLLUP(DepartmentID);
DepartmentID  TotalSalary  GrpID
------------  -----------  -----
1             50000        0
2             75000        0
3             60000        0
NULL          185000       1

Пример с двумя столбцами и оператором CUBE.

SELECT 
    DepartmentID,
    JobTitle,
    SUM(Salary) AS TotalSalary,
    GROUPING_ID(DepartmentID, JobTitle) AS GrpID
FROM Employees
GROUP BY CUBE(DepartmentID, JobTitle);
DepartmentID  JobTitle    TotalSalary  GrpID
------------  ----------  -----------  -----
1             Manager     30000        0
1             Engineer    20000        0
2             Manager     40000        0
2             Engineer    35000        0
NULL          Manager     70000        1
NULL          Engineer    55000        1
1             NULL        50000        2
2             NULL        75000        2
NULL          NULL        125000       3

Похожие функции в MS SQL

В MS SQL существует функция GROUPING, которая принимает один аргумент и возвращает 1, если указанный столбец является итоговым в результате ROLLUP, CUBE или GROUPING SETS. В отличие от GROUPING_ID, она работает с одним столбцом. GROUPING удобнее использовать, когда нужно проверить итоговость только по одному конкретному полю.

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

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

1. Передача аргументов в порядке, отличном от порядка в GROUP BY. Это приводит к некорректному расчету битовой маски.

-- Неправильно: порядок аргументов не совпадает с GROUP BY
SELECT 
    DepartmentID,
    JobTitle,
    SUM(Salary),
    GROUPING_ID(JobTitle, DepartmentID) AS GrpID -- Обратный порядок
FROM Employees
GROUP BY DepartmentID, JobTitle
WITH ROLLUP;

2. Использование функции без операторов ROLLUP, CUBE или GROUPING SETS. В этом случае функция всегда возвращает 0, так как итоговых строк нет.

-- GROUPING_ID всегда вернет 0
SELECT 
    DepartmentID,
    SUM(Salary),
    GROUPING_ID(DepartmentID) AS GrpID
FROM Employees
GROUP BY DepartmentID; -- Отсутствует ROLLUP, CUBE или GROUPING SETS
DepartmentID  TotalSalary  GrpID
------------  -----------  -----
1             50000        0
2             75000        0

3. Неучет того, что NULL в исходных данных и NULL как признак итога неразличимы без использования GROUPING_ID или GROUPING.

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

Начиная с SQL Server 2008, функция GROUPING_ID не претерпела значительных синтаксических изменений. Однако, с выходом новых версий SQL Server, улучшена производительность и оптимизация запросов с использованием GROUP BY и связанных функций. В SQL Server 2012 и выше рекомендуется использовать GROUPING_ID вместо устаревших методов идентификации итоговых строк (например, проверки NULL через COALESCE с магическими значениями).

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

Использование GROUPING_ID для фильтрации определенного уровня агрегации. Например, выбрать только строки с итогами по отделам.

Пример sql
SELECT 
    DepartmentID,
    JobTitle,
    SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY ROLLUP(DepartmentID, JobTitle)
HAVING GROUPING_ID(DepartmentID, JobTitle) = 2; -- Итоги по отделам (DepartmentID не NULL, JobTitle - итог)
DepartmentID  JobTitle  TotalSalary
------------  --------  -----------
1             NULL      50000
2             NULL      75000

Применение в сортировке для группировки итоговых строк.

Пример sql
SELECT 
    CASE WHEN GROUPING_ID(DepartmentID, JobTitle) = 3 THEN 'Общий итог'
         WHEN GROUPING_ID(DepartmentID, JobTitle) = 2 THEN 'Итог по отделу ' + CAST(DepartmentID AS VARCHAR)
         WHEN GROUPING_ID(DepartmentID, JobTitle) = 1 THEN 'Итог по должности ' + JobTitle
         ELSE 'Детализация' END AS RowType,
    DepartmentID,
    JobTitle,
    SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY CUBE(DepartmentID, JobTitle)
ORDER BY GROUPING_ID(DepartmentID, JobTitle) DESC, DepartmentID, JobTitle;

Использование с GROUPING SETS для сложных сценариев группировки.

Пример sql
SELECT 
    DepartmentID,
    JobTitle,
    YEAR(HireDate) AS HireYear,
    SUM(Salary) AS TotalSalary,
    GROUPING_ID(DepartmentID, JobTitle, YEAR(HireDate)) AS GrpID
FROM Employees
GROUP BY GROUPING SETS 
(
    (DepartmentID, JobTitle),
    (DepartmentID, YEAR(HireDate)),
    (JobTitle, YEAR(HireDate)),
    ()
);

Расшифровка битовой маски GROUPING_ID в читаемый вид.

Пример sql
SELECT 
    DepartmentID,
    JobTitle,
    SUM(Salary),
    GROUPING_ID(DepartmentID, JobTitle) AS GrpID,
    CONCAT('Департамент: ', CASE WHEN GROUPING(DepartmentID)=1 THEN 'Итог' ELSE 'Детально' END,
           ', Должность: ', CASE WHEN GROUPING(JobTitle)=1 THEN 'Итог' ELSE 'Детально' END) AS Description
FROM Employees
GROUP BY CUBE(DepartmentID, JobTitle);

Альтернативы в других СУБД

Oracle: Имеет функции GROUPING и GROUPING_ID, синтаксис и логика работы которых полностью аналогичны MS SQL.

PostgreSQL: Не имеет прямой аналогии GROUPING_ID. Для определения итоговых строк можно использовать комбинацию функций GROUPING SETS и проверку на NULL с помощью GROUPING (доступно с версии 9.5).

-- PostgreSQL
SELECT 
    DepartmentID,
    JobTitle,
    SUM(Salary),
    GROUPING(DepartmentID) AS grp_dept,
    GROUPING(JobTitle) AS grp_job
FROM Employees
GROUP BY GROUPING SETS ((DepartmentID, JobTitle), (DepartmentID), (JobTitle), ());
 departmentid | jobtitle |  sum  | grp_dept | grp_job
--------------+----------+-------+----------+---------
            1 | Manager  | 30000 |        0 |       0
            2 | Engineer | 35000 |        0 |       0
              | Manager  | 70000 |        1 |       0
              | Engineer | 55000 |        1 |       0
            1 |          | 50000 |        0 |       1
            2 |          | 75000 |        0 |       1
              |          | 125000|        1 |       1

MySQL и SQLite: Не поддерживают функции GROUPING_ID или GROUPING. Аналогичного поведения можно добиться с помощью нескольких запросов UNION ALL или обработкой на уровне приложения.

Sybase ASE: Поддерживает функцию GROUPING, но не имеет GROUPING_ID. Логика аналогична MS SQL для одного столбца.

MS SQL GROUPING_ID function comments

En
GROUPING ID Computes the level of grouping