Функция grouping sets

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

if object_id ( N'tempdb..#MonthsData', N'U' ) is not null

       drop table #MonthsData

create table #MonthsData

(

       dtDate date   not null,

       iType  int          not null,

       fSum   float  not null

) on [PRIMARY]

insert into #MonthsData ( dtDate, iType, fSum )

       values

             ( '2010-01-01', 1, 100 ),

             ( '2010-02-01', 1, 130 ),

             ( '2010-03-01', 1, 200 ),

             ( '2010-04-01', 1, 90 ),

             ( '2010-05-01', 1, 85 ),

             ( '2010-01-01', 2, 23 ),

             ( '2010-02-01', 2, 521 ),

             ( '2010-03-01', 2, 34 ),

             ( '2010-04-01', 2, 300 ),

             ( '2010-05-01', 2, 120 )

Перед нами стоит задача получить набор данных этой таблицы, так чтобы в в последней строке дополнительно была указана общая сумма продаж по всем типам, за все время. Для решения этой задачи можно было бы написать запрос к таблице, добавив с помощью union строку с общей суммой. Но есть более элегантное решение. Функция grouping sets позволяет получить набор данных, в котором будут результаты группирований на различных уровнях. Можно написать простой запрос, который возвратит таблицу с общей суммой в последней строке:

select dtDate, iType, sum ( fSum ) fSum

from #MonthsData

group by grouping sets ( ( dtDate, iType ), () )

Здесь виден алгоритм функции grouping sets: в скобках перечисляются наборы, по которым идет группировка. Кортэж ( dtDate, iType ) гарантирует, что вернутся строки таблицы #MonthData. А пустой кортэж исключит из группировки все столбцы, которые входят в select:

Видно, что в строке, в которой вычисляется общая сумма, в качестве dtDate и fSum стоят пустые значения. Во многих случаях это могло бы использоваться как признак того, что уровень группирования изменен. Однако не всегда можно этим пользоваться, поскольку некоторые столбцы в таблице могут иметь значения null. Рассмотрим это на примере:

alter table #MonthsData alter column dtDate date null

alter table #MonthsData alter column iType int null

insert into #MonthsData ( dtDate, iType, fSum )

       values ( null, null, 130 )

Чтобы отличить null, который был вставлен в таблицу, от null, который появился в результирующем наборе из-за группирования, воспользуемся функцией grouping:

select dtDate,

       case when grouping ( dtDate ) = 0 and grouping ( iType ) = 0 then cast ( iType as nvarchar ( 100 ) )

       else N'Общий итог: ' end iType, sum ( fSum ) fSum

from #MonthsData

group by grouping sets ( ( dtDate, iType ), () )

Когда кортэж содержит более одного элемента, то удобнее функция grouping_id, которая может получать в качестве параметра кортэжи. Она получает в качестве параметра кортэж и возвращает битовую маску. То есть, если элемент кортэжа не входит в группировку для данной строки, то для него соответствуюий бит выставлен в 1, иначе в 0. Биты считаются справа налево:

select dtDate,

       case when grouping_id ( dtDate, iType ) = 3 then N'Общий итог: ' else cast ( iType as nvarchar ( 100 ) ) end iType,

       sum ( fSum ) fSum

from #MonthsData

group by grouping sets ( ( dtDate, iType ), () )

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

select dtDate,

       case

             when grouping_id ( dtDate, iType ) = 3 then N'Общий итог: '

             when grouping_id ( dtDate, iType ) = 2 then N'Сумма для всех с типом ' + isnull ( cast ( iType as nvarchar ( 100 ) ), 'null' ) + ': '

       else cast ( iType as nvarchar ( 100 ) ) end iType,

       sum ( fSum ) fSum

from #MonthsData

group by grouping sets ( iType, ( dtDate, iType ), () )

Результаты такие: