8.1. Расширение команды SELECT для обработки данных

Расширения для оператора SELECT в реляционных СУБД

Производители промышленных реляционных СУБД стремятся расширить возможности аналитической обработки данных в своих диалектах SQL. Обычно расширение таких возможностей SQL выполняется в следующих направлениях:

• Расширение возможностей аналитической обработки в рамках предложения GROUP BY команды SELECT - предложения ROLLUP и CUBE.
• Новые семейства встроенных функций SQL для аналитической обработки данных.
• Добавление встроенных функций для линейных регрессионных моделей данных.
• Добавление CASE выражения для поддержки ветвления обработки колонок в команде SELECT.

Предложения CUBE и ROLLUP делают выполнение запросов и построение отчетов проще в среде ХД. Предложение ROLLUP создает промежуточные суммы (subtotals) в соответствие с возрастающим уровнем агрегации, от наиболее детализированных уровней представления данных к более обобщенным суммам. Предложение CUBE является расширением подобным предложению ROLLUP, позволяющим в одной команде вычислить все возможные комбинации промежуточных сумм. Предложение CUBE может генерировать информацию, необходимую для перекрестных отчетов (cross-tabulation reports) в одном запросе.

Аналитические функции увеличивают потенциал SQL в области статистической обработки данных результирующих множеств запросов. Функции ранжирования включают в себя вычисление куммулятивных распределений, процентных рангов (percent rank) и N-мерных рангов (N-tiles). Вычисления в плавающих окнах (moving window) позволяют работать с кумулятивными агрегатами (moving and cumulative aggregations), такие как суммы и средние величины.

Другие расширения SQL включают в себя семейство функций для вычисления регрессии и CASE выражения. Функции вычисления регрессии включают в себя полный набор вычислений для линейной регрессии. CASE выражения обеспечивают реализацию логики if - then.

Расширение SQL для агрегации данных

Одной из ключевых концепций систем поддержки принятия решений (DSS) и информационных систем руководителя (EIS) является многомерный анализ - анализ объекта во всех необходимых комбинациях измерений. Термин «Измерение» (dimension) используется для обозначения любой категории, используемой для спецификации запроса. Примерами измерений в ХД чаще всего выступают «Время», «География», «Товар», «Подразделение», и «Канал распределения». События или объекты, связанные с конкретными значениями измерений, принято называть фактами. Примерами фактов могут служить «Продажи», «Прибыль», «Количество клиентов», «Объем продукции».

Типичными примерами вопросов в многомерном анализе являются такие, которые будем называть многомерными запросами:

• Показать итоговые продажи всего товара по возрастанию уровня агрегации измерения «География»: от области к стране к региону за период от 2010 до 2018.
• Создать перекрестный отчет (cross-tabular) операций организации, показывающий расширение территории торговых операций в Южной Америке за 2004 – 2018 гг. Включить в него все возможные промежуточные суммы.
• Показать список 10 самых крупных продаж (top 10 sales) в Азии в соответствии с прибылью от продаж за 2019 год для автомобилей, и ранжировать их по комиссионным.

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

Можно выбирать разворачивать (делать сечения) данные (slices of data) из куба. Например, такая операция соответствует получению перекрестного отчета, показанному в Табл. 8.1. Например, региональный менеджер может изучать данные, сравнивая сечения куба по различным рынкам. Менеджер по товарам может сравнивать сечения куба по различным продуктам.

Ответы на многомерные запросы часто требуют доступа к большому количеству данных, агрегации этих данных по уровням иерархии измерений, вычислении частичных сумм по измерениям. Таким образом, аналитические задачи требуют эффективной и удобной агрегации данных.

Возможности агрегирования данных используются не только в многомерном анализе. Обработка транзакций, например, в финансовых или производственных системах (ERP), также генерирует большое число отчетов. Эффективность таких систем возрастает, когда создание отчетов не очень ограничивает нагрузку на систему. В практике финансовых и ERP систем, очень много отчетов генерируется в ночное время, когда число пользователей таких систем значительно снижается.

