Руководство По Языку SQL СУБД Firebird 5.0 | Рамка Окна

Окна строк

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

Example 1. Использование окон диапазона

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 1. Использование именованных окон

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 1. Использование 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

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-rank],[fblangref-windowfuncs-row-number].

`RANK()`

Доступно в

DSQL

Синтаксис

RANK() OVER {<window_specification> | window_name}

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

BIGINT

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

Example 1. Использование 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

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-dense-rank],[fblangref-windowfuncs-row-number].

`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 1. Использование 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 1. Использование 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

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-rank],[fblangref-windowfuncs-percent-rank].

`NTILE()`

Доступно в

DSQL

Синтаксис

NTILE(<expr>) OVER {<window_specification> | window_name}

Table 1. Параметры функции NTILE
Параметр	Описание
expr	Выражение целочисленного типа.Указывает количество групп, на которые необходимо разделить каждую секцию.

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

BIGINT

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

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

Example 1. Использование 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 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

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-rank],[fblangref-windowfuncs-dense-rank].

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

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

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

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

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-last-value],[fblangref-windowfuncs-nth_value].

`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

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-lead].

`LAST_VALUE()`

Доступно в

DSQL

Синтаксис

LAST_VALUE(<expr>) OVER {<window_specification> | window_name}

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

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

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

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

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-first-value],[fblangref-windowfuncs-nth_value].

`LEAD()`

Доступно в

DSQL

Синтаксис

LEAD(<expr> [, <offset> [, <default>]])
  OVER {<window_specification> | window_name}

Table 1. Параметры функции LEAD
Параметр	Описание
expr	Выражение.Может содержать столбец таблицы, константу, переменную, выражение, неагрегатную функцию или UDF.Агрегатные функции в качестве выражения не допускаются.
offset	Количество строк после текущей строки до строки, из которой необходимо получить значение.Если значение аргумента не указано, то по умолчанию принимается 1.offset может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целаяположительная величина, или другим типом, который может быть неявно преобразован в `BIGINT`.offset не может быть отрицательным значением или аналитической функцией.
default	Значение по умолчанию, которое возвращается, в случае если смещение (offset) указывает за пределы секции.По умолчанию равно `NULL`.

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

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

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

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

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-lag].

`NTH_VALUE()`

Доступно в

DSQL

Синтаксис

NTH_VALUE(<expr> [, <offset>]) [FROM FIRST | FROM LAST]
  OVER {<window_specification> | window_name}

Table 1. Параметры функции `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.

См. также:

SELECT,PARTITION BY,ORDER BY,[fblangref-windowfuncs-first-value],[fblangref-windowfuncs-last-value].

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

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

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

Example 1. Использование агрегатной функции в качестве аргумента оконной

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

Окна Строк

Окна строк

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

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

DENSE_RANK()

RANK()

PERCENT_RANK()

CUME_DIST()

NTILE()

ROW_NUMBER()

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

FIRST_VALUE()

LAG()

Примеры:

LAST_VALUE()

LEAD()

NTH_VALUE()

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

`DENSE_RANK()`

`RANK()`

`PERCENT_RANK()`

`CUME_DIST()`

`NTILE()`

`ROW_NUMBER()`

`FIRST_VALUE()`

`LAG()`

`LAST_VALUE()`

`LEAD()`

`NTH_VALUE()`