Оконные функции – то, что должен знать каждый T-SQL программист. Часть 2.

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

Напомню, окно – это набор строк, по которым производится вычисление функции. Инструкция OVER разбивает весь набор строк на отдельные группы – окна согласно заданному условию.

Поговорим о типах оконных функций. Выделяют три группы по назначению:

• Агрегатные функции: SUM(), MAX(), MIN(), AVG(). COUNT(). Эти функции возвращают значение, полученное путем арифметических вычислений;
• Функции ранжирования: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(). Позволяют получить порядковые номера записей в окне;
• Функции смещения: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Возвращают значение из другой строки окна.

Для демонстрации работы функций я буду использовать простую таблицу:

1
2
3
4
5
6
7

CREATE TABLE ForWindowFunc (ID INT, GroupId INT, Amount INT)
GO
 
INSERT INTO ForWindowFunc (ID, GroupId, Amount)
VALUES(1, 1, 100), (1, 1, 200), (1, 2, 150),
 (2, 1, 100), (2, 1, 300), (2, 2, 200), (2, 2, 50),
 (3, 1, 150), (3, 2, 200), (3, 2, 10);

Агрегатные функции

SUM()

Функция SUM() работает также как и обычная агрегатная функция – суммирует все значения заданного столбца в наборе данных. Однако, благодаря инструкции OVER() мы разбиваем набор данных на окна. Суммирование производится внутри окон согласно порядку, заданному в предложении ORDER BY. Давайте посмотрим на простой пример - сумма по трем группам.

1
2
3

SELECT ID,
 Amount,
 SUM(Amount) OVER (ORDER BY id) AS SUM FROM ForWindowFunc

Для удобства окна выделены разным цветом. Все значения в окне имеют одинаковую сумму – сумму всех Amount в окне.

Давайте добавим еще один столбец в выборку и изменим инструкцию OVER:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
SUM(Amount) OVER (Partition BY id ORDER BY id, GroupId) AS SUM
FROM ForWindowFunc

Как видите, теперь каждое окно разделено на группы благодаря полю GroupId. Каждая группа теперь имеет свою сумму.

А теперь, сделаем нарастающий итог внутри каждого окна:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
SUM(Amount) OVER (Partition BY id ORDER BY id, GroupId, Amount) AS SUM
FROM ForWindowFunc

Поле GroupId нам уже не нужно, поэтому мы убрали его из выборки. Теперь для каждой строки в окне рассчитывается свой итог, который представляет собой сумму текущего значения Amount и всех предыдущих.

AVG()

Эта функция рассчитывает среднее значение. Ее можно применять с предложениями Partition by и Order by.

1
2
3
4

SELECT ID,
Amount,
AVG(Amount) OVER (Partition BY id ORDER BY id) AS AVG
FROM ForWindowFunc

Каждая строка в окне имеет среднее значение Amount, которое рассчитывается по формуле: сумма всех Amount / на количество строк.

Поведение этой функции похоже на SUM().

MIN()

Из названия функции понятно, что она возвращает минимальное значение в окне.

1
2
3
4

SELECT ID,
Amount,
MIN(Amount) OVER (Partition BY id ORDER BY id) AS MIN
FROM ForWindowFunc

Как вы видите, в столбце Min, выводится минимальное значение Amount в окне.

MAX()

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

1
2
3
4

SELECT ID,
Amount,
MAX(Amount) OVER (Partition BY id ORDER BY id) AS MAX
FROM ForWindowFunc

Все предельно понятно. В первой группе максимальный Amount – 200, во второй 300, а в третьей – 200.

COUNT()

Эта функция возвращает количество строк в окне.

1
2
3

SELECT ID,
 Amount,
 COUNT(Amount) OVER (Partition BY id ORDER BY id) AS COUNT FROM ForWindowFunc

Усложним запрос, добавим поле GroupId.

1
2
3
4
5

SELECT ID,
GroupId,
 Amount,
 COUNT(Amount) OVER (Partition BY id ORDER BY id, GroupId) AS COUNT
FROM ForWindowFunc

В этом случае интереснее. Давайте рассмотрим первое окно. Для первой и второй строки количество записей составило 2. Но для третьей строки значение уже равно 3. У нас получилось накопление количества по группам наподобие накопительной суммы.

Если же мы все-таки хотим количество в каждой группе, то GroupId нужно добавить в предложение Partition by.

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
COUNT(Amount) OVER (Partition BY id, GroupId) AS COUNT
FROM ForWindowFunc

Функции ранжирования

RANK()/DENSE_RANK()

Функция RANK() возвращает порядковый номер текущей строки в окне. Однако, есть особенность. Если в предложении Order By попадется несколько равнозначных для правила строки, то все они будут считаться текущей строкой. Таким образом функцию RANK() нужно использовать для ранжирования, а не нумерации строк. Хотя, если правильно задать Order by, то можно нумеровать и физические строки. Например:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
RANK() OVER (Partition BY id ORDER BY id, GroupId, Amount) AS RANK
FROM ForWindowFunc