Для иллюстрации расширений SQL в настоящей лекции используется гипотетическое ХД организации, которая продает и сдает напрокат видеокассеты. В ХД сохраняется информация о действиях организации в нескольких регионах, отлеживаются продажи и прибыль с продаж. Данные сохраняются в трех измерениях – «Время» (Time), «Отдел продаж» (Department) и «Регион» (Region). Временной период данных составляет период от 2000 года до 2019 года, В компании имеется два типа отделов продаж – «Отдел розничных продаж» (Video Sales) и «Отдел видеопроката» (Video Rentals). Регион включает три направления «Центральный» (Central), «Восточный» (East) и «Западный» (West). Таблица фактов «Продажи» (Sales) содержит данные о продажах и прокате видеопродукции компании за 2000-2019 гг.

Таблицы схемы «звезда» для анализа движения товаров.

В Табл. 8.1 ниже приведен типичный отчет, который руководство компании может запросить для анализа деятельности компании за определенный период времени.

Обратим внимание на то, что в этом небольшом отчете генерируется пять частичных сумм и итоговые суммы. Частичные суммы являются скрытыми числами, которые должны быть вычислены для отчета в запросе, который использует агрегатную функцию SUM() и предложение GROUP BY.

Рассмотрим теперь подробнее расширения оператора SELECT, которые упрощают конструирование запросов для построения отчетов, аналогичных приведенному в Табл. 8.1.

Предложение ROLLUP

Предложение (в оригинальной документации Oracle – функция) ROLLUP позволяет в команде SELECT вычислять многоуровневые частичные суммы для специфицированных групп измерений. Также может вычисляться итоговая сумма. Предложение ROLLUP является простым расширением предложения GROUP BY, поэтому синтаксис для его использования прост. Использование предложения ROLLUP очень эффективно.

Синтаксис:

SELECT ... GROUP BY ROLLUP (grouping_column_reference_list)

Действия ROLLUP является следующими: создаются частичные суммы для каждого из раскрываемых уровней от наиболее низкого уровня иерархии к более высокому уровню, и вычисляется итоговая сумма в соответствие с указанным списком колонок в предложении ROLLUP. Предложение ROLLUP рассматривает свои аргументы как упорядоченный список колонок группировки. Сначала, вычисляется стандартное агрегатное значение, указанное в предложении GROUP BY. Затем создаются частичные суммы для уровней атрибутов из списка группировки GROUP BY в порядке возрастания их значений, справа налево по списку колонок группировки. И окончательно, создается итоговая сумма (grand total).

Предложение ROLLUP создает частичные суммы для n+1 уровней, где n есть число колонок группировки. Например, если в запросе указан ROLLUP на колонки группировки измерений «Время» (Time), «Регион» (Region) и «Отдел продаж» (Department) (n=3), то результирующее множество (result set) будет включать в себя строки для 4-х уровней агрегации.

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

SELECT TRUNC(Time, ‘YYYY’) Y1, Region, Department, SUM(Profit) AS Profit 
FROM sales
WHERE TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2017’, ‘YYYY’)  AND TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2018’, ‘YYYY’)  
   GROUP BY ROLLUP(Time, Region, Department);

Результат выполнения запроса (Агрегирование в ROLLUP для трех измерений):

Как видно из примера выше, запрос возвращает следующий набор строк:

• Обычные строки агрегации, которые выдаются предложением GROUP BY без использования ROLLUP;
• Частичные суммы 1-го уровня, агрегированные для «Отдела продаж» (Department) для каждой комбинации измерений «Время» (Time) и «Регион» (Region);
• Частичные суммы 2-го уровня, агрегированные для измерений «Регион» (Region) и «Отдела продаж» (Department) для каждого значения измерения «Время» (Time);
• Строку с итоговой суммой.

Заметим, что NULL-значения показываются только для ясности. В действительности при выводе будут показаны пробелы.

NULL-значения, возвращаемые в результате выполнения предложений ROLLUP и CUBE, не всегда могут толковаться в общепринятом смысле, как неопределенные значения. NULL-значения могут указывать, что строка содержит частичную сумму. Например, первое NULL-значение в Выводе 1 появляется в колонке «Отдел продаж» (Department). Это NULL - значение означает, что строка есть частичная сумма для всех отделов продаж для центрального региона за 2017 год.

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

Можно использовать предложение ROLLUP только для вычисления некоторых частичных сумм. Такие команды с использованием ROLLUP используют синтаксис как показано ниже:

GROUP BY expr1, ROLLUP(expr2, expr3);

