Skip to content

Latest commit

 

History

History
1255 lines (1031 loc) · 51.2 KB

_fblangref-functions-window.adoc

File metadata and controls

1255 lines (1031 loc) · 51.2 KB

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

Согласно 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 2. Сортировка в 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 3. Использование 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

Рамка окна

Набор строк внутри секции, которым оперирует оконная функция, называется рамкой окна (кадры окна). Рамка окна определяет, какие строки следует учитывать для текущей строки при оценке оконной функции.

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

  • <expr> PRECEDING

  • <expr> FOLLOWING

  • CURRENT ROW

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

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

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

    Затем все строки (внутри секции) между границам считаются частью результирующей рамки окна.

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

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

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

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

Note

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

Некоторые оконные функции игнорируют выражение рамки:

  • ROW_NUMBER, LAG и LEAD всегда работают как ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  • DENSE_RANK, RANK, PERCENT_RANK и CUME_DIST работают как RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  • FIRST_VALUE, LAST_VALUE и NTH_VALUE работают на рамке, но RANGE работает идентично ROWS.

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

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

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

Окна диапазона

Окна диапазона объединяют строки в соответствии с заданным порядком. Например, если рамка окна задана выражением RANGE 5 PRECEDING, то будет сгенерировано перемещающееся окно, включающее предыдущие строки группы, значение которых меньше текущего не более чем на 5.

Example 4. Использование окон диапазона
SELECT
    id,
    salary,
    SUM(salary) OVER() AS s1,
    SUM(salary) OVER(ORDER BY salary) AS s2,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s3,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s4,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s5,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s6,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s7,
    SUM(salary) OVER(ORDER BY salary RANGE 1 PRECEDING) AS s8
FROM
    employee
ID  SALARY     S1      S2      S3      S4      S5      S6      S7      S8
-------------------------------------------------------------------------
3     8.00  49.00    8.00    8.00   49.00   49.00   17.00   17.00    8.00
4     9.00  49.00   17.00   17.00   41.00   49.00   29.00   37.00   17.00
1    10.00  49.00   37.00   37.00   32.00   49.00   20.00   29.00   29.00
5    10.00  49.00   37.00   37.00   32.00   49.00   20.00   29.00   29.00
2    12.00  49.00   49.00   49.00   12.00   49.00   12.00   12.00   12.00

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

Окна строк

Окна срок задаются в физических единицах, строках. Например, если рамка окна задана выражением ROWS 5 PRECEDING, то окно будет включать в себя до 6 строк: текущую и пять предыдущих (порядок определяется конструкцией ORDER BY).

Example 5. Использование окон диапазона
SELECT
    id,
    salary,
    SUM(salary) OVER() AS s1,
    SUM(salary) OVER(ORDER BY salary) AS s2,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s3,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s4,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s5,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s6,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s7,
    SUM(salary) OVER(ORDER BY salary ROWS 1 PRECEDING) AS s8
FROM
    employee
ID SALARY      S1      S2      S3      S4      S5      S6      S7      S8
-------------------------------------------------------------------------
3    8.00   49.00    8.00    8.00   49.00   49.00   17.00   17.00    8.00
4    9.00   49.00   17.00   17.00   41.00   49.00   19.00   27.00   17.00
1   10.00   49.00   37.00   27.00   32.00   49.00   20.00   29.00   19.00
5   10.00   49.00   37.00   37.00   22.00   49.00   22.00   32.00   20.00
2   12.00   49.00   49.00   49.00   12.00   49.00   12.00   22.00   22.00

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

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

Example 6. Использование именованных окон
SELECT
    id,
    department,
    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) AS s
FROM employee
WINDOW w1 AS (PARTITION BY department),
       w2 AS (w1 ORDER BY salary)
ORDER BY department, salary;

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

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

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

Функции ранжирования могут быть использованы для создания различных типов инкрементных счётчиков. Рассмотрим SUM(1) OVER (ORDER BY SALARY) в качестве примера того, что они могут делать, каждая из них различным образом. Ниже приведён пример запроса, который позволяет сравнить их поведение по сравнению с SUM.

SELECT
  id,
  salary,
  DENSE_RANK() OVER (ORDER BY salary),
  RANK() OVER (ORDER BY salary),
  PERCENT_RANK() OVER(ORDER BY salary),
  CUME_DIST() OVER(ORDER BY salary),
  NTILE(3) OVER(ORDER BY salary),
  ROW_NUMBER() OVER (ORDER BY salary),
  SUM(1) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Результат
id salary dense_rank rank      percent_rank         cume_dist ntile row_number sum
-- ------ ---------- ---- ----------------- ----------------- ----- ---------- ---
3    8.00          1    1 0.000000000000000 0.200000000000000     1          1   1
4    9.00          2    2 0.250000000000000 0.400000000000000     1          2   2
1   10.00          3    3 0.500000000000000 0.800000000000000     2          3   4
5   10.00          3    3 0.500000000000000 0.800000000000000     2          4   4
2   12.00          4    5 1.000000000000000 1.000000000000000     3          5   5

DENSE_RANK()

Доступно в

DSQL

Синтаксис
DENSE_RANK() OVER {<window_specification> | window_name}
Тип возвращаемого результата

BIGINT

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

Example 7. Использование DENSE_RANK
SELECT
  id,
  salary,
  DENSE_RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Результат
id salary dense_rank
-- ------ ----------
3    8.00          1
4    9.00          2
1   10.00          3
5   10.00          3
2   12.00          4

RANK()

Доступно в

DSQL

Синтаксис
RANK() OVER {<window_specification> | window_name}
Тип возвращаемого результата

BIGINT

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

Example 8. Использование RANK
SELECT
  id,
  salary,
  RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Результат
id salary rank
-- ------ ------
3    8.00      1
4    9.00      2
1   10.00      3
5   10.00      3
2   12.00      5

PERCENT_RANK()

Доступно в

DSQL

Синтаксис
PERCENT_RANK() OVER {<window_specification> | window_name}
Тип возвращаемого результата

DOUBLE PRECISION

Возвращает относительный ранг текущей строки в группе строк. Функция PERCENT_RANK используется для вычисления относительного положения значения в секции или результирующем наборе запроса. Диапазон значений, возвращаемый функцией PERCENT_RANK, больше 0 и меньше или равен 1. В первой строке любого набора PERCENT_RANK равна 0. Значения NULL по умолчанию включаются и рассматриваются как наименьшие возможные значения.

Note

Функция PERNCENT RANK вычисляется как (RANK-1)/(total_rows - 1), где total_rows общее количество строк в секции.
Example 9. Использование PERNCENT RANK
SELECT
  id,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Результат
id salary percent_rank
-- ------ ------------
3    8.00          0.0
4    9.00         0.25
1   10.00          0.5
5   10.00          0.5
2   12.00          1.0
См. также:

SELECT, PARTITION BY, ORDER BY, RANK, CUME_DIST.

CUME_DIST()

Доступно в

DSQL

Синтаксис
CUME_DIST() OVER {<window_specification> | window_name}
Тип возвращаемого результата

DOUBLE PRECISION

Функция CUME_DIST рассчитывает кумулятивное распределение значения в наборе данных. Возвращаемое значение находится в диапазоне от 0 до 1. Функция CUME_DIST рассчитывается как (число строк, предшествующих или равных текущей) / (общее число строк). Для равных значений всегда вычисляется одно и то же значение накопительного распределения. Значения NULL по умолчанию включаются и рассматриваются как наименьшие возможные значения.

Example 10. Использование CUME_DIST
SELECT
  id,
  salary,
  CUME_DIST() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Результат
id salary    cume_dist
-- ------ ------------
3    8.00          0.2
4    9.00          0.4
1   10.00          0.8
5   10.00          0.8
2   12.00          1.0

NTILE()

Доступно в

DSQL

Синтаксис
NTILE(<expr>) OVER {<window_specification> | window_name}
Table 2. Параметры функции NTILE
Параметр Описание

expr

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

BIGINT

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

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

Example 11. Использование NTILE
SELECT
  id,
  salary,
  NTILE(3) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Результат
id salary        ntile
-- ------ ------------
3    8.00            1
4    9.00            1
1   10.00            2
5   10.00            2
2   12.00            3
См. также:

SELECT, PARTITION BY, ORDER BY.

ROW_NUMBER()

Доступно в

DSQL

Синтаксис
ROW_NUMBER() OVER {<window_specification> | window_name}
Тип возвращаемого результата

BIGINT

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

Example 12. Использование ROW_NUMBER
SELECT
  id,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Результат
id salary row_number
-- ------ ----------
3    8.00          1
4    9.00          2
1   10.00          3
5   10.00          4
2   12.00          5

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

Навигационные функции получают простые (не агрегированные) значения выражения из другой строки запроса в той же секции.

Important

Функции FIRST_VALUE, LAST_VALUE и NTH_VALUE оперируют на рамке окна (кадрах окна). По умолчанию, если задано предложение ORDER BY, то рамка состоит из всех строк, от начала разбиения до текущей строки, плюс любые следующие строки, которые равны текущей строке в соответствии с предложением ORDER BY, т.е.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Из-за этого результаты функций NTH_VALUE и в особенности LAST_VALUE могут показаться странными. Для устранения этого "недостатка" вы можете задать другую рамку окна, например:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Example 13. Навигационные функции
SELECT
  id,
  salary,
  FIRST_VALUE(salary) OVER (ORDER BY salary),
  LAST_VALUE(salary) OVER (ORDER BY salary),
  NTH_VALUE(salary, 2) OVER (ORDER BY salary),
  LAG(salary) OVER (ORDER BY salary),
  LEAD(salary) OVER (ORDER BY salary)
FROM employee
ORDER BY salary;
Результат
id salary first_value last_value nth_value    lag   lead
-- ------ ----------- ---------- --------- ------ ------
3    8.00        8.00       8.00    <null> <null>   9.00
4    9.00        8.00       9.00      9.00   8.00  10.00
1   10.00        8.00      10.00      9.00   9.00  10.00
5   10.00        8.00      10.00      9.00  10.00  12.00
2   12.00        8.00      12.00      9.00  10.00 <null>

Вариант с изменённой рамкой окна для функций LAST_VALUE и NTH_VALUE

SELECT
  id,
  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;
Результат
id salary first_value last_value nth_value    lag   lead
-- ------ ----------- ---------- --------- ------ ------
3    8.00        8.00      12.00      9.00 <null>   9.00
4    9.00        8.00      12.00      9.00   8.00  10.00
1   10.00        8.00      12.00      9.00   9.00  10.00
5   10.00        8.00      12.00      9.00  10.00  12.00
2   12.00        8.00      12.00      9.00  10.00 <null>

FIRST_VALUE()

Доступно в

DSQL

Синтаксис
FIRST_VALUE(<expr>) OVER {<window_specification> | window_name}
Table 3. Параметры функции FIRST_VALUE
Параметр Описание

expr

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

тот же что и аргумент функции expr

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

LAG()

Доступно в

DSQL

Синтаксис
LAG(<expr> [, <offset> [, <default>]])
  OVER {<window_specification> | window_name}
Table 4. Параметры функции LAG
Параметр Описание

expr

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

offset

Количество строк до строки перед текущей строкой, из которой необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1. offset может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целая положительная величина, или другим типом, который может быть неявно преобразован в BIGINT. offset не может быть отрицательным значением или аналитической функцией.

default

Значение по умолчанию, которое возвращается, в случае если смещение (offset) указывает за пределы секции. По умолчанию равно NULL
Тип возвращаемого результата

тот же что и аргумент функции expr

Функция LAG обеспечивает доступ к строке с заданным физическим смещением (offset) перед началом текущей строки.

Если смещение (offset) указывает за пределы секции, то будет возвращено значение default, которое по умолчанию равно NULL.

Примеры:
Example 14. Использование функции LAG

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

SELECT
  bydate,
  cost,
  cost - LAG(cost) OVER(ORDER BY bydate) AS change,
  100 * (cost - LAG(cost) OVER(ORDER BY bydate)) /
    LAG(cost) OVER(ORDER BY bydate) AS percent_change
FROM rate
WHERE bydate BETWEEN DATEADD(-4 DAY TO current_date)
  AND current_date
ORDER BY bydate
Результат
bydate        cost    change   percent_change
----------   -----   -------   --------------
27.10.2014   31.00    <null>           <null>
28.10.2014   31.53      0.53           1.7096
29.10.2014   31.40     -0.13          -0.4123
30.10.2014   31.67      0.27           0.8598
31.10.2014   32.00      0.33           1.0419
См. также:

SELECT, PARTITION BY, ORDER BY, LEAD().

LAST_VALUE()

Доступно в

DSQL

Синтаксис
LAST_VALUE(<expr>) OVER {<window_specification> | window_name}
Table 5. Параметры функции LAST_VALUE
Параметр Описание

expr

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

тот же что и аргумент функции expr

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

LEAD()

Доступно в

DSQL

Синтаксис
LEAD(<expr> [, <offset> [, <default>]])
  OVER {<window_specification> | window_name}
Table 6. Параметры функции LEAD
Параметр Описание

expr

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

offset

Количество строк после текущей строки до строки, из которой необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1. offset может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целая положительная величина, или другим типом, который может быть неявно преобразован в BIGINT. offset не может быть отрицательным значением или аналитической функцией.

default

Значение по умолчанию, которое возвращается, в случае если смещение (offset) указывает за пределы секции. По умолчанию равно NULL.
Тип возвращаемого результата

тот же что и аргумент функции expr

Функция LEAD обеспечивает доступ к строке на заданном физическом смещении (offset) после текущей строки.

Если смещение (offset) указывает за пределы секции, то будет возвращено значение default, которое по умолчанию равно NULL.

См. также:

SELECT, PARTITION BY, ORDER BY, LAG().

NTH_VALUE()

Доступно в

DSQL

Синтаксис
NTH_VALUE(<expr> [, <offset>]) [FROM FIRST | FROM LAST]
  OVER {<window_specification> | window_name}
Table 7. Параметры функции NTH_VALUE
Параметр Описание

expr

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

offset

Номер записи, начиная с первой (опция FROM FIRST) или последней (опция FROM LAST) записи.
Тип возвращаемого результата

тот же что и аргумент функции expr

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

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

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

Note

При использовании агрегатных функции в качестве аргументов оконных функций, все столбцы, не используемые в агрегатных функциях должны быть указаны в предложении GROUP BY.
Example 15. Использование агрегатной функции в качестве аргумента оконной
SELECT
    code_employee_group,
    AVG(salary) AS avg_salary,
    RANK() OVER(ORDER BY AVG(salary)) AS salary_rank
FROM employee
GROUP BY code_employee_group