Оконные функции

Елена Дворядкина

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

Создание таблицы для выполнения тестовых примеров

 
Чтобы выполнить приведенные в статье примеры и посмотреть, как работают оконные функции, создайте предварительно таблицу wnd_func_table:

CREATE TABLE wnd_func_table (group_id Int32, sort_id Int32, value Int32) ENGINE = MergeTree ORDER BY group_id;
INSERT INTO wnd_func_table FORMAT Values
(1,1,10), (1,2,20), (1,3,30), (1,4,40), (1,5,50),
(2,1,1), (2,2,2), (2,3,3), (2,4,4), (2,4,5), (2,4,6), (2,5,7), (2,6,8);
┌─group_id─┬─sort_id─┬─value─┐
│        1 │       1 │    10 │
│        1 │       2 │    20 │
│        1 │       3 │    30 │
│        1 │       4 │    40 │
│        1 │       5 │    50 │
│        2 │       1 │     1 │
│        2 │       2 │     2 │
│        2 │       3 │     3 │
│        2 │       4 │     4 │
│        2 │       4 │     5 │
│        2 │       4 │     6 │
│        2 │       5 │     7 │
│        2 │       6 │     8 │
└──────────┴─────────┴───────┘

Обзор синтаксиса

Синтаксис запроса с использованием оконной функции в общем виде можно записать следующим образом:

SELECT <window_function_name>([<argument_list>]) OVER {() | <window_name> | (<window_spec>)}
FROM <table_name>
WINDOW <window_name> AS (<window_spec>);

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

Чтобы задать окно для функции, после выражения OVER можно указать:

  • () — пустые круглые скобки, если нужно передать все строки из набора данных в оконную функцию;

    Пример

     
    Следующий запрос считывает все строки из таблицы wnd_func_table и добавляет к результату в дополнительные столбцы number и sum номера строк и сумму всех значений столбца value:

    SELECT
    group_id,
    sort_id,
    value,
    row_number() OVER() AS number,
    sum(value) OVER() AS sum
FROM wnd_func_table;
    ┌─group_id─┬─sort_id─┬─value─┬─number─┬─sum─┐
│        1 │       1 │    10 │      1 │ 186 │
│        1 │       2 │    20 │      2 │ 186 │
│        1 │       3 │    30 │      3 │ 186 │
│        1 │       4 │    40 │      4 │ 186 │
│        1 │       5 │    50 │      5 │ 186 │
│        2 │       1 │     1 │      6 │ 186 │
│        2 │       2 │     2 │      7 │ 186 │
│        2 │       3 │     3 │      8 │ 186 │
│        2 │       4 │     4 │      9 │ 186 │
│        2 │       4 │     5 │     10 │ 186 │
│        2 │       4 │     6 │     11 │ 186 │
│        2 │       5 │     7 │     12 │ 186 │
│        2 │       6 │     8 │     13 │ 186 │
└──────────┴─────────┴───────┴────────┴─────┘

  • <window_name> — имя окна, которое указывается при описании окна с помощью выражение WINDOW в запросе (то есть выражение WINDOW позволяет описать окно один раз и использовать его для нескольких оконных функций в запросе SELECT);

  • <window_spec> — описание окна в круглых скобках в виде набора опциональных выражений (см. синтаксис ниже).

РЕКОМЕНДАЦИЯ
Если данные запроса нужно отфильтровать по значениям оконных функций, вычисление оконных функций необходимо выполнять в рамках CTE или подзапроса.

Описание окна

Описание окна (<window_spec> в приведенном выше синтаксисе запроса) имеет следующий синтаксис:

[<window_name>]
[PARTITION BY <partition_column> [, ...]]
[ORDER BY <order_column> [ASC|DESC] [, ...]]
[{RANGE | ROWS} {<frame_start> | BETWEEN <frame_start> AND <frame_end>}]