В этом случае, предложение ROLLUP создает частичные суммы для (2+1=3) уровней агрегации (aggregation levels), т.е. для уровней (expr1, expr2, expr3), (expr1, expr2) и (expr1). Итоговая сумма (grand total) не создается. Пусть руководству компании требуется отчет о прибыли по всем регионам по всем отделам продаж за 2017-18 гг. без итоговой суммы прибыли. Предложение SELECT для приведенной схемы ХД может выглядеть следующим образом.

SELECT TRUNC(Time, ‘YYYY’) Y1, Region, Department, SUM(Profit) AS Profit 
FROM sales
WHERE TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2017’, ‘YYYY’)  AND TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2018’, ‘YYYY’)  
   GROUP BY Time, ROLLUP (Region, Department);

Результат выполнения запроса (Использование предложения ROLLUP для вывода частичных сумм):

Как видно, запрос возвращает следующее множество строк:

• Обычные строки агрегации, которые выдаются предложением GROUP BY без использования ROLLUP;
• Частичную сумму 1-го уровня агрегации по «Отделам продаж» (Department) для каждой комбинации «Время» (Time) и «Регион» (Region);
• Частичную сумму 2-го уровня агрегации по измерениям «Регион» (Region) и «Отдел продаж» (Department) для каждого значения измерения «Время» (Time);
• Нет строки с итоговой суммой.

Можно вычислить частичные суммы без использования предложения ROLLUP следующим образом:

SELECT TRUNC(Time,’YYYY’), Region, Department, SUM(Profit)
 FROM Sales
 GROUP BY Time, Region, Department
UNION  ALL
 SELECT TRUNC(Time,’YYYY’), Region, '' , SUM(Profit)
 FROM Sales
 GROUP BY Time, Region
UNION ALL
 SELECT TRUNC(Time,’YYYY’), '', '', SUM(Profit)
 FROM Sales
 GROUP BY Time
UNION ALL
 SELECT '', '', '', SUM(Profit)
 FROM Sales;

Как видно из примера выше, для этого требуется для n измерений n+1 SELECT с UNION ALL.

Таким образом, предложение ROLLUP формирует промежуточные частичные суммы, начиная с самого нижнего уровня иерархии, вычисляет итоговую сумму. Уровни иерархии задаются в предложении GROUP BY.

ROLLUP предложение целесообразно использовать для задач, в которых вычисляются промежуточные или частичные суммы:

• В измерениях с иерархической структурой, таких как время или географическое расположение: ROLLUP(y, m, day) или ROLLUP(country, state, city).
• Для быстрой генерации отчетов с суммарными данными.

Обратимся к учебной БД «Отдел кадров» и представлению EMP_DETAILS_VIEW. Запрос

SELECT 
	region_name, 
	country_name,  
	count(*) 
FROM emp_details_view 
GROUP BY region_name, country_name
ORDER BY region_name, country_name;

Вычисляет итоги по регионам и странам:

REGION_NAME		COUNTER_NAME			COUNT(*)	
Americas		Canada				2
Americas		United States of America	68
Europe			Germany				1
Europe			United Kingdom			35

Добавим теперь BY ROLLUP в предложение GROUP BY.

SELECT 
	region_name, 
	country_name,  
	count(*) 
FROM emp_details_view 
GROUP BY ROLLUP(region_name, country_name)
ORDER BY region_name, country_name;

и в результате получим:

REGION_NAME		COUNTER_NAME			COUNT(*)	
Americas		Canada				2
Americas		United States of America	68
Americas		NULL				70
Europe			Germany				1
Europe			United Kingdom			35
Europe			NULL				36
NULL			NULL				106

Как можно увидеть, в результирующем множестве появились три новых строки (были сгенерированы): две строки с количеством по регионам (уровень региона) и строка с количеством по странам. Таким образом, была выполнена детализация по странам и регионам.

Предложение CUBE

Частичные суммы, генерируемые предложением ROLLUP, представляют только часть возможных комбинаций частичных сумм в измерениях. Например, в перекрестном отчете (см. Табл. 15.1) итоги работы отделов продаж по регионам (279,000 и 319,000) не могут быть вычислены в предложении ROLLUP (Time, Region, Department). Для этого нужно изменить порядок колонок группировки в предложении ROLLUP: ROLLUP (Time, Department, Region). Простой способ генерации полного набора частичных сумм для перекрестных отчетов состоит в использовании расширения CUBE предложения GROUP BY.

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

