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

Управление иерархией в предложениях ROLLUP и CUBE

Предложения ROLLUP и CUBE работают независимо от какой-либо иерархии данных в БД. Их вычисления основываются на колонках, которые появляются в списке оператора оператора SELECT. Такой подход позволяет использовать CUBE и ROLLUP и для обработки иерархий. Простой способ управления иерархией измерения — использовать предложение ROLLUP и точно указать уровни иерархии для выделенной колонки. В примере ниже показано, как месяцы сворачиваются в кварталы, а кварталы — в год. При этом предполагается, что схема ХД учебного примера модифицирована, как показано на рис. 22.4.

Пример 22.11.

SELECT Year, Quarter, Month, SUM(Profit) AS Profit 
FROM sales
   GROUP BY ROLLUP(Year, Quarter, Month)
HAVING Year = 2008;

Рис. 22.4. Модифицированная схема ХД учебного примера

Вывод 9. Пример ROLLUP с использованием уровней иерархии измерения "Время" (Time)

Обратим внимание на некоторые особенности использования предложений ROLLUP и CUBE.

Предложения CUBE и ROLLUP не ограничивают мощность колонки (Column Capacity) предложения GROUP BY. Предложение GROUP BY, с расширениями или без них, может работать с 255 колонками. Однако число комбинаций, которое создает предложение CUBE, может быть очень велико. Для 20-ти колонок предложением CUBE будут создано 220 комбинаций в результирующем множестве.

Предложение HAVING команды SELECT не влияет на использование предложений ROLLUP и CUBE в том смысле, что оно применяется в целом к предложению GROUP BY. Предикат в предложении HAVING применяется как к строкам частичных сумм, так и к строкам-агрегатам результирующего множества.

Предложение ORDER BY команды SELECT не влияет на использование предложений ROLLUP и CUBE. Предикат в предложении ORDER BY применяется ко всем строкам результирующего множества.

Резюме

В настоящей лекции мы рассмотрели расширения предложения GROUP BY оператора SELECT, предназначенные для агрегации данных в результирующем множестве.

• Предложение ROLLUP(), которое генерирует агрегированные строки предложения GROUP BY плюс частичные суммы или многоуровневые агрегированные строки, а также строку итоговой суммы
• Предложение CUBE(), которое генерирует агрегированные строки предложения GROUP BY, многоуровневые агрегированные строки для всевозможных комбинаций колонок из заданного списка

Использование предложений CUBE и ROLLUP делает выполнение запросов и построение отчетов проще в среде ХД. Предложение ROLLUP целесообразно использовать для задач, в которых вычисляются промежуточные или частичные суммы, предложение CUBE — при решении задач создания перекрестных отчетов.

В заключение приведем подробный синтаксис предложения GROUP BY диалекта SQL СУБД семейства MS SQL Server.

Синтаксис, совместимый со стандартом ISO/IEC 9075-5:

GROUP BY <group by spec>

<group by spec> ::=
    <group by item> [ ,...n ]

<group by item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>
    | <grouping sets spec>
    | <grand total>

<simple group by item> ::=
    <column_expression>

<rollup spec> ::=
    ROLLUP ( <composite element list> )

<cube spec> ::=
    CUBE ( <composite element list> )

<composite element list> ::=
    <composite element> [ ,...n ]

<composite element> ::=
    <simple group by item>
    | ( <simple group by item list> )

<simple group by item list> ::=
    <simple group by item> [ ,...n ]

<grouping sets spec> ::=
    GROUPING SETS ( <grouping set list> )

<grouping set list> ::=
    <grouping set> [ ,...n ]

<grouping set> ::=
    <grand total>
    | <grouping set item>
    | ( <grouping set item list> )

<empty group> ::=
        ( )

<grouping set item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>

<grouping set item list> ::=
    <grouping set item> [ ,...n ]

Синтаксис, не предусмотренный стандартом ISO/IEC 9075-5:

[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
    [ WITH { CUBE | ROLLUP } ] 
]
<column_expression>