Забыли пароль?
  • Главная
  • /
  • Статьи
  • /
  • Оконные функции – то, что должен знать каждый T-SQL программист. Часть 2.

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

Dmitriy Fedyashov

Во второй части статьи мы поговорим о самих функциях, которые применяются для формирования значения. Оконная функция вычисляет значение по набору данных, связанных с текущей строкой, то есть данные из одной группы, если используется 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

 

ID

Amount

Sum

1

100

450

1

200

450

1

150

450

2

100

650

2

300

650

2

200

650

2

50

650

3

150

360

3

200

360

3

10

360

Для удобства окна выделены разным цветом. Все значения в окне имеют одинаковую сумму – сумму всех 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

 

ID

GroupId

Amount

Sum

1

1

100

300

1

1

200

300

1

2

150

450

2

1

100

400

2

1

300

400

2

2

200

650

2

2

50

650

3

1

150

150

3

2

200

360

3

2

10

360

Как видите, теперь каждое окно разделено на группы благодаря полю 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

 

ID

Amount

Sum

1

100

100

1

200

300

1

150

450

2

100

100

2

300

400

2

50

450

2

200

650

3

150

150

3

10

160

3

200

360

Поле 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

 

ID

Amount

AVG

1

100

150

1

200

150

1

150

150

2

100

162

2

300

162

2

200

162

2

50

162

3

150

120

3

200

120

3

10

120

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

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

MIN()

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

1
2
3
4
SELECT ID,
Amount,
MIN(Amount) OVER (Partition BY id ORDER BY id) AS MIN
FROM ForWindowFunc

 

ID

Amount

Min

1

100

100

1

200

100

1

150

100

2

100

50

2

300

50

2

200

50

2

50

50

3

150

10

3

200

10

3

10

10

Как вы видите, в столбце 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

 

ID

Amount

Max

1

100

200

1

200

200

1

150

200

2

100

300

2

300

300

2

200

300

2

50

300

3

150

200

3

200

200

3

10

200

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

 

COUNT()

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

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

 

ID

Amount

Count

1

100

3

1

200

3

1

150

3

2

100

4

2

300

4

2

200

4

2

50

4

3

150

3

3

200

3

3

10

3

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

1
2
3
4
5
SELECT ID,
GroupId,
 Amount,
 COUNT(Amount) OVER (Partition BY id ORDER BY id, GroupId) AS COUNT
FROM ForWindowFunc

 

ID

GroupId

Amount

Count

1

1

100

2

1

1

200

2

1

2

150

3

2

1

100

2

2

1

300

2

2

2

200

4

2

2

50

4

3

1

150

1

3

2

200

3

3

2

10

3

В этом случае интереснее. Давайте рассмотрим первое окно. Для первой и второй строки количество записей составило 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

 

ID

GroupId

Amount

Count

1

1

100

2

1

1

200

2

1

2

150

1

2

1

100

2

2

1

300

2

2

2

200

2

2

2

50

2

3

1

150

1

3

2

200

2

3

2

10

2

 

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

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

 

ID

GroupId

Amount

RANK

1

1

100

1

1

1

200

2

1

2

150

3

2

1

100

1

2

1

300

2

2

2

50

3

2

2

200

4

3

1

150

1

3

2

10

2

3

2

200

3

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

 

1
2
3
4
5
SELECT ID,
GroupId,
Amount,
RANK() OVER (Partition BY id ORDER BY id, GroupId) AS RANK
FROM ForWindowFunc

 

ID

GroupId

Amount

RANK

1

1

100

1

1

1

200

1

1

2

150

3

2

1

100

1

2

1

300

1

2

2

200

3

2

2

50

3

3

1

150

1

3

2

200

2

3

2

10

2

Интересно, что третья строка в первом окне имеет ранг 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

 

ID

GroupId

Amount

DENSE_RANK

1

1

100

1

1

1

200

1

1

2

150

2

2

1

100

1

2

1

300

1

2

2

200

2

2

2

50

2

3

1

150

1

3

2

200

2

3

2

10

2

Вот теперь все, как и должно быть. 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

 

ID

GroupId

Amount

ROW_NUMBER

1

1

100

1

1

1

200

2

1

2

150

3

2

1

100

1

2

1

300

2

2

2

50

3

2

2

200

4

3

1

150

1

3

2

10

2

3

2

200

3

В запросе мы использовали 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

 

ID

GroupId

Amount

ROW_NUMBER

1

1

100

1

1

1

200

2

1

2

150

3

2

1

100

4

2

1

300

5

2

2

50

6

2

2

200

7

3

1

150

8

3

2

10

9

3

2

200

10

Фактически отсутствие предложения 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

Ntile

10

1

50

1

100

1

100

2

150

2

150

2

200

3

200

3

200

4

300

4

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

 

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

LAG() и LEAD()

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

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

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

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

 

id

Amount

Lag

1

100

NULL

1

150

100

1

200

150

2

50

200

2

100

50

2

200

100

2

300

200

3

10

300

3

150

10

3

200

150

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

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

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

 

id

Amount

Lag

1

100

200

1

150

50

1

200

100

2

50

200

2

100

300

2

200

10

2

300

150

3

10

200

3

150

NULL

3

200

NULL

Как вы видите, в запросе мы передаем в функцию 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

 

id

Amount

First

1

100

100

1

150

100

1

200

100

2

50

50

2

100

50

2

200

50

2

300

50

3

10

10

3

150

10

3

200

10

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

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

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

 

id

Amount

First

1

100

100

1

150

100

1

200

100

2

50

100

2

100

100

2

200

100

2

300

100

3

10

100

3

150

100

3

200

100

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

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

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

 

id

Amount

Last

1

100

150

1

200

150

1

150

150

2

100

50

2

300

50

2

200

50

2

50

50

3

150

10

3

200

10

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

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

Похожие статьи:

назад