Суммирование данных с помощью оператора CUBE
Оператор CUBE формирует результирующий набор, представляющий собой многомерный куб. Многомерный куб — это расширение данных об отдельных фактах или событиях. Расширение основывается на столбцах, которые пользователю необходимо проанализировать. Эти столбцы называются измерениями. Куб представляет собой результирующий набор, содержащий перекрестную таблицу всех возможных комбинаций измерений.
Оператор CUBE указывается в предложении GROUP BY инструкции SELECT. Список выбора содержит столбцы измерений и выражения статистических функций. Предложение GROUP BY задает столбцы измерений и ключевые слова WITH CUBE. Результирующий набор содержит все возможные комбинации значений столбцов измерений вместе со значениями статистических вычислений соответствующих строк, которые совпадают с комбинацией значений измерений.
Например, в простой таблице Inventory содержится следующее:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
Следующий запрос возвращает результирующий набор, который содержит подытог Quantity для всех возможных комбинаций столбцов Item и Color:
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Ниже приводится результирующий набор.
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00
Следующие строки результирующего набора представляют особый интерес:
Chair (null) 311.00
Эта строка возвращает подытог для всех строк, содержащих значение Chair в измерении Item. Значение null возвращается для измерения Color, что указывает на то, что статистическое вычисление, сообщенное строкой, включает строки с любым значением измерения Color.
Table (null) 347.00
Эта строка схожа с предыдущей, но возвращает подытог для всех строк, содержащих значение Table в измерении Item.
(null) (null) 658.00
Эта строка возвращает общий итог для куба. Как измерение Item, так и Color имеют значения null. Это указывает на то, что все значения для обоих измерений просуммированы в данной строке.
(null) Blue 225.00
(null) Red 433.00
Эти две строки возвращают подытоги для измерения Color. Обе строки содержат значение null в измерении Item, что указывает на то, что статистические данные, взятые из строк, могут иметь любое значение измерения Item.
Использование функции GROUPING для определения значений NULL
Значения NULL, формируемые операцией CUBE, представляют собой проблему: как отличить значение NULL, сформированное операцией CUBE, от значения NULL, возвращенного фактическими данными? Для этого используется функция GROUPING. Функция GROUPING возвращает 0, если значение столбца взято из фактических данных, и 1, если значение столбца NULL сформировано операцией CUBE. Формируемое в операции CUBE значение NULL замещает все значения. В инструкции SELECT можно также использовать функцию GROUPING, чтобы заменить любое формируемое значение NULL строкой ALL. Так как значение NULL из фактических данных указывает на то, что значение данных неизвестно, в инструкции SELECT можно также указать возвращение строки UNKNOWN вместо любых значений NULL из фактических данных. Например:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Многомерные кубы
Оператор CUBE можно также использовать для формирования n-мерных кубов, или кубов с любым числом измерений. Одномерный куб можно использовать для формирования итога, например:
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO
Данная инструкция SELECT возвращает результирующий набор, который показывает как подытоги для каждого значения Item, так и общий итог для всех значений Item:
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
Инструкции SELECT, содержащие оператор CUBE с множеством измерений, могут возвращать результирующие наборы большого размера, так как эти инструкции формируют строки для всех комбинаций значений во всех измерениях. Такие большие результирующие наборы могут содержать слишком много данных, которые не просто прочесть и понять. Решением этой проблемы может стать помещение инструкции SELECT в представление:
CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
Затем представление можно использовать для запроса только нужных значений измерений:
SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)
См. также
Основные понятия
Определение итоговых данных с помощью оператора ROLLUP