Оконные (аналитические) функции

Оконная функция выполняет вычисления над списком строк в таблице, которые как-то относятся к текущей строке. Это сравнимо с типом вычислений, которые могут быть выполнены с помощью какой-либо агрегатной функции. Но в отличие от обычных агрегатных функций, использование оконной функции не заставляет строки группироваться в одну; строки сохраняют свои отдельные значения. Другими словами, оконная функция позволяет получить доступ более чем только к текущей строке результата запроса.

Синтаксически вызов оконной функции есть указание её имени, за которым всегда следует ключевое слово OVER() с возможными аргументами внутри скобок. В этом и заключается её синтаксическое отличие от обычной функции или агрегатной функции. Оконные функции могут находиться только в списке SELECT и предложении ORDER BY.

Предложение OVER может содержать секционирование, сортировку и рамку окна.

Простой пример

Начнем с простого примера, который показывает как меняются результаты в зависимости от указаний внутри OVER.

SELECT A, B, C,
    SUM(C) OVER(),
    SUM(C) OVER(ORDER BY A, B) AS S_O_AB,
    SUM(C) OVER(PARTITION BY A) AS S_P_A,
    SUM(C) OVER(PARTITION BY A ORDER BY B) AS S_P_A_O_B
FROM T

Потенциальные результаты могут быть следующими.

Столбцы A, B, C содержат некие данные. В запросе использована оконная функция SUM по столбцу C, но с разными указаниям к выполнению суммирования. Посмотрим на них более внимательно.

SUM(C) OVER() не содержит никаких указаний и для каждой строки выдает один и тот же результат (столбец SUM в результате): сумму всех значений столбца C. Такое же значение мы бы получили при простом использовании функции SUM как агрегатной и без предложения GROUP BY. Но заметьте что здесь количество строк не уменьшается и не равно количеству групп. Значение выдается для каждом строки.

SUM(C) OVER(ORDER BY A, B) (столбец S_O_AB) содержит указание к порядку использования значений C в соответствие со значениями столбцов A и B. В таком случае для каждой строки результат SUM(C) будет выдавать сумму значений столбца C с первого до текущего по указанному порядку суммирования. Последнее значение как и следует ожидать будет равно сумме всех значений C.

SUM(C) OVER(PARTITION BY A) (столбец S_P_A) снова не содержит указания к порядку выполнения и там мы видим снова одинаковые суммы, но разбитые на группы. Это потому что есть указания к сегментированию, разделению общего набора данных на части. Суммируются значения столбца C всех строк, в которых значения поля A равно значению поля A текущей строки.

SUM(C) OVER(PARTITION BY A ORDER BY B) (столбец S_P_A_O_B) содержит указания и к сегментированию, и к порядку. В таком случае сумма вычисляется по значениям, входящим в указанную группу начиная с первого до текущего, в указанном порядке.

Суммы, вычисленные по порядку еще называют сумма, вычисленная нарастающим итогом.

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

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

Допустим, у нас есть таблица EMPLOYEE со столбцами:

• DEPT_NO,
• FULL_NAME
• SALARY.

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

SELECT FULL_NAME, DEPT_NO, 
  SALARY,
  100 * SALARY / (SELECT SUM(SALARY) FROM EMPLOYEE) PERCENTAGE
FROM EMPLOYEE
ORDER BY FULL_NAME;

Результат:

Запрос повторяется и может работать довольно долго, особенно если EMPLOYEE является сложным представлением.

Этот запрос может быть переписан в более быстрой и элегантной форме с использованием оконных функций:

SELECT FULL_NAME, DEPT_NO,
  SALARY,
  100 * SALARY / SUM(SALARY) OVER () PERCENTAGE
FROM EMPLOYEE
ORDER BY FULL_NAME;

Здесь SUM(SALARY) OVER () вычисляет сумму всех зарплат из запроса.

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

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

Синтаксис:

<оконная функция>(...) OVER (PARTITION BY <выражение> [, <выражение> ...])

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

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

SELECT FULL_NAME, DEPT_NO,
  SALARY,
  SALARY / SUM(SALARY) OVER (PARTITION BY DEPT_NO) PERCENTAGE
FROM EMPLOYEE
ORDER BY FULL_NAME;

Результат:

Сортировка

Предложение ORDER BY может быть использовано с секционированием или без него. Предложение ORDER BY внутри OVER задаёт порядок, в котором оконная функция будет обрабатывать строки. Этот порядок не обязан совпадать с порядком вывода строк.

Синтаксис:

<оконная функция>(...) OVER (ORDER BY <выражение> [, <выражение> ...])

Есть ещё одно важное понятие, связанное с оконными функциями: для каждой строки существует набор строк в её секции, называемый рамкой окна (кадры окна, frame). По умолчанию, с указанием ORDER BY рамка состоит из всех строк от начала секции до текущей строки и строк, равных текущей по значению выражения ORDER BY. Без ORDER BY рамка по умолчанию состоит из всех строк секции.

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

Пример:

SELECT EMP_NO, SALARY,
  SUM(SALARY) OVER (ORDER BY SALARY) AS CUMUL_SALARY
FROM EMPLOYEE
ORDER BY SALARY;

Результат:

В этом случае CUMUL_SALARY возвращает частичную/накопительную агрегацию (функции SUM). Может показаться странным, что значение 249830 повторяется для идентификаторов 114 и 144, но так и должно быть. Сортировка (ORDER BY) ключей группирует их вместе, и агрегат вычисляется единожды (но суммируя сразу два значения 35000). Чтобы избежать этого, вы можете добавить поле EMP_NO в конце предложения ORDER BY.

SELECT EMP_NO, SALARY,
    SUM(SALARY) OVER (ORDER BY SALARY, EMP_NO) AS CUMUL_SALARY
FROM EMPLOYEE
ORDER BY SALARY;

И тогда результат будет отличаться.

Вы можете использовать несколько окон с различными сортировками, и дополнять предложение ORDER BY опциями ASCи DESC, а также NULLS FIRST и NULLS LAST.

С секциями предложение ORDER BY работает таким же образом, но на границе каждой секции агрегаты сбрасываются.

Рамка окна

Набор строк внутри секции, которым оперирует оконная функция называется рамкой окна. Рамка окна состоит из трёх частей: единица (unit), начальная граница и конечная граница. В качестве единицы может быть использовано ключевые слова RANGE или ROWS, которые указывают каким образом определены границы окна. Границы окна определяются следующими выражениями:

• <значение> PRECEDING
• <значение> FOLLOWING
• CURRENT ROW

Предложения ROWS и RANGE требуют, чтобы было указано предложение ORDER BY, а иначе для агрегатных функций рамка окна состоит из всех строк секции.

Синтаксис:

Стандартное, для оконной функции, начало:

<оконная функция>(...) OVER (ORDER BY <выражение> [, <выражение> ...] <описание рамки окна>)

Рамка окна описывается: одним из следующих способов.

С помощью описания предшествующих строк:

    ROWS UNBOUNDED PRECEDING
    ROWS <значение> PRECEDING
    ROWS CURRENT ROW

С помощью описания предшествующих значений:

    RANGE UNBOUNDED PRECEDING
    RANGE <значение> PRECEDING
    RANGE CURRENT ROW

С помощью описание диапазона строк:

    ROWS BETWEEN 
          { UNBOUNDED PRECEDING | 
          <значение> PRECEDING |
          <значение> FOLLOWING | 
          CURRENT ROW }
      AND 
          { UNBOUNDED FOLLOWING |
          <значение> PRECEDING |
          <значение> FOLLOWING | 
          CURRENT ROW })

С помощью описание диапазона значений:

    2) RANGE BETWEEN
          { UNBOUNDED PRECEDING | 
          <значение> PRECEDING |
          <значение> FOLLOWING | 
          CURRENT ROW }
      AND 
          { UNBOUNDED FOLLOWING |
          <значение> PRECEDING |
          <значение> FOLLOWING | 
          CURRENT ROW })

Предложение ROWS ограничивает строки внутри секции путем указания фиксированного числа строк, предшествующих или следующих после текущей строки, а предложение RANGE логически ограничивает строки внутри секции путем указания диапазона значений в отношении к значению текущей строки. Предшествующие и последующие строки определяются на основании порядка, заданного в предложении ORDER BY.

Если рамка окна задаётся с помощью предложения RANGE, то предложение ORDER BY может содержать только одно выражение числового типа или типа DATE, TIME или TIMESTAMP. Для границ <значение> PRECEDING и <значение> FOLLOWING значения вычитаются и добавляются соответственно к значению указанному в ORDER BY, таким образом получаются границы значений для рамки. Затем все строки внутри секции между границам считаются частью результирующей рамки окна и по ним вычисляется оконная функция.

