ROLLUP, CUBE и GROUPING SETS операторы Transact-SQL для подведения итогов и промежуточных итогов

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

Возможность построения отчетов на Transact-SQL очень полезная, мы с Вами уже об этом говорили, когда рассматривали оператор PIVOT, который может транспонировать набор данных, теперь давайте научимся писать запросы, в которых будут выделяться промежуточные итоги и итоги в целом. В Transact-SQL сделать это можно с помощью операторов ROLLUP, CUBE и GROUPING SETS.

Как Вы, наверное, догадываетесь, для того чтобы подсчитать итог или подытог, необходимо прибегнуть к агрегатным функциям и, соответственно, к группировке данных, поэтому операторы ROLLUP, CUBE и GROUPING SETS относятся к конструкции GROUP BY, т.е. являются расширением GROUP BY.

Эти операторы естественно отличаются друг от друга, поэтому в процессе рассмотрения мы будем их сравнивать, но для начала давайте определимся с тестовыми данными для примеров.

Содержание

Исходные данные для примеров
ROLLUP
CUBE
GROUPING SETS

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

В качестве SQL сервера у нас будет выступать Microsoft SQL Server Express 2014, а запросы будем писать в Management Studio Express.

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

Таблица

 
   CREATE TABLE [dbo].[test_table](
        [id] [INT] IDENTITY(1,1) NOT NULL,
        [manager] [VARCHAR](50) NULL,
        [otdel] [VARCHAR](50) NULL,
        [god] [INT] NULL,
        [summa] [MONEY] NULL
   ) ON [PRIMARY]
   GO

А данные вот такие

Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих. В нем подробно рассмотрены все базовые конструкции языка T-SQL.

ROLLUP

ROLLUP – оператор Transact-SQL, который формирует промежуточные итоги для каждого указанного элемента и общий итог.

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

   
   SELECT otdel, god, SUM(summa) AS itog 
   FROM dbo.test_table
   GROUP BY otdel, god
   ORDER BY otdel, god

Как видите, группировка у нас получилась и в принципе мы видим что, например, в бухгалтерии в 2014 был такой расход, а 2015 такой, но иногда руководство хочет видеть и общую информацию, например, общий расход по каждому отделу. Для этих целей мы можем использовать оператор ROLLUP.

Текст запроса

   
   SELECT otdel, god, SUM(summa) AS itog 
   FROM dbo.test_table
   GROUP BY 
   ROLLUP (otdel,god)

Строки со значением NULL и есть промежуточные итоги по отделам, а самая последняя строка общий итог. Согласитесь, что это уже более наглядно.

Можно также использовать rollup и с группировкой по одному полю, например:

Группировка по отделам с общим итогом

Группировка по годам с общим итогом

CUBE

CUBE — оператор Transact-SQL, который формирует результаты для всех возможных перекрестных вычислений.

Давайте напишем практически такой же SQL запрос, только вместо rollup укажем cube и посмотрим на полученный результат.

Текст запроса

   
   SELECT otdel, god, SUM(summa) AS itog 
   FROM dbo.test_table
   GROUP BY 
   CUBE (otdel,god)

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

GROUPING SETS

GROUPING SETS – оператор Transact-SQL, который формирует результаты нескольких группировок в один набор данных, другими словами, он эквивалентен конструкции UNION ALL к указанным группам.

Пример GROUPING SETS

Текст запроса

   
   SELECT otdel, god, SUM(summa) AS itog 
   FROM dbo.test_table
   GROUP BY
   GROUPING SETS (otdel,god)

тот же результат, но с использованием UNION ALL

Текст запроса

   
   SELECT null AS otdel, god, SUM(summa) AS itog 
   FROM dbo.test_table
   GROUP BY god

   UNION ALL

   SELECT otdel, null AS god, SUM(summa) AS itog 
   FROM dbo.test_table
   GROUP BY otdel

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

GROUPING – функция Transact-SQL, которая возвращает истину, если указанное выражение является статистическим, и ложь, если выражение нестатистическое.

Данная функция создана для того, чтобы отличить статистические строки, которые добавил SQL сервер, от строк, которые и есть сами данные, так как когда используешь много группировок, запутаться в строках очень легко.

Пример GROUPING

Текст запроса

   
   SELECT otdel,
          ISNULL(CAST(god AS VARCHAR(30)), 
                 CASE WHEN GROUPING(god)=1 ANS GROUPING(otdel)=0 
                      THEN 'Промежуточный итог' 
                      ELSE 'Общий итог' END) AS god,
          SUM(summa) AS itog,
          GROUPING(otdel) AS grouping_otdel,
          GROUPING(god) AS grouping_god
   FROM dbo.test_table
   GROUP BY 
   ROLLUP (otdel,god)

На этом все, надеюсь, что возможность формировать итоги и промежуточные итоги в Microsoft SQL Server Вам будет полезна. Также рекомендую Вам почитать мою книгу «SQL код», в ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных. Удачи!