Синтаксис:

SELECT ...  GROUP BY
  CUBE (grouping_column_reference_list)

Из примера ниже видно, что CUBE берет указанный набор колонок группировки и создает частичные суммы для всех возможных комбинаций значений этих колонок. С точки зрения многомерного анализа, предложение CUBE генерирует все частичные суммы, которые могут быть вычислены для куба данных с указанными измерениями. Если указывается CUBE(Time, Region, Department), то результирующее множество запроса будет включать все значения, которые включаются в аналогичную конструкцию ROLLUP плюс набор дополнительных комбинаций.

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

SELECT TRUNC(Time, ‘YYYY’) Y1, Region, Department, SUM(Profit) AS Profit 
FROM sales
WHERE TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2017’, ‘YYYY’)  AND TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2018’, ‘YYYY’)  
   GROUP BY CUBE(Time, Region, Department);

Результат выполнения запроса (Выполнение CUBE с агрегацией по трем измерениям)

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

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

Синтаксис:

GROUP BY expr1, CUBE(expr2, expr3);

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

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

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

SELECT TRUNC(Time, ‘YYYY’) Y1, Region, Department, SUM(Profit) AS Profit 
FROM sales
WHERE TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2017’, ‘YYYY’)  AND TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2018’, ‘YYYY’)  
   GROUP BY Time CUBE(Region, Department);

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

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

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

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

Обратимся к учебной БД «Отдел кадров» и представлению EMP_DETAILS_VIEW. В результирующем множестве запроса

SELECT 
region_name, first_name,  count(*) 
FROM emp_details_view 
WHERE first_name LIKE 'Da%'
GROUP BY ROLLUP(region_name, first_name);

REGION_NAME		LAST_NAME		COUNT(*)
Europe			David			2
Europe			Danielle		1
Europe			NULL			3
Americas		David			1
Americas		Daniel			1
Americas		NULL			2
NULL			NULL			5

можно видеть итоги по регионам, но не видны детали по сотруднику David. Заменим ROLLUP на CUBE, чтобы увидеть эти детали:

SELECT 
	region_name, 
	first_name,  
	count(*) 
FROM emp_details_view 
WHERE first_name LIKE 'Da%'
GROUP BY CUBE(region_name, first_name);

И получим результирующее множество:

REGION_NAME	LAST_NAME	COUNT(*)
SUMMARY		TOTAL		5
SUMMARY		David		3
SUMMARY		Daniel		1
SUMMARY		Danielle	1
Europe		TOTAL		3
Europe		David		2
Europe		Danielle	1
Americas	TOTAL		2
Americas	David		1
Americas	Daniel		1

Функция GROUPING

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

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

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

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

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

SELECT SELECT TRUNC(Time, ‘YYYY’) Y1, Region, Department, SUM(Profit) AS Profit,
  GROUPING (Time) as T, 
  GROUPING (Region) as R, 
  GROUPING (Department) as D
  FROM Sales
WHERE TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2017’, ‘YYYY’)  AND TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2018’, ‘YYYY’)  
GROUP BY ROLLUP (Time, Region, Department);

Результат выполнения запроса (Использование функции GROUPING ):

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

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

Результат выполнения запроса:

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

Использование GROUPING функции в комбинации с CASE выражением и функцией преобразования значений одних типов данных CAST ({expr | MULTISET (subquery) } AS type_name ) позволяет решить эту проблему. Выражение CASE выполняет оценку списка условий и возвращает один из нескольких возможных выражений результатов. Функция CAST выполняет разграничение NULL-значений в результирующем множестве.

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

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
WHERE TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2017’, ‘YYYY’)  AND TRUNC(Time, ‘YYYY’) = TO_DATE(’01.01.2018’, ‘YYYY’)  
GROUP BY CUBE(Time, Region);

Результат выполнения запроса (Разграничение агрегатных и хранимых 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.

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);

Результат выполнения запроса (использование функции GROUPING для фильтрации результата в частичных суммах и итоговой сумме):

Обратимся к учебной БД «Отдел кадров» и представлению EMP_DETAILS_VIEW. В запрос, вычисляющий итоги с ROLLUP, добавим функцию GROUPING:

SELECT 
	decode(grouping(region_name),0,region_name,'GRAND') AS region_name,
	decode(grouping(country_name),0,country_name,'TOTAL') AS country_name,  
	count(*) 
FROM emp_details_view 
GROUP BY ROLLUP(region_name, country_name);

REGION_NAME	COUNTER_NAME			COUNT(*)	
Europe		Germany				1
Europe		United Kingdom			35
Europe		TOTAL				36
Americas	Canada				2
Americas	United States of America	68
Americas	TOTAL				70
GRAND		TOTAL				106

Как можно увидеть, функция DECODE в сочетании с функцией GROUPING позволяет заменить неопределенные значения в сгенерированных дополнительных строках на слова, поясняющие смысл вычисленных итогов по регионам и странам.

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

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

Пример команды SELECT.

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

Результат выполнения запроса (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 применяются ко всем строкам результирующего множества.

8.2. Предложение MODEL для обработки многомерных данных

В диалекте SQL Oracle 11g предложение MODEL задает выражение, которое позволяет представлять результирующее множество в виде многомерного куба и задавать выражения для расчета его произвольных ячеек.

Синтаксис (сокращенный):

MODEL [IGNORE NAV] [RETURN UPDATED ROWS]
    [PARTITION BY (partition_column_1, ...)]
    DIMENSION BY (dimension_column_1, ...)
    MEASURES (measured_column_1, ...)
    RULES [AUTOMATIC ORDER | ITERATE (value) [UNTIL (expression)]] (  rule_1, ...  );

Определим обязательные позиции.

Фраза DIMENSION BY определяет колонки, с помощью которых для любой ячейки многомерного куба можно сопоставить единственную строку. Нельзя использовать алиасное имя колонки предложения SELECT.

Фраза MEASURES определяет, какие колонки результирующего множества будут использоваться для доступа к данным. Нельзя ссылаться на колонку, которая отсутствует в DIMENSION BY и MEASURES.

Фраза RULES задает правила обработки строк результирующего множества.

Предложение PARTITION BY задает разбиение результирующего множества на секции, строки которой обрабатываются отдельно от строк других секций по заданным правилам (см. Лекцию 4).

Таким образом, с помощью предложения MODEL можно задать многомерный массив на результирующем множестве и работать с ним как с электронной таблицей. Это предмет обсуждения отдельной лекции, а для подробного изучения предложения MODEL следует обратиться к документации [].

Приведем простой пример использования этого расширения команды SELECT. Обратимся к таблице EMPLOYEES схемы HR и построим на ней куб по измерению employee_id, job_id с метрикой salary, как показано ниже.

SELECT employee_id, job_id, salary as sal 
FROM EMPLOYEES
MODEL
DIMENSION BY (employee_id, job_id) 
MEASURES (salary)
RULES ();
ORDER BY job_id;

Результат выполнения запроса (фрагмент):

EMPLOYEE_ID		JOB_ID		SAL
103			IT_PROG		9000
104			IT_PROG		6000
105			IT_PROG		4800
106			IT_PROG		4800
107			IT_PROG		4200
205			AC_MGR		12008
…..
115			PU_CLERK	3100
116			PU_CLERK	2900
117			PU_CLERK	2800
118			PU_CLERK	2600
119			PU_CLERK	2500

…..

В следующем запросе давайте назначим всем сотрудникам, работающими агентами по продажам, оклад в размере 3200 у.е.

SELECT employee_id, job_id, salary as sal 
FROM EMPLOYEES
MODEL
DIMENSION BY (employee_id, job_id) 
MEASURES (salary)
RULES (salary[any,'PU_CLERK']=3200);
ORDER BY job_id;

Результат выполнения запроса (фрагмент):

EMPLOYEE_ID		JOB_ID		SAL
103			IT_PROG		9000
104			IT_PROG		6000
105			IT_PROG		4800
…..
116			PU_CLERK	3200
117			PU_CLERK	3200
119			PU_CLERK	3200
115			PU_CLERK	3200
118			PU_CLERK	3200

Резюме

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

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

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

Также было представлено предложение MODEL команды SELECT, которое позволяет строить многомерный куб (массив ячеек) на результирующем множестве.