Определение рамок оконной функции

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

Что такое оконные функции

Оконные функции (Window Functions) — это инструмент SQL, позволяющий выполнять вычисления над набором строк, связанных с текущей строкой, без группировки результатов в одну строку. В отличие от агрегатных функций с GROUP BY, оконные функции сохраняют все строки исходной таблицы, добавляя к ним результат вычислений. Ключевая концепция — "окно" (window) — набор строк, над которыми производится вычисление. Каждое окно определяется с помощью:

PARTITION BY — разделение на группы (аналогично GROUP BY, но без "схлопывания" строк)
ORDER BY — порядок строк внутри окна
Рамка (фрейм) — подмножество строк внутри окна, над которыми выполняется вычисление

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

Синтаксис оконной функции

Базовый синтаксис оконной функции:

sql

function_name([arguments]) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST}]]
    [frame_clause]
)

Где frame_clause определяет рамку окна и имеет следующий формат:

sql

{ ROWS | RANGE | GROUPS } BETWEEN frame_start AND frame_end
    [EXCLUDE { CURRENT ROW | GROUP | TIES | NO OTHERS }]

Если рамка не указана, используется значение по умолчанию, которое зависит от наличия ORDER BY в определении окна.

Отличия PARTITION BY и GROUP BY

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

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

Пример: сравним два подхода на данных о платежах из базы dvd-rental.

sql

-- group by: получаем общую сумму платежей по каждому клиенту (одна строка на клиента)
select customer_id, sum(amount) 
from payment
where customer_id in (1, 2, 3)
group by customer_id;

text

customer_id|sum   |
-----------+------+
          1|118.68|
          2|128.73|
          3|135.74|

sql

-- partition by: сохраняем все платежи, но добавляем сумму по клиенту к каждой строке
select payment_id, customer_id, amount,
    sum(amount) over (partition by customer_id)
from payment
where customer_id in (1, 2, 3);

text

payment_id|customer_id|amount|sum   |
----------+-----------+------+------+
         1|          1|  2.99|118.68|
         2|          1|  0.99|118.68|
         3|          1|  5.99|118.68|
        33|          2|  4.99|128.73|
        34|          2|  2.99|128.73|
        35|          2|  2.99|128.73|
        60|          3|  1.99|135.74|
        61|          3|  2.99|135.74|
        62|          3|  8.99|135.74|

В первом запросе мы получаем 3 строки (по одной на каждого клиента). Во втором запросе мы видим все исходные строки платежей с добавленной колонкой, показывающей общую сумму платежей каждого клиента.

Как работают рамки окна по умолчанию

Если в определении окна указан ORDER BY, то рамка по умолчанию:

sql

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Если ORDER BY не указан, то рамка по умолчанию включает все строки раздела:

sql

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Это важно понимать, потому что рамка влияет на агрегатные оконные функции (SUM, AVG, MIN, MAX и др.), а также FIRST_VALUE/LAST_VALUE/NTH_VALUE. Функции вроде ROW_NUMBER, RANK, NTILE, LAG и LEAD используют только PARTITION BY и ORDER BY и не зависят от frame clause.

Пример с нарастающим итогом по дате платежа для клиента:

sql

select payment_id, payment_date, amount,
    -- по умолчанию: range between unbounded preceding and current row
    sum(amount) over (partition by customer_id order by payment_date),
    -- явное указание рамки дает тот же результат
    sum(amount) over (partition by customer_id order by payment_date
        range between unbounded preceding and current row)
from payment
where customer_id = 1;

text

payment_id|payment_date           |amount|sum   |sum   |
----------+-----------------------+------+------+------+
         1|2005-05-25 11:30:37.000|  2.99|  2.99|  2.99|
         2|2005-05-28 10:35:23.000|  0.99|  3.98|  3.98|
         3|2005-06-15 00:54:12.000|  5.99|  9.97|  9.97|
         4|2005-06-15 18:02:53.000|  0.99| 10.96| 10.96|
         5|2005-06-15 21:08:46.000|  9.99| 20.95| 20.95|
         6|2005-06-16 15:18:57.000|  4.99| 25.94| 25.94|
         7|2005-06-18 08:41:48.000|  4.99| 30.93| 30.93|
         8|2005-06-18 13:33:59.000|  0.99| 31.92| 31.92|
         9|2005-06-21 06:24:45.000|  3.99| 35.91| 35.91|
        10|2005-07-08 03:17:05.000|  5.99| 41.90| 41.90|

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