А вот случай с одинаковыми строками в контексте Order by:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
RANK() OVER (Partition BY id ORDER BY id, GroupId) AS RANK
FROM ForWindowFunc

Интересно, что третья строка в первом окне имеет ранг 3, хотя предыдущие две строки отнесены к первому рангу. Не самая понятная логика. В этом случае лучше использовать DENSE_RANK().

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
DENSE_RANK() OVER (Partition BY id ORDER BY id, GroupId) AS DENSE_RANK
FROM ForWindowFunc

Вот теперь все, как и должно быть. DENSE_RANK() не пропускает ранги если предыдущий ранг содержит несколько строк.

Функции RANK() и DENSE_RANK() не требуют указания поля в скобках.

ROW_NUMBER()

Функция ROW_NUMBER () отображает номер текущей строки в окне. Как и предыдущие две функции, ROW_NUMBER () не требует указания поля в круглых скобках.

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
ROW_NUMBER() OVER (Partition BY id ORDER BY id, GroupId, Amount) AS ROW_NUMBER
FROM ForWindowFunc

В запросе мы использовали Partition by для разделения набора данных на группы. Здесь все понятно и не должно вызвать вопросов.

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

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
ROW_NUMBER() OVER (ORDER BY id, GroupId, Amount) AS ROW_NUMBER
FROM ForWindowFunc

Фактически отсутствие предложения Partition by говорит от том, что весь набор данных является окном.

NTILE()

Функция NTILE() позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках, а предложение ORDER BY определяет, какой столбец используется для определения группы.

К примеруЭто означает, что, если у вас есть 100 строк, и вы хотите создать 4 квартили на основе указанного поля значений, вы можете сделать это легко и посмотреть, сколько строк попадает в каждый квартиль.

Давайте посмотрим пример. В приведенном ниже запросе мы указали, что хотим создать четыре квартили на основе суммы заказа. Затем мы хотим увидеть, сколько заказов попадает в каждый квартиль.

NTILE создает группы на основе следующей формулы:

Количество строк в каждой группе = количество строк в наборе / количество указанных групп

Вот наш пример: в запросе указано всего 10 строк и 4 плитки, поэтому количество строк в каждой плите будет 2,5 (10/4). Поскольку число строк должно быть целым числом, а не десятичным. SQL engine назначит 3 строки для первых двух групп и 2 строки для оставшихся двух групп.

1
2
3

SELECT Amount,
NTILE(4) OVER(ORDER BY amount) AS Ntile
FROM ForWindowFunc

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

Функции смещения

LAG() и LEAD()

Эти две функции позволяют получить предыдущее и следующее значение соответственно. Довольно часто бывает нужно сравнивать текущее значение с предыдущим или следующим в вычисляемых колонках.

В качестве параметров вы можете передать в функцию имя поля и количество строк, которое нужно отступить от текущей и взять значение. Как в SUBSTRING() мы указываем позицию с которой брать символы, так и здесь указываем позицию, из которой взять значение. Если не указывать количество значений, то по умолчанию берется одно.

Итак, функция LAG позволяет получать доступ к данным из предыдущей строки в одном окне.

1
2
3

SELECT id, Amount,
LAG(Amount) OVER(ORDER BY id, amount) AS Lag
FROM ForWindowFunc

В первой строке значение поля Lag навно Null потому, что для этой строки нет предыдущего значения Amount. Для всех последующих строк, берется значение Amount из предыдущей строки.

Функция LEAD работает аналогично, только в другую сторону – берет значение из следующей строки.

1
2
3

SELECT id, Amount,
LEAD(Amount,2) OVER(ORDER BY id, amount) AS Lag
FROM ForWindowFunc

Как вы видите, в запросе мы передаем в функцию LEAD параметр 2. Это значит, что мы получаем второе от текущего значение Amount. Для последних двух строк значение Null, т.к. для них нет следующих значений.

FIRST_VALUE() и LAST_VALUE()

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

1
2
3

SELECT id, Amount,
FIRST_VALUE(Amount) OVER(Partition BY Id ORDER BY Id, amount) AS FIRST
FROM ForWindowFunc

Здесь мы получили первое значение по каждому окну.

А теперь получим первое значение по всему набору данных:

1
2
3

SELECT id, Amount,
FIRST_VALUE(Amount) OVER(ORDER BY Id, amount) AS FIRST
FROM ForWindowFunc

Мы убрали предложение Partition из запроса, тем самым мы определили весь набор данных окном.

А теперь посмотрим на работу функции LAST_VALUE:

1
2
3

SELECT id, Amount,
LAST_VALUE(Amount) OVER(ORDER BY id) AS LAST
FROM ForWindowFunc

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

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