где:

  • PARTITION BY — столбец, по значениям которого строки запроса разбиваются на окна. Результат выполнения оконной функции для определенной строки будет зависеть от строк окна, содержащего эту строку. Если выражение PARTITION BY не указано, функция вычисляется для одного окна, которое содержит все строки.

    Пример

     
    В следующем запросе с помощью выражения PARTITION BY устанавливается столбец, по которому будет производиться группировка данных, то есть разделение набора строк на окна:

    SELECT
    group_id,
    sort_id,
    value,
    row_number() OVER (w) AS number,
    sum(value) OVER (w) AS sum
FROM wnd_func_table
WINDOW w AS (PARTITION BY group_id);

    Функции row_number и sum вычислились по данным каждого окна отдельно (в этом примере окно — набор строк с одинаковыми значниями в столбце group_id):

    ┌─group_id─┬─sort_id─┬─value─┬─number─┬─sum─┐
│        1 │       1 │    10 │      1 │ 150 │
│        1 │       2 │    20 │      2 │ 150 │
│        1 │       3 │    30 │      3 │ 150 │
│        1 │       4 │    40 │      4 │ 150 │
│        1 │       5 │    50 │      5 │ 150 │
│        2 │       1 │     1 │      1 │  36 │
│        2 │       2 │     2 │      2 │  36 │
│        2 │       3 │     3 │      3 │  36 │
│        2 │       4 │     4 │      4 │  36 │
│        2 │       4 │     5 │      5 │  36 │
│        2 │       4 │     6 │      6 │  36 │
│        2 │       5 │     7 │      7 │  36 │
│        2 │       6 │     8 │      8 │  36 │
└──────────┴─────────┴───────┴────────┴─────┘

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

    Если в описании окна указан ORDER BY, функция вычисляется для строк от начала окна до текущей строки (включая все строки с одинаковыми значениями в поле ORDER BY) в соотвтетсвии с установленным порядком сортировки. То есть для каждой строки выбирается фрейм (frame) — набор строк, по которому будет вычисляться функция (например, агрегатные функции, которые используются как оконные, вычисляются на основании фрейма, а не всего окна). Указать другое правило для определения фрейма можно с помощью выражения RANGE или ROWS.

    Пример

     
    В следующем запросе устанавливается сортировка по столбцу sort_id:

    SELECT
    group_id,
    sort_id,
    value,
    row_number() OVER (w) AS number,
    groupArray(value) OVER (w) AS frame_values,
    sum(value) OVER (w) AS sum
FROM wnd_func_table
WINDOW w AS (PARTITION BY group_id ORDER BY sort_id ASC);

    В результирующую таблицу добавляются столбцы с результатами вычисления оконных функций:

    • number — порядковый номер строки в окне;

    • frame_values — значения столбца value в строках, которые входят во фрейм для текущей строки;

    • sum — сумма значений столбца value в тех строках, которые входят во фрейм для текущей строки (сумма каждого значения value со всеми предыдущими).

    ┌─group_id─┬─sort_id─┬─value─┬─number─┬─frame_values──────┬─sum─┐
│        1 │       1 │    10 │      1 │ [10]              │  10 │
│        1 │       2 │    20 │      2 │ [10,20]           │  30 │
│        1 │       3 │    30 │      3 │ [10,20,30]        │  60 │
│        1 │       4 │    40 │      4 │ [10,20,30,40]     │ 100 │
│        1 │       5 │    50 │      5 │ [10,20,30,40,50]  │ 150 │
│        2 │       1 │     1 │      1 │ [1]               │   1 │
│        2 │       2 │     2 │      2 │ [1,2]             │   3 │
│        2 │       3 │     3 │      3 │ [1,2,3]           │   6 │
│        2 │       4 │     4 │      4 │ [1,2,3,4,5,6]     │  21 │
│        2 │       4 │     5 │      5 │ [1,2,3,4,5,6]     │  21 │
│        2 │       4 │     6 │      6 │ [1,2,3,4,5,6]     │  21 │
│        2 │       5 │     7 │      7 │ [1,2,3,4,5,6,7]   │  28 │
│        2 │       6 │     8 │      8 │ [1,2,3,4,5,6,7,8] │  36 │
└──────────┴─────────┴───────┴────────┴───────────────────┴─────┘

    Порядок сортировки строк в окне не связан с порядком сортировки на уровне запроса.

ROWS

