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

Рамка окна

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

Рамка окна состоит из трёх частей: единица (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 1. Использование окон диапазона
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.Это помогает увидеть диапазоны окна и проверить, корректно ли установлены параметры.