SQL в хранилищах данных: аналитическая обработка данных

Оконные функции

Оконные функции определены в стандарте ISO SQL. В СУБД семейства MS SQL Server предоставляются ранжирующие и статистические оконные функции. Окно — это набор строк, определяемый пользователем. Оконная функция вычисляет значение для каждой строки в результирующем наборе, полученном из окна.

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

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

Предложение OVER определяет секционирование и упорядочение набора строк до применения соответствующей оконной функции. В качестве оконных функций используются агрегатные и статистические ( SUM, AVG, MAX, MIN, COUNT ), ранжирующие функции. Каждая из ранжирующих функций ROW_NUMBER, DENSE_RANK, RANK и NTILE задействует предложение OVER (см. сл. раздел настоящей лекции).

Синтаксис:

• Для ранжирующих оконных функций

  < OVER_CLAUSE > ::= OVER ( [PARTITION BY value_expression, … [n] ] <ORDER BY expression>)
• Для агрегатных функций

  < OVER_CLAUSE > ::= OVER ( [PARTITION BY value_expression, … [n] ]

PARTITION BY разделяет результирующий набор на секции. Оконная функция применяется к каждой секции отдельно, и вычисление начинается заново для каждой секции. Если это предложение опущено, функция интерпретирует все результирующее множество как одну группу.

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

Предложение ORDER BY задает порядок для ранжирующей оконной функции. Если предложение ORDER BY используется в контексте ранжирующей оконной функции, оно может ссылаться только на столбцы, доступные через предложение FROM. Указывать положение имени или псевдонима столбца в списке выборки с помощью целого числа нельзя. Предложение ORDER BY не может работать со статистическими оконными функциями.

В одном запросе с одним предложением FROM может использоваться несколько статистических или ранжирующих оконных функций. Однако предложение OVER для каждой функции может применять свое секционирование и упорядочение. Предложение OVER не может работать со статистической функцией CHECKSUM.

Семантика NULL-значений оконных функций соответствует семантике NULL-значений агрегатных функций SQL.

Статистические оконные функции

Покажем на примере, как используются статистические оконные функции.

Пример. 16.4. Статистические оконные функции.

Пусть в ХД имеется таблица фактов "Позиции счетов" (OrderDetail), содержащая номер позиции (OrderID), идентификатор товара (ProductID), количество товара (OrderQt) и стоимость товара (Price). Физическая структура таблицы приведена на рис. 23.4.

Рис. 23.4. Физическая структура таблицы фактов "Финансы" (Finance)

Пусть необходимо для двух позиций счетов 43659 и 43664 посчитать для каждого проданного товара общее количество проданного товара, среднее количество каждого проданного товара, минимальное и максимальное количество проданного товара.

Следующий запрос решает поставленную задачу с использованием оконных функций.

SELECT OrderID, ProductID, OrderQty
	,SUM(OrderQty) OVER(PARTITION BY OrderID) AS 'Итого'
	,AVG(OrderQty) OVER(PARTITION BY OrderID) AS 'Среднее' 
	,COUNT(OrderQty) OVER(PARTITION BY OrderID) AS 'Кол-во'
	,MIN(OrderQty) OVER(PARTITION BY OrderID) AS 'Min'
	,MAX(OrderQty) OVER(PARTITION BY OrderID) AS 'Max'
FROM OrderDetail
WHERE OrderID IN(43659,43664);
GO

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

Вывод 3.

Пусть руководство организации требует подсчитать процент проданного товара по позиции 43659. Следующий запрос с использованием оконных функций решает поставленную задачу.

SELECT OrderID, ProductID, OrderQty
	,SUM(OrderQty) OVER(PARTITION BY OrderID) AS 'Итого'
	,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY 	OrderID) *100 AS DECIMAL(5,2))AS 'Процент проданного товара'
FROM OrderDetail
WHERE OrderID = 43659;

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

Вывод 4.

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