Структура оконных функций T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Структура оконных функций

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

Секционирование

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

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

Если это не очевидно, то я замечу, что разные функции в одном запросе могут использовать разные определения секционирования. Посмотрите на следующий пример запроса с двумя функциями RANK:

SELECT custid, orderid, val,
    RANK() OVER(ORDER BY val DESC) AS rnk_all,
    RANK() OVER(PARTITION BY custid
              ORDER BY val DESC) AS rnk_cust
FROM Sales.OrderValues;

Заметьте, что первая функция RANK (которая создает атрибут rnkall) полагается на секционирование по умолчанию, а во вторая - (она создает rnkcust) используется явное секционирование по custid. Следующий рисунок наглядно иллюстрирует секции, созданные в этом примере на основе трех результатов вычислений в запросе: одного результата вычисления значения rnkall и двух результатов - rnkcust:

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

Упорядочение

Элемент упорядочения определяет упорядочения вычисления в секции, если это необходимо. В стандартном SQL все функции поддерживают элемент упорядочения. Что касается SQL Server, то поначалу он не поддерживал элемент упорядочения в агрегатных функциях - поддерживалось только секционирование. Поддержка упорядочения в агрегатах появилась в SQL Server 2012.

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

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

Этот рисунок представляет окна только трех операций вычисления ранга. Ясно, что их много больше - 1660, если быть совсем точным. Дело в том, что задействовано 830 строк, и в каждой строке выполняются две операции вычисления ранга. Самое интересное то, что концептуально это выглядит так, как если бы все эти окна сосуществовали одновременно.

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

Кадрирование

Кадрирование по сути является еще одним фильтром, а не ограничителем строк в секции. Оно применяется как к агрегатным функциям, так и к трем функциям сдвига: FIRST_VALUE, LAST_VALUE и NTH_VALUE. Этот элемент поддержки окон можно считать определяющим две точки секции текущей строки на основе данного упорядочения, обеспечивающий кадрирование строк, к которым будет применяться данное вычисление.

К параметрам кадрирования в стандарте относится ROWS или RANGE, который определяет начальный и конечный строки кадра, а также параметр исключения оконного кадра. В SQL Server 2012 появилась поддержка кадрирования с полной реализацией параметра ROWS и частичной - параметра RANGE, но реализация исключения кадров из окна отсутствует.

Параметр ROWS позволяет задать две точки в кадре как смещение (число строк) относительно текущей строки. Параметр RANGE более динамичен и определяет смещения как разницу между значением точки кадра и значением в текущей строке.

Параметр исключения кадров из окна определяет, что делать с текущей строкой и ее «братьями» при наличии связей. Это объяснение кажется туманным и недостаточным, но пока я не хочу углубляться в детали. Далее этих подробностей будет достаточно. На данный момент я просто хочу сформулировать принцип и предоставить простой пример. Вот пример запроса представления EmpOrders, в котором вычисляется количество нарастающим итогом для каждого сотрудника по каждому месяцу:

SELECT empid, ordermonth, qty,
    SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runqty
FROM Sales.EmpOrders;

Заметьте, что в оконной функции применяется агрегат SUM по атрибуту qty, окно секционируется по empid, строки секции упорядочиваются по ordermonth, а затем секция кадрируется от неограниченной предшествующей (нет ограничений снизу) и до текущей строки. Иначе говоря, результат содержит сумму всех предыдущих строк в кадре, включая текущую строку. Этот запрос дает следующий результат (сокращено):

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