четверг, 21 марта 2013 г.

TSQL - GROUP BY с операторами ROLLUP и CUBE, и агрегатные функции GROUPING и GROUPING_ID


TSQL - GROUP BY с операторами ROLLUP и CUBE, и агрегатные функции GROUPING и GROUPING_ID


   Простейшее группирование результатов запроса не вызывает никаких сложностей, и SQL Server всегда выдает понятный и ожидаемый результат.  Но, когда впервые сталкивается с конструкциями ROLLUP и CUB можно натолкнуться на преграду, так как интуитивно понятными данные конструкции назвать сложно.
   ROLLUP и CUBE вставляют в результат статистические строки, что это разберемся далее, сейчас хочу лишь заметить что разница в этих конструкциях только в формуле, благодаря которой они определяют сколько и каких статистических строк вставить.
   Создадим в тестовой базе таблицу, которая будет представлять винный погреб, и положим в него несколько бутылок отличного вина.

CREATE TABLE WineCellar (
      Color nvarchar(50),
      Sugar nvarchar(50),
      Years int);
GO
INSERT WineCellar VALUES ('White','doux',5);
INSERT WineCellar VALUES ('White','doux',10);

INSERT WineCellar VALUES ('White','sec',5);
INSERT WineCellar VALUES ('White','sec',10);

INSERT WineCellar VALUES ('Red','doux',5);
INSERT WineCellar VALUES ('Red','doux',10);

INSERT WineCellar VALUES ('Red','sec',5);
INSERT WineCellar VALUES ('Red','sec',10);

 Ранее я упоминал, что и ROLLUP и CUBE используют некую формулу для предоставления результатов, но не только - они так же предоставляют итоговую строку, это группа в которой включено все множество запроса, и называется она  «строка общего итога».
   Формулы в ROLLUP и CUBE могут работать только когда идет группировка по нескольким столбцам, если же идет группировка по единственному столбцу, они работать не будут, а в результирующем наборе появится только «строка общего итога»
Если мы выполним SQL запрос:

SELECT Color, Count(*) Count
FROM WineCellar
GROUP BY ROLLUP(Color)

Или:

SELECT Color, Count(*) Count
FROM WineCellar
GROUP BY CUBE(Color)

То результат будет идентичным:


   Так как формулы не могут работать, когда группировка происходит только по одному столбцу.
Что же такое «строка общего итога», выполним тот же запрос только без  ROLLUP или CUBE

SELECT Color, Count(*) Count
FROM WineCellar
GROUP BY Color

Результатом будет:


   Как можно заметить запрос вернул в общей сумме 2 группы по 4 строки в каждой группе, то есть всего запрос вернул 8 строк. Если посмотрим на предыдущий результат, в котором есть «строка общего итога», то можно заметить что последняя группа содержит в себе 8 строк. Другими словами «строка общего итога» это группа, в которой находятся копии всех строк, возвращенных запросом.
Теперь обратим внимание на формулы, по которым работают ROLLUP и CUBE.
  ROLLUP один из возможных переводов с английского это сдвиг строк (на экране дисплея), если провести аналогию дальше, то это похоже на битовый сдвиг влево. Рассмотри на примере колонок:
   Предположим, есть три исходные колонки A,B,C   воображаемо сделаем два сдвига влево. Результатом первого сдвига будет B,C,NULL а результатом второго будет C,NULL,NULL . Делать сдвиги далее не имеет смысла, так как результат будет всегда один NULL,NULL,NULL. 
   Формула ROLLUP использует эти сдвиги, что бы понять какие результаты от нее требуются.
Попробуем рассчитать самостоятельно результат для запроса:

SELECT Color,Sugar,Years, Count(*) Count
FROM WineCellar
GROUP BY ROLLUP(Color,Sugar,Years)

Итак, первым делом SQL SERVER поймет, что вам требуется сгруппировать результат по колонкам Color,Sugar,Years запрос может выглядеть таким образом:

SELECT Color,Sugar,Years, Count(*) Count
FROM WineCellar
GROUP BY Color,Sugar,Years

Результат:

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

SELECT Color,Sugar, Count(*) Count
FROM WineCellar
GROUP BY  Color,Sugar



И

SELECT Color, Count(*) Count
FROM WineCellar
GROUP BY  Color



Объединим вручную, в один результат:



Осталось лишь выполнить запрос:

SELECT Color,Sugar,Years, Count(*) Count
FROM WineCellar
GROUP BY ROLLUP(Color,Sugar,Years)

И сравнить результаты:



   Немного не то что мы ожидали, но если приглядеться то SQL Server лишь изменил порядок вывода, на более удобный, с его точки зрения.
Перейдем к оператору CUBE
   Название CUBE пошло от многомерного куба и представляет собой результирующий набор, содержащий перекрестную таблицу всех возможных комбинаций столбцов.
   То есть, если будет создан запрос, который будет использовать три столбца с оператором CUBE, то в результирующий набор войдет все возможное множество комбинаций этих столбцов.
Рассмотрим на примере:
   Для запроса использующего три столбца (A,B,C) в случае ROLLUP в дополнение к основному результату создается «строка общего итога» и результаты для сочетаний (A,B) и (A) в соответствии со сдвигами.
   В случае же с CUBE в дополнение к основному результату создается «строка общего итога» и все возможные уникальные вариации для столбцов (A,B);(A,C);(B,C);(A);(B);(C).
Попробуем вновь предугадать результат для запроса:

SELECT Color,Sugar,Years, Count(*) Count
FROM WineCellar
GROUP BY CUBE(Color,Sugar,Years)

   Как и в прошлый раз SQL SERVER поймет, что вам требуется сгруппировать результат по колонкам Color,Sugar,Years запрос может выглядеть таким образом:

SELECT Color,Sugar,Years, Count(*) Count
FROM WineCellar
GROUP BY Color,Sugar,Years

Результат:


  Так же он подсчитает строку общего итога.
  Далее идет целых 6 возможных вариаций со столбцами Color,Sugar,Years

Color + Sugar

SELECT Color,Sugar, Count(*) Count
FROM WineCellar
GROUP BY Color,Sugar

Результат:


Color + Years

SELECT Color,Years, Count(*) Count
FROM WineCellar
GROUP BY Color,Years

Результат:


Sugar + Years

SELECT Sugar,Years, Count(*) Count
FROM WineCellar
GROUP BY Sugar,Years

Результат:


Color

SELECT Color, Count(*) Count
FROM WineCellar
GROUP BY Color

Результат:


Sugar

SELECT Sugar, Count(*) Count
FROM WineCellar
GROUP BY Sugar

Результат:


Years

SELECT Years, Count(*) Count
FROM WineCellar
GROUP BY Years

Результат:


Объединим все результаты вручную:



И сравним с результатом:

SELECT Color,Sugar,Years, Count(*) Count
FROM WineCellar
GROUP BY CUBE(Color,Sugar,Years)



Как можно заметить мы опять ошиблись лишь с последовательностью вывода.
   Перейдем к агрегатным функциям GROUPING и GROUPING_ID
   Вы наверняка обратили внимание на большое количество NULL значений после того как отработают операторы ROLLUP или CUBE. Так же вы привыкли думать, что NULL это всегда NULL, но не в этом случае. Дело в том, что SQL Server различает NULL как значение в таблице и NULL который был выставлен в результате работы ROLLUP или CUBE.
   Чтобы продемонстрировать это, надо изменить нашу таблицу и вставить строку с NULL значениями:

ALTER TABLE WineCellar ALTER COLUMN Color nvarchar(50) NULL;
ALTER TABLE WineCellar ALTER COLUMN Sugar nvarchar(50) NULL;
ALTER TABLE WineCellar ALTER COLUMN Years int NULL;
GO
INSERT WineCellar VALUES (NULL,NULL,NULL);

   Агрегатная функция GROUPING проверяет NULL значение, в случае если оно было выставлено в результате работы ROLLUP или CUBE значение будет единица в остальных случаях ноль.

Проверяем:

SELECT Color,Sugar,Years, Count(*) Count,
      GROUPING(Color) GROUPING_Color,
      GROUPING(Sugar) GROUPING_Sugar,
      GROUPING(Years) GROUPING_Years
FROM WineCellar
GROUP BY ROLLUP(Color,Sugar,Years)

Результат:



   В отличии от GROUPING для понимания GROUPING_ID необходимо знать про битовые флаги (для ознакомления можно прочесть статью http://skillcoding.com/Default.aspx?id=87 ), так как GROUPING_ID возвращает Integer значение битового флага.
Немного переделаем предыдущий пример:

SELECT Color,Sugar,Years, Count(*) Count,
      GROUPING(Color) Color_Bit,
      GROUPING(Sugar) Sugar_Bit,
      GROUPING(Years) Years_Bit,
      GROUPING_ID(Color,Sugar,Years) as BinaryFlag
FROM WineCellar
GROUP BY ROLLUP(Color,Sugar,Years)

Результат:


   Как видно из запроса мы указываем - из каких колонок и в какой последовательности проверять NULL значения.
   Возьмем для примера десятую строчку. NULL значения в столбце Color нет, в столбце Sugar есть и этот NULL - результат работы ROLLUP. В столбце Years тоже есть NULL значение, которое является результатом работы ROLLUP. В итоге получаем битовый флаг 011, при переводе из двоичной системы в десятеричную получаем тройку, что и отображается в столбце BinaryFlag.
   На этом все, удачи вам и спасибо что прочитали мою статью. Буду рад оставленным комментариям и новым постоянным читателям.

Александр Кобелев.