Операторы ROWS, RANGE, GROUPS

ROWS — определяет рамку в терминах физических строк относительно текущей строки. Наиболее интуитивно понятный вариант.

RANGE — определяет рамку в терминах значений ORDER BY. Включает все строки, чье значение ORDER BY попадает в указанный диапазон.

GROUPS (доступен с PostgreSQL 11) — определяет рамку в терминах групп одинаковых значений ORDER BY. Каждая группа считается одной единицей.

ВАЖНО: Режимы RANGE и GROUPS определяют фрейм на основе значений столбцов из ORDER BY (диапазоны значений или группы). Поэтому они логически требуют наличия ORDER BY в определении окна — без него система не может определить, на каком основании строить эти диапазоны или группы. Для режима ROWS также логически важен порядок строк, так как концепция режима построена на относительном положении строк.

Сравним поведение на примере скользящего среднего за 3 строки:

sql

-- создадим временные данные для наглядности
with sample_data as (
    select column1 as id, column2 as value
    from (
        values (1, 10), (2, 20), (3, 30), (4, 40), (5, 50))
)
select id, value,
    -- rows: 3 физические строки (предыдущая, текущая, следующая)
    avg(value) over (order by id rows between 1 preceding and 1 following),
    -- range: все строки со значением id в диапазоне ±1 от текущего
    avg(value) over (order by id range between 1 preceding and 1 following)
from sample_data;

text

id|value|avg                |avg                |
--+-----+-------------------+-------------------+
 1|   10|                15 |                15 |
 2|   20|                20 |                20 |
 3|   30|                30 |                30 |
 4|   40|                40 |                40 |
 5|   50|                45 |                45 |

В базе dvd-rental рассмотрим среднюю сумму платежа в скользящем окне:

sql

-- для клиента 1: скользящее среднее по 3 платежам (предыдущий, текущий, следующий)
select payment_id, payment_date, amount,
    avg(amount) over (order by payment_date rows between 1 preceding and 1 following)
from payment
where customer_id = 1;

text

payment_id|payment_date           |amount|avg               |
----------+-----------------------+------+------------------+
         1|2005-05-25 11:30:37.000|  2.99|1.9900000000000000|
         2|2005-05-28 10:35:23.000|  0.99|3.3233333333333333|
         3|2005-06-15 00:54:12.000|  5.99|2.6566666666666667|
         4|2005-06-15 18:02:53.000|  0.99|5.6566666666666667|
         5|2005-06-15 21:08:46.000|  9.99|5.3233333333333333|

Пример отработки GROUPS с получением массива со значениями платежей по каждому пользователю за 3 группы: предыдущий день, текущий день, следующий день:

sql

select customer_id, payment_date::date, amount, 
	array_agg(amount) over (partition by customer_id order by payment_date::date groups between 1 preceding and 1 following)
from payment 
limit 10

text

customer_id|payment_date|amount|array_agg                      |
-----------+------------+------+-------------------------------+
          1|  2005-05-25|  2.99|{2.99,0.99}                    |
          1|  2005-05-28|  0.99|{2.99,0.99,9.99,5.99,0.99}     |
          1|  2005-06-15|  9.99|{0.99,9.99,5.99,0.99,4.99}     |
          1|  2005-06-15|  5.99|{0.99,9.99,5.99,0.99,4.99}     |
          1|  2005-06-15|  0.99|{0.99,9.99,5.99,0.99,4.99}     |
          1|  2005-06-16|  4.99|{9.99,5.99,0.99,4.99,4.99,0.99}|
          1|  2005-06-18|  4.99|{4.99,4.99,0.99,3.99}          |
          1|  2005-06-18|  0.99|{4.99,4.99,0.99,3.99}          |
          1|  2005-06-21|  3.99|{4.99,0.99,3.99,5.99,5.99}     |
          1|  2005-07-08|  5.99|{3.99,5.99,5.99,4.99,4.99}     |

