SQL в хранилищах данных: агрегация и суммирование

Использование CUBE для вычисления частичных сумм

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

Синтаксис:

GROUP BY expr1, CUBE(expr2, expr3);

В результате выполнения этой команды будет вычислено 4 частичные суммы:

• (expr1, expr2, expr3)
• (expr1, expr2)
• (expr1, expr3)
• (expr1)

Пример 22.6. Пусть руководству компании требуется перекрестный отчет о прибыли по всем регионам по всем отделам продаж за 2007-2008 гг. без вывода частичных сумм. Предложение SELECT для приведенной на рис. 22.3 схемы ХД может выглядеть следующим образом:

SELECT Time, Region, Department, SUM(Profit) AS Profit FROM sales
   GROUP BY Time CUBE(Region, Department);

Вывод 4. Использование CUBE для вычислений частичных сумм

Без использования предложения CUBE для n -мерного куба потребуется 2n команд SELECT с UNION ALL.

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

Квалификатор DISTINCT имеет ошибочную семантику в предложениях ROLLUP и CUBE. Не рекомендуется применять квалификатор DISTINCT в комбинации с этими предложениями.

Функция GROUPING

Две проблемы возникают при использовании ROLLUP и CUBE. Первая: как можно программно определить, какие строки результирующего множества являются частичными суммами, и как найти точный уровень агрегации данной частичной суммы? Часто необходимо использовать частичные суммы для вычислений процентных отношений между суммами, поэтому нужен простой способ находить частичные суммы. Вторая проблема: что произойдет, если результат запроса содержит и NULL-значение хранимых строк, и псевдо-NULL-значения, созданные ROLLUP или CUBE? Как различить их в результирующем множестве?

Для решения этой задачи предназначена функция GROUPING. Это статистическая функция, выдающая дополнительный столбец, который содержит значение 1, если строка добавлена с помощью оператора CUBE или ROLLUP, или значение 0 — в ином случае.

Синтаксис (указывается в списке предложения SELECT ):

SELECT ...  [GROUPING(column_name)...]  ... 
  GROUP BY ...    {CUBE | ROLLUP}  (column_name)

Пример 22.7. Использование функции GROUPING для создания колонок-масок в результирующем множестве.

SELECT Time, Region, Department, SUM(Profit) AS Profit,
  GROUPING (Time) as T, 
  GROUPING (Region) as R, 
  GROUPING (Department) as D
  FROM Sales
GROUP BY ROLLUP (Time, Region, Department);

Вывод 5. Использование функции GROUPING

Как видно из примера, маска "0 0 0" — агрегированная строка из таблицы, "0 0 1" — первый уровень агрегации, "0 1 1" — второй уровень агрегации, "1 1 1" — итоговая сумма.

Пример 22.8. Предположим, что оператор SELECT выдает следующее результирующее множество, созданное выражением CUBE.

Вывод 6.

В результирующем множестве 4 различных строки с NULL-значениями для колонок "Время" (Time) и "Регион" (Region). Некоторые из этих NULL-значений должны представлять агрегаты CUBE, а некоторые — агрегаты NULL-значений из базы данных. Как отличить в отчете агрегатные NULL-значения, построенные предложением CUBE, от хранимых в БД NULL-значений?

Использование GROUPING-функции в комбинации с CASE -выражением и функцией преобразования типов данных CAST (expression AS data_type [ (length ) ]) позволяет решить эту проблему.

Выражение CASE выполняет оценку списка условий и возвращает одно из нескольких возможных выражений результатов.

Синтаксис:

CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

или

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Теперь можно преобразовать отчет из примера 22.8 таким образом, чтобы выделить агрегаты предложения CUBE, как показано ниже.

Пример 22.9. Разграничение агрегатных и хранимых NULL-значений.

SELECT 
CASE WHEN GROUPING(Time) = 1 THEN 'All Times' ELSE CAST(Time as CHAR(20)) END AS Time,
CASE WHEN GROUPING(Region) = 1 THEN 'All Regions' ELSE Region END AS Region, SUM(Profit) AS Profit
FROM Sales
GROUP BY CUBE(Time, Region);

Вывод 7. Разграничение агрегатных и хранимых NULL-значений

Первая колонка есть "Время" (Time), определенное выражением

CASE WHEN GROUPING(Time) = 1 THEN 'All Times' ELSE CAST(Time as CHAR(20)) END AS Time,

Значение Time определяется выражением CASE, содержащим функцию GROUPING. Функция GROUPING возвращает 1, если строка есть агрегат предложений ROLLUP или CUBE, иначе — 0. CASE работает с результатом функции GROUPING. Оно возвращает текст "All Times", если это 1, и значение колонки "Время" (time) из БД, если это 0. Значениями из базы данных будут либо фактическое значение, такое как 2007, или сохраняемое NULL-значение. Функция CAST используется для согласования типов, поскольку в результирующем множестве все колонки должны быть одного типа. Вторая колонка спецификации, показывающая значения колонки "Регион" (Region), обрабатывается аналогичным образом.

Функция GROUPING полезна не только для идентификации NULL-значений, она также может помочь отсортировать строки частичных сумм и отфильтровать результирующее множество. В примере ниже выбирается подмножество частичных сумм, созданное CUBE. Предложение HAVING ограничивает колонки, которые используются в функции GROUPING.

Пример 22.10.

SELECT Time, Region, Department, SUM(Profit) AS Profit, 
  GROUPING (Time) AS T,
  GROUPING (Region) AS R, 
  GROUPING (Department) AS D
  FROM Sales
  GROUP BY CUBE (Time, Region, Department)
  HAVING (GROUPING(Department)=1 AND GROUPING(Region)=1 AND GROUPING(Time)=1) 
  OR (GROUPING(Region)=1 AND (GROUPING(Department)=1)
  OR (GROUPING(Time)=1 AND GROUPING(department)=1);

Вывод 8. Пример использования функции GROUPING для фильтрации результата в частичных суммах и итоговой сумме