Групповые операции SQL

1.

Курс «Хранилища данных»
Тема: Групповые операции SQL
Барабанщиков
Игорь Витальевич

2.

Новые групповые операции
В Oracle SQL введены полезные операции,
облегчающие обобщение данных:
• ROLLUP – для вставки в результаты
суммирования итогов и подитогов.
• CUBE – для формирования подитогов для всех
возможных комбинаций группировки столбцов.
• GROUPING – помогает корректно
интерпретировать результаты, генерируемые
ROLLUP и CUBE.
• GROUPING SETS – генерирует суммарную
информацию заданного уровня.
2

3.

Использование ROLLUP
• ROLLUP – это расширение инструкции
GROPUP BY
• Операция ROLLUP группирует выделенные
строки на основе выражений GROPUP BY и
подготавливает итоговую строку для
каждой группы.
• Это новый способ.
SELECT . . .
FROM . . .
GROUP BY ROLLUP (список столбцов)
3

4.

Пример ROLLUP
SELECT R.region, O.month, SUM(O.sales)
FROM orders O, region R
WHERE R.region_id = O.region_id
AND O.year = 2015
GROUP BY ROLLUP (R.region, O.month)
• Аргументом ROLLUP является упорядоченный
список столбцов группировки.
• Сначала генерируются обобщенные значения на
основе операции GROUP BY над всем списком
столбцов.
• Затем генерируются подитоги более высокого
уровня и общий итог.
4

5.

Результат выполнения
REGION
MONTH
SUM(O.SALES)
Урал
1
200
...
...
...
Урал
12
200
Урал
2400
Сибирь
1
100
...
...
...
Сибирь
12
100
Сибирь
1200
3600
5

6.

Преимущества ROLLUP
• Вместо того, чтобы производить
многочисленные сканирования таблиц,
объединения и другие операции,
необходимые в UNION-версии запроса,
ROLLUP-запрос для получения желаемого
результата требует всего одного полного
просмотра таблицы регионов, одного
полного просмотра таблицы заказов и
одного объединения.
• Можно получить подитоги за каждый
месяц, а не для каждого региона. Для этого
надо поменять порядок столбцов в операции
ROLLUP.
6

7.

Применение ROLLUP
• ROLLUP создает промежуточные итоги
(subtotals) двигаясь по данным от низкого
уровня к высокому.
• При этом также подсчитывается общий итог
(grand total).
• Оператор ROLLUP полезен при обсчете
данных по иерархическому измерению,
например, времени.
Пример: GROUP BY ROLLUP (year, month, day).
Будут подсчитаны итоги для групп:
(year, month, day), (year, month), (year), общий
итог
7

8.

Использование CUBE
• CUBE – это расширение инструкции GROUP BY.
• CUBE генерирует подитоги для всех возможных
комбинаций столбцов группировки.
• Вывод операции CUBE будет содержать все
подитоги аналогичной операции ROLLUP, а также
некоторые дополнительные подитоги.
Синтаксис:
SELECT . . .
FROM . . .
GROUP BY CUBE (список группируемых столбцов)
8

9.

Сравнение CUBE c ROLLUP
• Если выполнить ROLLUP для столбцов регион и
месяц, то получим:
- обычные строки, порожденные GROUP BY
- подитоги для всех месяцев по каждому региону
- общий итог
• Если выполнить CUBE для столбцов регион и
месяц, то получим:
- обычные строки, порожденные GROUP BY
- подитоги для всех месяцев по каждому региону
- подитоги для всех регионов по каждому месяцу
- общий итог
9

10.

Сравнение CUBE c ROLLUP
• CUBE выводит обобщенные результаты для
всех возможных комбинаций группируемых
столбцов.
• Результат запроса с CUBE не зависит от
порядка столбцов в операции CUBE.
• Результат запроса с ROLLUP зависит от
порядка столбцов в операции ROLLUP.
• Результат CUBE(a,b) такой же, как и
CUBE(b,a).
• Результат ROLLUP(a,b) будет отличаться от
ROLLUP(b,a)
10

11.

Пример CUBE
SELECT R.region, O.month, SUM(O.sales)
FROM orders O, region R
WHERE R.region_id = O.region_id
GROUP BY CUBE(R.region, O.month)
Чтобы получить такой же результат без
использования CUBE надо написать 4
запроса, результаты которых придется
объединить с помощью 3-х UNION ALL.
11

12.

GROUPING SETS
• Это еще одно расширение инструкции
GROUP BY.
• Оно позволяет генерировать суммарную
информацию только необходимого уровня,
не включая в вывод строки, полученные в
результате выполнения обычной операции
GROUP BY.
SELECT . . .
FROM . . .
GROUP BY GROUPING SETS (список столбцов)
12

13.

Пример GROUPING SETS
SELECT O.year, O.month, R.name, SUM(O.sales)
FROM orders O, region R
WHERE R.region_id = O.region_i
and O.month BETWEEN 1 and 3
GROUP BY
GROUPING SETS(O.year, O.month, R.name)
13

14.

Результат запроса
Год
Месяц
Регион
Продажи
2000
10042570
2001
5021285
Январь
4496799
Февраль
4988535
Март
5578521
Сибирь
5029212
Урал
5074232
Д.Восток
4960311
14

15.

Анализ результата запроса
• Результат запроса содержит только
подитоги уровня региона, месяца и
года, более подробных данных GROUP BY
в нем нет.
• Порядок столбцов операции GROUPING
SETS не имеет значения.
• Операция выводит один и тот же
результат вне зависимости от порядка
столбцов, меняется только порядок строк
вывода.
15

16.

Составные столбцы
• Составной столбец – это набор из двух или
более столбцов, значения которых при
выполнении групповых расчетов
рассматриваются как единое целое.
• Начиная с Oracle 9i, допускается
применение групповых операций типа
ROLLUP((a, b), c).
• Групповые расчеты воспринимают (a, b)
как один столбец.
16

17.

Пример запроса
SELECT O.year, O.month, R.name, SUM(O.sales)
FROM orders O, region R
WHERE R.region_id = O.region_i
and O.month BETWEEN 1 and 3
GROUP BY
ROLLUP((O.year, O.month), R.name)
17

18.

Результат запроса
Год
Месяц
Регион
Продажи
2000
Январь
Сибирь
100
2000
Январь
Урал
200
2000
Январь
Д.Восток
150
2000
Январь
2000
Февраль
Сибирь
120
2000
Февраль
Урал
230
2000
Февраль
Д.Восток
170
2000
Февраль
450
520
18

19.

Каскадная группировка
• Один запрос может содержать в инструкции
GROUP BY несколько операций ROLLUP, CUBE
или GROUPING SETS или их комбинацию.
• Наличие нескольких операций группировки
(ROLLUP, CUBE, GROUPING SETS) в инструкции
GROUP BY называется каскадной группировкой.
• Результатом каскадной группировки является
вывод перекрестного произведения
группировок каждой операции.
19

20.

Пример
Запрос
SELECT O.year, O.month, R.region, sum(O.sales)
FROM orders O, region R
WHERE R.region_id = O.region_id
GROUP BY ROLLUP(O.year), ROLLUP(O.month),
ROLLUP(R.region)
Возвращает такой же результат, что и запрос:
SELECT O.year, O.month, R.region, sum(O.sales)
FROM orders O, region R
WHERE R.region_id = O.region_id
GROUP BY CUBE(O.year, O.month , R.region)
20

21.

Заключение
• Для решения задач
бизнес-аналитики
приходится писать
сложные SQLзапросы.
• Использование
аналитических
функций SQL
упрощает разработку
запросов для бизнесаналитики.
21