Аналитические запросы

SQL имеет несколько возможностей для поддержки OLAP. С агрегирующими операторами вы уже знакомы. SQL для OLAP поддерживает механизмы многоуровневой агрегации. Предположим, нужно вывести среднюю зарплату по городам и среднюю зарплату по городам и отделам в одном запросе. Для этого, используя традиционный SQL, нужно написать два запроса и объединить их с помощью UNION (запрос приведен на Лист. 44, результат - Табл. 56).

Лист. 44. Запрос на многоуровневую агрегацию

SELECT Город, 'по всем отделам' AS Отдел, avg(Зарплата) AS Средняя_Зарплата

FROM Продавцы

GROUP BY Город

UNION SELECT Город, Отдел, avg(Зарплата) AS Средняя_Зарплата

FROM Продавцы

GROUP BY Город, Отдел;

Табл. 56. Результат запроса на многоуровневую агрегацию

Используя механизмы OLAP, данный запрос можно написать значительно короче (см. Лист. 45).

Лист. 45. Запрос OLAP на многоуровневую агрегацию

SELECT Город, Отдел, MAX (Зарплата)

FROM Продавцы

GROUP BY ROLLUP (Город, Отдел);

Иногда бывают ситуации, когда поля не обязательны для заполнения. В этом случае при выполнении предыдущего запроса возникает проблема, что могут быть продавцы из какого-нибудь города, у которых не указан отдел. Нужно различать среднюю стоимость по продавцам без отдела и среднюю стоимость по городу. Для этого вводится дополнительная колонка с помощью оператора GROUPING, которая содержит ”1”, если имеется в виду средняя стоимость по городу, и ”0”, если средняя стоимость по продавцам без отдела (см. Лист. 46)

Лист. 46. Уточненный запрос OLAP на многоуровневую агрегацию

SELECT Город, Отдел, MAX (Зарплата), GROUPING(Город), GROUPING(Отдел)

FROM Продавцы

GROUP BY ROLLUP (Город, Отдел);

Предположим, нам нужно произвести многоуровневую агрегацию по альтернативным иерархиям, например, вывести среднюю зарплату по городам, среднюю зарплату по отделам и среднюю зарплату по городам и отделам в одном запросе. Запрос на традиционном SQL приведен на Лист. 47, результаты – в Табл. 57, запрос на SQL с использованием OLAP – Лист. 48).

Лист. 47. Запрос на многоуровневую агрегацию по всем измерениям

SELECT Город, 'по всем отделам' AS Отдел, avg(Зарплата) AS Средняя_Зарплата

FROM Продавцы

GROUP BY Город

UNION SELECT 'по всем городам' AS Город, Отдел, avg(Зарплата) AS Средняя_Зарплата

FROM Продавцы

GROUP BY Отдел

UNION SELECT Город, Отдел, avg(Зарплата) AS Средняя_Зарплата

FROM Продавцы

GROUP BY Город, Отдел;

Табл. 57. Результат запроса на многоуровневую агрегацию по всем измерениям

Лист. 48 Запрос OLAP на многоуровневую агрегацию по всем измерениям

SELECT Город, Отдел, MAX (Зарплата), GROUPING(Город), GROUPING(Отдел)

FROM Продавцы

GROUP BY ROLLUP CUBE(Город, Отдел);

В OLAP помимо avg, min, max, count и sum предусмотрены дополнительные статистические функции, приведенные в Табл. 58.

Табл. 58. Статистические функции OLAP

Предположим, для каждого продавца нужно посчитать скользящее среднее стоимости по трем датам. То есть для каждой даты нужно посчитать среднюю стоимость продаж за эту дату и три предыдущих дня. Традиционными средствами SQL написать такой запрос не просто. В OLAP есть аналитические запросы с окном. Пример запроса приведен на Лист. 49. В нем после статистической функции avg в круглых скобках описывается окно. PARTITION BY задает разбиение таблицы на группы по продавцам, а ROWS 2 PRECEDING – размер окна.

Лист. 49. Аналитический запрос с окном

SELECT Дата, N_Продавца,

AVG (Стоимость)

OVER (PARTITION BY N_Продавца

ORDER BY Дата ASC

ROWS 2 PRECEDING )

AS ‘Скользящее среднее’

FROM Сделки

ORDER BY Дата ASC;

Подробнее об аналитических запросах см. [4].

Д/З 8. Для БД из Д/З 4 напишите следующие запросы:

1. Аналитический запрос c group by rollup cube.
2. Аналитический запрос с окном.

Вопросы для самопроверки:

1. Чем отличается использование операторов group by, group by rollup, group by rollup cube, grouping, orger by, partition?