Указание начала и конца рамок

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

UNBOUNDED PRECEDING — начало раздела (первая строка)
n PRECEDING — n строк/единиц перед текущей строкой (для ROWS/GROUPS) или значение меньше текущего на n (для RANGE)
CURRENT ROW — текущая строка
n FOLLOWING — n строк/единиц после текущей строки
UNBOUNDED FOLLOWING — конец раздела (последняя строка)

Примеры различных рамок для анализа платежей:

sql

-- различные варианты рамок для одного клиента
select payment_id, payment_date, amount,
    -- нарастающий итог от начала раздела до текущей строки,
    -- если нужно накапливать каждый платеж в отдельности
    sum(amount) over (order by payment_date rows between unbounded preceding and current row),   
    -- текущая строка и две следующие
    sum(amount) over (order by payment_date rows between current row and 2 following),  
    -- три строки вокруг текущей (предыдущая, текущая, следующая)
    avg(amount) over (order by payment_date rows between 1 preceding and 1 following),  
    -- от текущей строки до конца раздела (убывающий итог)
    sum(amount) over (order by payment_date rows between current row and unbounded following)
from payment
where customer_id = 1;

text

payment_id|payment_date           |amount|sum   |sum  |avg               |sum   |
----------+-----------------------+------+------+-----+------------------+------+
         1|2005-05-25 11:30:37.000|  2.99|  2.99| 9.97|1.9900000000000000|118.68|
         2|2005-05-28 10:35:23.000|  0.99|  3.98| 7.97|3.3233333333333333|115.69|
         3|2005-06-15 00:54:12.000|  5.99|  9.97|16.97|2.6566666666666667|114.70|
         4|2005-06-15 18:02:53.000|  0.99| 10.96|15.97|5.6566666666666667|108.71|
         5|2005-06-15 21:08:46.000|  9.99| 20.95|19.97|5.3233333333333333|107.72|
         6|2005-06-16 15:18:57.000|  4.99| 25.94|10.97|6.6566666666666667| 97.73|
         7|2005-06-18 08:41:48.000|  4.99| 30.93| 9.97|3.6566666666666667| 92.74|
         8|2005-06-18 13:33:59.000|  0.99| 31.92|10.97|3.3233333333333333| 87.75|
         9|2005-06-21 06:24:45.000|  3.99| 35.91|15.97|3.6566666666666667| 86.76|
        10|2005-07-08 03:17:05.000|  5.99| 41.90|16.97|5.3233333333333333| 82.77|

Особенность RANGE: при ORDER BY по дате, RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW включит все строки за последние сутки.

Пример:

sql

select customer_id, payment_date::date, amount, 
	array_agg(amount) over (partition by customer_id order by payment_date::date range between '2 day' preceding and current row)
from payment

text

customer_id|payment_date|amount|array_agg            |
-----------+------------+------+---------------------+
          1|  2005-05-25|  2.99|{2.99}               |
          1|  2005-05-28|  0.99|{0.99}               |
          1|  2005-06-15|  9.99|{9.99,5.99,0.99}     |
          1|  2005-06-15|  5.99|{9.99,5.99,0.99}     |
          1|  2005-06-15|  0.99|{9.99,5.99,0.99}     |
          1|  2005-06-16|  4.99|{9.99,5.99,0.99,4.99}|
          1|  2005-06-18|  4.99|{4.99,4.99,0.99}     |
          1|  2005-06-18|  0.99|{4.99,4.99,0.99}     |
          1|  2005-06-21|  3.99|{3.99}               |
          1|  2005-07-08|  5.99|{5.99,5.99}          |

Исключение рамок через EXCLUDE

Предложение EXCLUDE позволяет исключать определенные строки из оконного фрейма после его определения. Это полезно, когда нужно вычислить агрегат или другую оконную функцию по всем строкам фрейма, за исключением некоторых специфических строк. Доступные опции:

EXCLUDE CURRENT ROW — исключает только текущую строку из фрейма.
EXCLUDE GROUP — исключает текущую строку и все строки, равные ей по значению ORDER BY (все "ничьи").
EXCLUDE TIES — исключает строки, равные текущей по значению ORDER BY, но оставляет саму текущую строку.
EXCLUDE NO OTHERS — ничего не исключает (значение по умолчанию).

Важное ограничение: в PostgreSQL до версии 16 EXCLUDE работает только с фреймами, которые включают CURRENT ROW. Это означает, что предложение EXCLUDE можно использовать только тогда, когда границы фрейма определены так, что текущая строка входит в фрейм.

Например, следующие фреймы совместимы с EXCLUDE в версиях до 16:

sql

-- Эти фреймы включают CURRENT ROW, поэтому EXCLUDE работает:
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
RANGE BETWEEN 10 PRECEDING AND CURRENT ROW

А эти фреймы не включают CURRENT ROW, поэтому EXCLUDE в старых версиях нельзя использовать:

sql

-- Эти фреймы НЕ включают CURRENT ROW, поэтому EXCLUDE не работает (до PG 16):
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING

Начиная с PostgreSQL 16 это ограничение снято: EXCLUDE можно использовать с любыми фреймами. В документации PostgreSQL 16+ указано, что EXCLUDE применяется к фрейму после его определения, независимо от того, включает ли он текущую строку.

Пример использования EXCLUDE для анализа платежей клиента, исключая текущий платеж:

sql

-- для каждого платежа показываем разницу с минимальным платежом того же клиента,
-- исключая текущий платеж из фрейма (если это возможно)
select payment_id, customer_id, amount,
    min(amount) over (partition by customer_id order by payment_date
        rows between unbounded preceding and unbounded following exclude current row),
    amount - min(amount) over (partition by customer_id order by payment_date
        rows between unbounded preceding and unbounded following exclude current row)
from payment
where customer_id in (1, 2);

text

payment_id|customer_id|amount|min |?column?|
----------+-----------+------+----+--------+
         1|          1|  2.99|0.99|    2.00|
         2|          1|  0.99|0.99|    0.00|
         3|          1|  5.99|0.99|    5.00|
         4|          1|  0.99|0.99|    0.00|
         5|          1|  9.99|0.99|    9.00|

В этом примере для каждого платежа мы находим минимальную сумму среди всех других платежей того же клиента (исключая текущий платеж). Это демонстрирует типичный случай использования EXCLUDE CURRENT ROW.

Примечание: если вы используете PostgreSQL версии ниже 16, убедитесь, что ваш фрейм включает CURRENT ROW при использовании EXCLUDE. В противном случае запрос завершится ошибкой. В PostgreSQL 16+ такое ограничение отсутствует.

Заключение

Определение рамок оконной функции — это мощный механизм, позволяющий точно контролировать, какие строки участвуют в вычислениях. Ключевые моменты:

Рамки определяют подмножество строк внутри окна для каждого вычисления
ROWS, RANGE и GROUPS предлагают разные подходы к определению этого подмножества
Предложение EXCLUDE позволяет исключать определенные строки из рамки
Понимание рамок по умолчанию критически важно для корректного использования оконных функций

Для эффективной работы с оконными функциями в PostgreSQL рекомендуется:

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

Определение рамок оконной функции ​

Что такое оконные функции ​

Синтаксис оконной функции ​

Отличия PARTITION BY и GROUP BY ​

Как работают рамки окна по умолчанию ​

Операторы ROWS, RANGE, GROUPS ​

Указание начала и конца рамок ​

Исключение рамок через EXCLUDE ​

Заключение ​

Определение рамок оконной функции

Что такое оконные функции

Синтаксис оконной функции

Отличия PARTITION BY и GROUP BY

Как работают рамки окна по умолчанию

Операторы ROWS, RANGE, GROUPS

Указание начала и конца рамок

Исключение рамок через EXCLUDE

Заключение