Выражение ROWS позволяет определить фрейм, указав фиксированное количество строк, предшествующих или следующих за текущей строкой, одним из следующих способов:

ROWS <frame_start>
ROWS BETWEEN <frame_start> AND <frame_end>

Чтобы указать границы фрейма <frame_start> и <frame_end>, можно использовать следующие выражения.

CURRENT ROW

Фрейм начинается или заканчивается на текущей строке

UNBOUNDED PRECEDING

Фрейм начинается с первой строки окна

UNBOUNDED FOLLOWING

Фрейм заканчивается последней строкой окна

<N> PRECEDING

Фрейм включает <N> строк перед текущей строкой

<M> FOLLOWING

Фрейм включает <M> строк после текущей строки

Если <frame_end> не указан, конец фрейма по умолчанию CURRENT ROW.

Пример — ROWS <frame_start>

 

Фрейм включает две предыдущие и текущую запись:

SELECT
    group_id,
    sort_id,
    value,
    groupArray(value) OVER (PARTITION BY group_id ORDER BY sort_id ASC
            ROWS 2 PRECEDING) AS frame_values
FROM wnd_func_table;

В столбце frame_values показаны значения столбца value всех строк, попадающих во фрейм, который формируется для каждой строки отдельно:

┌─group_id─┬─sort_id─┬─value─┬─frame_values─┐
│        1 │       1 │    10 │ [10]         │
│        1 │       2 │    20 │ [10,20]      │
│        1 │       3 │    30 │ [10,20,30]   │
│        1 │       4 │    40 │ [20,30,40]   │
│        1 │       5 │    50 │ [30,40,50]   │
│        2 │       1 │     1 │ [1]          │
│        2 │       2 │     2 │ [1,2]        │
│        2 │       3 │     3 │ [1,2,3]      │
│        2 │       4 │     4 │ [2,3,4]      │
│        2 │       4 │     5 │ [3,4,5]      │
│        2 │       4 │     6 │ [4,5,6]      │
│        2 │       5 │     7 │ [5,6,7]      │
│        2 │       6 │     8 │ [6,7,8]      │
└──────────┴─────────┴───────┴──────────────┘
Пример — ROWS BETWEEN <frame_start> AND <frame_end>

 
Фрейм включает текущую строку и все последующие строки до конца окна:

SELECT
    group_id,
    sort_id,
    value,
    groupArray(value) OVER (PARTITION BY group_id ORDER BY sort_id ASC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS frame_values
FROM wnd_func_table;
┌─group_id─┬─sort_id─┬─value─┬─frame_values──────┐
│        1 │       1 │    10 │ [10,20,30,40,50]  │
│        1 │       2 │    20 │ [20,30,40,50]     │
│        1 │       3 │    30 │ [30,40,50]        │
│        1 │       4 │    40 │ [40,50]           │
│        1 │       5 │    50 │ [50]              │
│        2 │       1 │     1 │ [1,2,3,4,5,6,7,8] │
│        2 │       2 │     2 │ [2,3,4,5,6,7,8]   │
│        2 │       3 │     3 │ [3,4,5,6,7,8]     │
│        2 │       4 │     4 │ [4,5,6,7,8]       │
│        2 │       4 │     5 │ [5,6,7,8]         │
│        2 │       4 │     6 │ [6,7,8]           │
│        2 │       5 │     7 │ [7,8]             │
│        2 │       6 │     8 │ [8]               │
└──────────┴─────────┴───────┴───────────────────┘

RANGE

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

Есть два способа использовать выражение RANGE:

RANGE <frame_start>
RANGE BETWEEN <frame_start> AND <frame_end>

Границы фрейма <frame_start> и <frame_end> обозначаются с помощью следующих выражений.

CURRENT ROW

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

UNBOUNDED PRECEDING

Фрейм начинается с первой строки окна

UNBOUNDED FOLLOWING

Фрейм заканчивается последней строкой окна

<offset> PRECEDING

Фрейм включает строки со значениями в столбце ORDER BY, которые меньше значения столбца ORDER BY в текущей строке максимум на значение <offset>. Выражение <offset> PRECEDING можно использовать, только если в ORDER BY указан один столбец

<offset> FOLLOWING

Фрейм включает строки со значениями в столбце ORDER BY, которые больше значения столбца ORDER BY в текущей строке максимум на значение <offset>. Выражение <offset> FOLLOWING можно использовать, только если в ORDER BY указан один столбец

Если <frame_end> не указан, конец фрейма по умолчанию CURRENT ROW.

Если в запросе указан ORDER BY, но фрейм не описан явно, применяется фрейм по умолчанию: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Пример — CURRENT ROW

 
Фрейм включает только текущую строку:

SELECT
    group_id,
    sort_id,
    value,
    groupArray(value) OVER (PARTITION BY group_id ORDER BY sort_id ASC
        RANGE BETWEEN CURRENT ROW AND CURRENT ROW) AS frame_values
FROM wnd_func_table;

Во втором окне (group_id=2) диапозон строк с одинаковым значением в поле sort_id рассматривается как одна строка:

┌─group_id─┬─sort_id─┬─value─┬─frame_values─┐
│        1 │       1 │    10 │ [10]         │
│        1 │       2 │    20 │ [20]         │
│        1 │       3 │    30 │ [30]         │
│        1 │       4 │    40 │ [40]         │
│        1 │       5 │    50 │ [50]         │
│        2 │       1 │     1 │ [1]          │
│        2 │       2 │     2 │ [2]          │
│        2 │       3 │     3 │ [3]          │
│        2 │       4 │     4 │ [4,5,6]      │
│        2 │       4 │     5 │ [4,5,6]      │
│        2 │       4 │     6 │ [4,5,6]      │
│        2 │       5 │     7 │ [7]          │
│        2 │       6 │     8 │ [8]          │
└──────────┴─────────┴───────┴──────────────┘
Пример — <offset> PRECEDING/FOLLOWING

 

Фрейм для каждой строки включает строки, в которых значение number (поле сортировки ORDER BY) максимум на 10 меньше и на 5 больше, чем значение number в текущей строке:

SELECT
    number,
    groupArray(number) OVER (ORDER BY number ASC
        RANGE BETWEEN 10 PRECEDING AND 5 FOLLOWING) AS frame_values
FROM values('number Int8', 10, 20, 25, 27, 30, 40, 15, 50, 60, 7, 5, 2);

В результирующей таблице столбец frame_values содержит массивы значений number для фрейма каждой строки:

┌─number─┬─frame_values─────┐
│      2 │ [2,5,7]          │
│      5 │ [2,5,7,10]       │
│      7 │ [2,5,7,10]       │
│     10 │ [2,5,7,10,15]    │
│     15 │ [5,7,10,15,20]   │
│     20 │ [10,15,20,25]    │
│     25 │ [15,20,25,27,30] │
│     27 │ [20,25,27,30]    │
│     30 │ [20,25,27,30]    │
│     40 │ [30,40]          │
│     50 │ [40,50]          │
│     60 │ [50,60]          │
└────────┴──────────────────┘

Виды функций

Оконные функции можно разделить на следующие группы:

  • Aгрегатные — агрегатные функции (например, sum, avg, count, min, max) могут быть использованы как оконные.

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

    Функция Возвращаемое значение

    row_number()

    Номер строки внутри окна. Нумерация начинается с 1 и не зависит от того, есть ли в строках повторяющиеся значения в столбце ORDER BY

    rank()

    Ранг строки. Для строк с одинаковыми значениями в столбце ORDER BY возвращается одинаковый ранг, при этом для следующих строк пропускается N-1 значений ранга, где N — число строк в предыдущей группе строк с одинаковыми значениями в столбце, по которому выполняется упорядочивание

    dense_rank()

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

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

    Функция Возвращаемое значение

    lagInFrame(value, [N])

    leadInFrame(value, [N])

    Значение столбца value в строке, которая находится на смещении N перед или после текущей строки внутри фрейма. Значение N по умолчанию равно 1

    first_value(value)

    last_value(value)

    Значение столбца value в первой или последней строке фрейма

    nth_value(value, N)

    Значение столбца value в строке, которая находится на позиции N внутри фрейма
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней