FirebirdSQL logo

ROW_NUMBER()

Доступно в

DSQL

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

BIGINT

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

Example 1. Использование 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 1. Навигационные функции
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 1. Параметры функции FIRST_VALUE
Параметр Описание

expr

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

Тип возвращаемого результата

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

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

LAG()

Доступно в

DSQL

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

expr

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

offset

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

default

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

Тип возвращаемого результата

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

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

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

Примеры:
Example 1. Использование функции 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