Если рамка окна задаётся с помощью предложения ROWS, то на предложение ORDER BY не накладывается ограничений на количество и типы выражений. В этом случае значение в <значение> PRECEDING указывает количество строк предшествующее текущей строке, а соответственно в <значение> FOLLOWING указывает количество строк после текущей строки.

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

SELECT I, J,
  SUM(J) OVER( ORDER BY I RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS S1,
  SUM(J) OVER( ORDER BY J RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS S2,
  SUM(J) OVER( ORDER BY J RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING ) AS S22,
  SUM(J) OVER( ORDER BY I ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS S3,
  SUM(J) OVER( ORDER BY J ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS S4
FROM T

И выдает следующий результат.

Фраза UNBOUNDED PRECEDING указывает, что окно начинается с первой строки секции. UNBOUNDED PRECEDING может быть указано только как начальная точка окна.

Фраза UNBOUNDED FOLLOWING указывает, что окно заканчивается последней строкой секции. UNBOUNDED FOLLOWING может быть указано только как конечная точка окна.

Фраза CURRENT ROW указывает, что окно начинается или заканчивается на текущей строке при использовании совместно с предложением ROWS или что окно заканчивается на текущем значении при использовании с предложением RANGE. CURRENT ROW может быть задана и как начальная, и как конечная точка.

Предложение BETWEEN используется совместно с ключевым словом ROWS или RANGE для указания нижней (начальной) или верхней (конечной) граничной точки окна. Верхняя граница не может быть меньше нижней границы.

Если указана только начальная точка окна, то конечной точкой окна считается CURRENT ROW. Например, если указано ROWS 1 PRECEDING, то это аналогично указанию ROWS BETWEEN 1 PRECEDING AND CURRENT ROW.

Некоторые оконные функции игнорируют выражение рамки, например, ROW_NUMBER.

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

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

• Если верхняя и нижняя границы фиксированы относительно текущей строки, например 1 строка до текущей и 2 после текущей, то получаем скользящий агрегат. В этом случае размер окна фиксирован, а само окно скользит.

Именованные окна

Для того чтобы не писать каждый раз сложные выражения для задания окна, имя окна можно задать в предложении WINDOW. Имя окна может быть использовано в предложении OVER для ссылки на определение окна. Кроме того, оно может быть использовано в качестве базового окна для другого именованного или встроенного (в предложении OVER) окна.

Окна с предложениями RANGE или ROWS не могут быть использованы в качестве базового окна (но могут быть использованы в предложении OVER <имя окна>). Окно, которое использует ссылку на базовое окно, не может иметь предложение PARTITION BY и переопределять сортировку с помощью предложения ORDER BY.

Другими словами, базовое окно может определять секции и сортировку, а наследующие окна могут добавлять рамки в виде предложений RANGE и ROWS. Сортировку они могут добавлять только если ее нет в базовом окне.

Рассмотрим следующий запрос.

SELECT
    EMP_NO,
    DEPT_NO,
    SALARY,
    COUNT(*) OVER W1,
    FIRST_VALUE(SALARY) OVER W2,
    LAST_VALUE(SALARY) OVER W2,
    SUM(SALARY) OVER (W2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM EMPLOYEE
WINDOW 
    W1 AS (PARTITION BY DEPT_NO), 
    W2 AS (W1 ORDER BY SALARY)
ORDER BY DEPT_NO, SALARY

В этом запросе используются четыре оконные функции и два окна:

• W1 определяет секции по полю DEPT_NO
• W2 унаследовано от W1 и, следовательно, тоже определяет секции по полю DEPT_NO, но дополняет его сортировкой по полю SALARY.

Функция COUNT использует окно W1 и по вычисляет общее количество записей для каждого отдела. Функции FIRST_VALUE и LAST_VALUE фактически показывают рамки окна W2 внутри секций. Рамками в данном случае будут являться строки от первой до текущей, так как указана сортировка. Функция SUM определяет сумма зарплат из строк рамки, полученной из окна W2, но с переопределенными границами окна. Эта функция суммирует записи от текущей до следующей (если она есть).

Запрос выдаст следующий результат.

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

1) OVER (W2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
2) OVER (W1 ORDER BY SALARY ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
3) OVER (PARTITION BY DEPT_NO ORDER BY SALARY ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)

Ранжирующие функции

Ранжирующие функции вычисляют некоторый ранг или относительный ранг внутри секции окна.

ROW_NUMBER

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

Например запрос,

SELECT EMP_NO, DEPT_NO, SALARY,
    ROW_NUMBER() OVER (ORDER BY SALARY)
FROM EMPLOYEE

присвоит номер каждой строке

Тот же запрос, но с секционированием по номерам отделов.

SELECT EMP_NO, DEPT_NO, SALARY,
    ROW_NUMBER() OVER (PARTITION BY DEPT_NO ORDER BY SALARY)
FROM EMPLOYEE

присвоит номера каждой строке внутри секции с одним отделом.

RANK

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

Например, запрос:

SELECT EMP_NO, DEPT_NO, SALARY,
    RANK() OVER (ORDER BY SALARY)
FROM EMPLOYEE
WHERE SALARY > 33000

вернет такой результат:

DENSE_RANK

Возвращает ранг строки в секции без промежутков в ранжировании. Строки с одинаковыми значениями получают одинаковый ранг в пределах секции. Ранг строки равен количеству различных значений рангов в секции, предшествующих текущей строке, увеличенному на единицу.

Например, запрос:

SELECT EMP_NO, DEPT_NO, SALARY,
    RANK() OVER (ORDER BY SALARY)
FROM EMPLOYEE
WHERE SALARY > 33000

вернет такой результат:

NTILE

Принимает один параметр — количество групп, на которые необходимо разделить каждую секцию.

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

Например, запрос:

SELECT EMP_NO, DEPT_NO, SALARY,
    NTILE(25) OVER (ORDER BY SALARY)
FROM EMPLOYEE

вернет такой результат:

Навигационные функции

FIRST_VALUE и LAST_VALUE

Функция FIRST_VALUE возвращает первое значение из упорядоченного набора значений.

Функция LAST_VALUE возвращает последнее значение из упорядоченного набора значений рамки окна.

LAG и LEAD

LAG(<выражение> [, <смещение> [, <default>]]) OVER (...)
LEAD(<выражение> [, <смещение> [, <default>]]) OVER (...)

Эти функции обеспечивают доступ к строке с заданным физическим смещением перед или после текущей строки (LAG и LEAD соответственно). Если смещение указывает за пределы секции, то будет возвращено значение , которое по умолчанию равно NULL. Параметр <выражение> может содержать столбец таблицы, константу, переменную, выражение,неагрегатную функцию. Параметр <смещение> — количество строк до строки перед (LAG) или после (LEAD) текущей строки, из которой необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1. <смещение> может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целая положительная величина.

NTH_VALUE

NTH_VALUE(<выражение> [, <смещение> ]) [FROM FIRST | FROM LAST]
OVER  (...)

Функция NTH_VALUE возвращает N-ое значение, начиная с первой (опция FROM FIRST) или последней (опция FROM LAST) записи. По умолчанию используется опция FROM FIRST. Смещение 1 от первой записи будет эквивалентно функции FIRST_VALUE, смещение 1 от последней записи будет эквивалентно функции LAST_VALUE. Параметр <выражение> может содержать столбец таблицы, константу, переменную, выражение,неагрегатную функцию. Параметр <смещение> — номер записи, начиная с первой (опция FROM FIRST) или последней (опция FROM LAST) записи.

Пример

В качестве примера, рассмотрим запрос:

SELECT EMP_NO, SALARY,
    FIRST_VALUE(SALARY) OVER (ORDER BY SALARY),
    LAST_VALUE(SALARY) OVER W,
    NTH_VALUE(SALARY, 2) OVER W,
    LAG(SALARY) OVER (ORDER BY SALARY),
    LEAD(SALARY) OVER (ORDER BY SALARY)
  FROM EMPLOYEE
    WINDOW W AS (ORDER BY SALARY ROWS 
      BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ORDER BY SALARY

Запрос вернет следующий результат:

Контрольные вопросы

1. На сколько сократиться количество возвращаемых строк, если использовать оконные функции с секционированием?
2. Как отличить агрегатную функцию от оконной?
3. Можно ли использовать разные секции оконных функций в одном запросе?
4. Что означает враза “нарастающим итогом”?
5. Для чего используется секционирование?
6. Какой смысл несет сортировка?
7. Что называют рамкой окна?
8. Как можно указать границы рамки?
9. Чем отличается диапазон значений от диапазона строк?
10. Для какой цели используются именованные окна?
11. Какие ограничения есть у базовых окон?
12. Какие ограничения есть у наследующих окон?
13. В каком порядке функция ROW_NUMBER присваивает номера строкам?
14. В чем разница RANK и DENSE_RANK?
15. Что произойдет, если NTILE не сможет поделить строки секции на группы равного размера?
16. Если указать функции LAG отрицательное смещение, будет ли она эквивалентна функции LEAD?