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.
На этом все,
удачи вам и спасибо что прочитали мою статью. Буду рад оставленным комментариям
и новым постоянным читателям.
Александр Кобелев.
