Опубликован: 20.12.2010 | Доступ: свободный | Студентов: 2412 / 165 | Оценка: 4.27 / 3.91 | Длительность: 39:39:00
ISBN: 978-5-9963-0353-3
Лекция 16:

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)

Year Quarter Month Profit
2008 Первый Январь 55,00
2008 Первый Февраль 64,00
2008 Первый Март 71,00
2008 Первый NULL 190,00
2008 Второй Апрель 75,00
2008 Второй Май 86,00
2008 Второй Июнь 88,00
2008 Второй NULL 249,00
2008 Третий Июль 91,00
2008 Третий Август 87,00
2008 Третий Сентябрь 101,00
2008 Третий NULL 279,00
2008 Четвертый Октябрь 109,00
2008 Четвертый Ноябрь 114,00
2008 Четвертый Декабрь 133,00
2008 Четвертый NULL 356,00
2008 NULL NULL 1074,00

Обратим внимание на некоторые особенности использования предложений 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>
Владислав Нагорный
Владислав Нагорный

Подскажите, пожалуйста, планируете ли вы возобновление программ высшего образования? Если да, есть ли какие-то примерные сроки?

Спасибо!

Лариса Парфенова
Лариса Парфенова

1) Можно ли экстерном получить второе высшее образование "Программная инженерия" ?

2) Трудоустраиваете ли Вы выпускников?

3) Можно ли с Вашим дипломом поступить в аспирантуру?