FirebirdSQL logo
 Агрегатные функцииСистемные пакеты 

Согласно SQL спецификации оконные функции (также известные как аналитические функции) являются своего рода агрегатными функциями, не уменьшающими степень детализации.При этом агрегированные данные выводятся вместе с неагрегированными.

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

Предложение OVER может содержать разбивку по группам ("секционирование"), сортировку и рамку окна.

Доступно в

DSQL

Синтаксис
<window_function> ::=
    <aggregate_function> OVER <window_name_or_spec>
  | <window_function_name> ([<expr> [, <expr> ...]]) OVER <window_name_or_spec>

<window_name_or_spec> ::=
  <window_specification> | window_name

<window_function_name> ::=
     <ranking_function>
   | <navigation_function>

<window_specification> ::=
   ([window_name] [<window_partition>] [<window_order>] [<window_frame>])


<window_partition> ::= PARTITION BY <expr> [, <expr> ...]

<window_order> ::=
  ORDER BY <expr> [<direction>] [<nulls_placement>]
        [, <expr> [<direction>] [<nulls_placement>] ...]

<direction> ::= ASC | DESC

<nulls_placement> ::= NULLS {FIRST | LAST}

<window_frame> ::=
  {ROWS | RANGE} <window_frame_extent>

<window_frame_extent> ::=
  <window_frame_start> | <window_frame_between>

<window_frame_start> ::=
  UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW

<window_frame_between> ::=
  BETWEEN <window_frame_bound_1> AND <window_frame_bound_2>

<window_frame_bound_1> ::=
    UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW
  | <expr> FOLLOWING

<window_frame_bound_2> ::=
    <expr> PRECEDING | CURRENT ROW | <expr> FOLLOWING
  | UNBOUNDED FOLLOWING

<aggregate_function> ::= Агрегатные функции

<ranking_function> ::=
    DENSE_RANK
  | RANK
  | PERCENT_RANK
  | CUME_DIST
  | NTILE
  | ROW_NUMBER

<navigation_function> ::=
    LEAD
  | LAG
  | FIRST_VALUE
  | LAST_VALUE
  | NTH_VALUE

<query-spec> ::=
  SELECT
    [<first-clause>] [<skip-clause>]
    [<distinct-clause>]
    <select-list>
    <from-clause>
    [<where-clause>]
    [<group-clause>]
    [<having-clause>]
    [<named-windows-clause>]
    [<order-clause>]
    [<rows-clause>]
    [<offset-clause>] [<limit clause>]
    [<plan-clause>]

<named-windows-clause> ::=
  WINDOW <window-definition> [, <window-definition>] ...

<window-definition> ::=
  window_name AS <window_specification>
Table 1. Параметры оконных функций
Параметр Описание

expr

Выражение.Может содержать столбец таблицы, константу, переменную, выражение, скалярную или агрегатную функцию.Оконные функции в качестве выражения не допускаются.

window_partition

Выражение секционирования.

window_order

Выражение сортировки.

window_frame

Выражение для задания рамки окна.

window_name

Имя окна.

direction

Направление сортировки.

nulls_placement

Положение псевдозначения NULL в отсортированном наборе.

aggregate_function

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

ranking_function

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

navigation_function

Навигационная функция.

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

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

Допустим, у нас есть таблица EMPLOYEE со столбцами ID, NAME и SALARY.Нам необходимо показать для каждого сотрудника, соответствующую ему заработную плату и процент от фонда заработной платы.

Простым запросом это решается следующим образом:

select
    id,
    department,
    salary,
    salary / (select sum(salary) from employee) percentage
from employee
order by id;
Результат
id department salary percentage
-- ---------- ------ ----------
1  R & D       10.00     0.2040
2  SALES       12.00     0.2448
3  SALES        8.00     0.1632
4  R & D        9.00     0.1836
5  R & D       10.00     0.2040

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

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

select
  id,
  department,
  salary,
  salary / sum(salary) OVER () percentage
from employee
order by id;

Здесь sum(salary) OVER () вычисляет сумму всех зарплат из запроса (таблицы сотрудников).

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

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

Синтаксис
<window function>(...) OVER (PARTITION BY <expr> [, <expr> ...])

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

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

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

Example 1. Секционирование в OVER
select
  id,
  department,
  salary,
  salary / sum(salary) OVER (PARTITION BY department) percentage
from employee
order by id;
Результат
id department salary percentage
-- ---------- ------ ----------
1  R & D       10.00     0.3448
2  SALES       12.00     0.6000
3  SALES        8.00     0.4000
4  R & D        9.00     0.3103
5  R & D       10.00     0.3448

Сортировка

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

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

Example 1. Сортировка в OVER
SELECT
  id,
  salary,
  SUM(salary) OVER (ORDER BY salary) AS cumul_salary
FROM employee
ORDER BY salary;
Результат
id salary cumul_salary
-- ------ ------------
3    8.00         8.00
4    9.00        17.00
1   10.00        37.00
5   10.00        37.00
2   12.00        49.00

В этом случае cumul_salary возвращает частичную/накопительную агрегацию (функции SUM). Может показаться странным, что значение 37.00 повторяется для идентификаторов 1 и 5, но так и должно быть.Сортировка (ORDER BY) ключей группирует их вместе, и агрегат вычисляется единожды (но суммируя сразу два значения 10.00). Чтобы избежать этого, вы можете добавить поле ID в конце предложения ORDER BY.

Это происходит потому, что не задана рамка окна, которая по умолчанию, с указанием ORDER BY состоит из всех строк от начала раздела до текущей строки и строк, равных текущей по значению выражения ORDER BY (т.е.RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Без ORDER BY рамка по умолчанию состоит из всех строк раздела.Подробней о рамке окна (кадрах окна) будет рассказано далее.

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

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

Все агрегатные функции могут использовать предложение ORDER BY, за исключением LIST().

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

Example 2. Использование OVER(ORDER BY …​) для кумулятивных сумм
SELECT
  payments.id AS id,
  payments.bydate AS bydate,
  credit.amount AS credit_amount,
  payments.amount AS pay,
  SUM(payments.amount) OVER(ORDER BY payments.bydate) AS s_amount,
  SUM(payments.amount) OVER(ORDER BY payments.bydate,
                                     payments.id) AS s_amount2,
  credit.amount - SUM(payments.amount) OVER(ORDER BY payments.bydate,
                                                     payments.id) AS balance
FROM credit
JOIN payments ON payments.credit_id = credit.id
WHERE credit.id = 1
ORDER BY payments.bydate
Результат
ID BYDATE     CREDIT_AMOUNT PAY    S_AMOUNT S_AMOUNT2 BALANCE
-- ---------- ------------- ------ -------- --------- ----------
1  15.01.2015 1000000       100000  100000  100000    900000
2  15.02.2015 1000000       150000  250000  250000    750000
3  15.03.2015 1000000       130000  400000  380000    620000
4  15.03.2015 1000000        20000  400000  400000    600000
5  15.04.2015 1000000       200000  600000  600000    400000
6  15.05.2015 1000000       150000  750000  750000    250000
7  15.06.2015 1000000       150000 1000000  900000    100000
8  15.06.2015 1000000       100000 1000000 1000000         0