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

Андрей Аксенов

Contents

Обзор оконных выражений
Функции, поддерживаемые оконными выражениями
- Оконные функции общего назначения
Примеры использования

Оконные функции выполняют вычисления по набору строк, связанных с текущей строкой, не уменьшая количество строк в результирующем наборе. В отличие от агрегатных функций, которые возвращают одно значение для каждой группы строк, оконные функции вычисляют отдельное значение для каждой строки на основе строк, входящих в рамку окна этой строки. Чаще всего они применяются в SELECT-запросах для аналитических задач, таких как расчет скользящих средних, накопительных итогов или ранжирования.

Оконные выражения могут использоваться только в списке SELECT:

SELECT <function_name>( [...] ) OVER ( () | <window_specification> | <window_name> )
FROM <table_name>
[ WINDOW <window_name> AS (<window_specification>) ];

Оконное выражение применяет оконную функцию к набору строк, называемому рамкой окна (window frame), которая определяется выражением OVER. Рамка определяет, какие строки участвуют в вычислении.

После выражения OVER можно указать одно из следующих выражений:

() — пустые круглые скобки, чтобы передать все строки из результата запроса в оконную функцию.
<window_specification> — описание окна прямо в запросе.
<window_name> — имя, указанное в описании окна с помощью выражения WINDOW.

ПРИМЕЧАНИЕ

Чтобы отфильтровать строки по значениям оконных функций, сначала вычислите их в CTE или подзапросе.

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

<function_name>( [expression [, ...]] ) OVER ( () | <window_specification> | <window_name> )

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

<function_name>( [expression [, ...]] ) [ FILTER ( WHERE <filter_clause> ) ] OVER ( () | <window_specification> | <window_name> )

Только агрегатные оконные функции могут использовать выражение FILTER. Узнайте больше в разделе Функции, поддерживаемые оконными выражениями.

<window_specification> выглядит следующим образом:

[PARTITION BY <partition_column> [, ...]]
[ORDER BY <order_column> [ASC | DESC] [, ...]]
[ <frame_clause> ]

Описание окна задается следующими параметрами:

PARTITION BY — делит результат запроса на разделы (подмножества строк), к каждому из которых оконная функция применяется отдельно. Если выражение не указано, все строки рассматриваются как один раздел.
ORDER BY — определяет порядок строк внутри каждого раздела. Этот порядок используется для определения последовательности выполнения вычислений. Функции ранжирования (например, RANK() или ROW_NUMBER()) требуют наличия ORDER BY для вычисления значений ранга. Для OLAP-агрегаций ORDER BY необходим для использования рамок окна (<frame_clause>).
<frame_clause> — определяет тип и границы рамки окна, указывая, какие строки из раздела будут включены в вычисления функции. Узнайте больше в разделе Рамка окна: RANGE и ROWS.

ПРИМЕЧАНИЕ

Выражение ORDER BY внутри описания окна работает независимо от верхнеуровневого выражения ORDER BY в запросе.

Рамка окна (window frame) — это подмножество строк внутри раздела окна (window partition), определяемое относительно текущей строки. Если рамка указана, оконная функция обрабатывает только строки в этом скользящем окне, а не весь раздел. Рамка может быть задана на основе значений (RANGE) или на основе количества строк (ROWS):

RANGE — определяет рамку на основе значений сортировки, заданных в выражении ORDER BY. Это означает, что строки с одинаковыми значениями в столбцах ORDER BY занимают одну и ту же позицию в сортировке и включаются в рамку вместе.
ROWS — определяет рамку по числу строк до или после текущей строки.

Чтобы задать тип и границы рамки окна, используйте одну из следующих форм <frame_clause>:

{ RANGE | ROWS } <frame_start>
{ RANGE | ROWS } BETWEEN <frame_start> AND <frame_end>

Значения <frame_start> и <frame_end> определяют границы рамки и могут принимать следующие значения:

UNBOUNDED PRECEDING
<value> PRECEDING
CURRENT ROW
<value> FOLLOWING
UNBOUNDED FOLLOWING

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

Значение Применяется к Описание

Значение	Применяется к	Описание
UNBOUNDED PRECEDING	Начало рамки	Рамка начинается с первой строки раздела
<value> PRECEDING	Начало или конец рамки	Рамка начинается или заканчивается на строках, у которых значение столбца `ORDER BY` отличается от значения текущей строки не более чем на `<value>` в меньшую сторону
CURRENT ROW	Начало или конец рамки	Когда `CURRENT ROW` используется как начало рамки, рамка начинается с первой строки текущей группы родственных строк (все строки с тем же значением в столбце `ORDER BY`, что и у текущей строки). Когда `CURRENT ROW` используется как конец рамки, рамка заканчивается последней строкой текущей группы родственных строк (все строки с тем же значением в столбце `ORDER BY`, что и у текущей строки).
<value> FOLLOWING	Начало или конец рамки	Рамка начинается или заканчивается на строках, у которых значение столбца `ORDER BY` отличается от значения текущей строки не более чем на `<value>` в большую сторону
UNBOUNDED FOLLOWING	Конец рамки	Рамка заканчивается на последней строке раздела

UNBOUNDED PRECEDING

Начало рамки

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

<value> PRECEDING

Начало или конец рамки

Рамка начинается или заканчивается на строках, у которых значение столбца ORDER BY отличается от значения текущей строки не более чем на <value> в меньшую сторону

CURRENT ROW

Начало или конец рамки

Когда CURRENT ROW используется как начало рамки, рамка начинается с первой строки текущей группы родственных строк (все строки с тем же значением в столбце ORDER BY, что и у текущей строки).
Когда CURRENT ROW используется как конец рамки, рамка заканчивается последней строкой текущей группы родственных строк (все строки с тем же значением в столбце ORDER BY, что и у текущей строки).

<value> FOLLOWING

Начало или конец рамки

Рамка начинается или заканчивается на строках, у которых значение столбца ORDER BY отличается от значения текущей строки не более чем на <value> в большую сторону

UNBOUNDED FOLLOWING

Конец рамки

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

В данной таблице описаны граничные значения, поддерживаемые выражением ROWS.

Значение Применяется к Описание

Значение	Применяется к	Описание
UNBOUNDED PRECEDING	Начало рамки	Рамка начинается с первой строки раздела
<value> PRECEDING	Начало или конец рамки	Рамка включает `<value>` строк перед текущей строкой
CURRENT ROW	Начало или конец рамки	Рамка начинается или заканчивается на текущей строке
<value> FOLLOWING	Начало или конец рамки	Рамка включает `<value>` строк после текущей строки
UNBOUNDED FOLLOWING	Конец рамки	Рамка заканчивается на последней строке раздела

UNBOUNDED PRECEDING

Начало рамки

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

<value> PRECEDING

Начало или конец рамки

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

CURRENT ROW

Начало или конец рамки

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

<value> FOLLOWING

Начало или конец рамки

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

UNBOUNDED FOLLOWING

Конец рамки

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

Оконные выражения поддерживают две основные категории функций:

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

Агрегатные функции, такие как SUM(), COUNT(), AVG(), MIN() и MAX(), могут использоваться как оконные функции. Это также относится к любым пользовательским агрегатным функциям. Однако агрегатные функции сортирующего типа (ordered-set) и гипотезирующего типа (hypothetical-set) нельзя использовать как оконные функции. Агрегатные функции ведут себя как оконные только если за ними следует выражение OVER; в противном случае они работают как обычные агрегаты.
Оконные функции общего назначения

Эти специализированные функции работают на множестве строк, связанных с текущей строкой, как определено в выражении OVER. Они включают в себя:
- Функции ранжирования, например, RANK(), DENSE_RANK() или ROW_NUMBER().
- Функции распределения, например,CUME_DIST() или PERCENT_RANK().
- Функции доступа к значениям, например, LAG() или LEAD().

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

Функции ранжирования
Функция	Возвращаемый тип	Описание
RANK()	bigint	Вычисляет ранг строки в упорядоченной группе значений. Строки с одинаковыми значениями по критерию ранжирования получают один и тот же ранг. Для вычисления следующего ранга к текущему добавляется количество строк с одинаковым значением. В этом случае ранги могут иметь пропуски
DENSE_RANK()	bigint	Вычисляет ранг строки в упорядоченной группе строк без пропусков значений ранга. Строки с равными значениями получают одинаковый ранг
ROW_NUMBER()	bigint	Присваивает уникальный номер каждой строке, к которой применяется функция
NTILE(expr)	bigint	Разбивает упорядоченный набор данных на несколько частей (как задано в `expr`) и присваивает каждой строке номер части

Функции распределения
Функция	Возвращаемый тип	Описание
CUME_DIST()	double precision	Вычисляет кумулятивное распределение значения в группе. Строки с одинаковыми значениями всегда получают одно и то же значение кумулятивного распределения
PERCENT_RANK()	double precision	Вычисляет относительный ранг текущей строки как `(ранг - 1) / (общее число строк раздела - 1)` в пределах раздела окна

Функции доступа к значениям
Функция	Возвращаемый тип	Описание
LAG(expr [,offset] [,default])	такой же, как тип `expr`	Позволяет обращаться к нескольким строкам одной и той же таблицы без использования самосоединений. Если существует упорядоченный набор строк и известна текущая позиция курсора, функция `LAG()` возвращает значение строки, сдвинутой на `offset` строк от текущей к началу раздела. Смещение по умолчанию равно `1`. `default` задает значение, которое возвращается, если смещение выходит за границы окна. Если `default` не указан, значение по умолчанию — `NULL`
LEAD(expr [,offset] [,default])	такой же, как тип `expr`	Позволяет обращаться к нескольким строкам одной и той же таблицы без использования самосоединений. Если существует упорядоченный набор строк и известна текущая позиция курсора, функция `LEAD()` возвращает значение строки, сдвинутой на `offset` строк от текущей к концу раздела. Смещение по умолчанию равно `1`. `default` задает значение, которое возвращается, если смещение выходит за границы окна. Если `default` не указан, значение по умолчанию — `NULL`
FIRST_VALUE(expr)	такой же, как тип `expr`	Возвращает первое значение в упорядоченном наборе значений
LAST_VALUE(expr)	такой же, как тип `expr`	Возвращает последнее значение в упорядоченном наборе значений

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Затем создайте таблицу и заполните ее данными:

Создание таблицы

CREATE TABLE sales
(
    id           INT,
    product_name TEXT,
    category     TEXT,
    quantity     INT,
    price        NUMERIC(8, 2),
    sale_date    TIMESTAMP WITHOUT TIME ZONE,
    region       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

INSERT INTO sales (id, product_name, category, quantity, price, sale_date, region)
VALUES (1, 'Laptop', 'Electronics', 1, 1200.00, '2025-01-01 09:15:00', 'East'),
       (2, 'T-Shirt', 'Clothing', 2, 30.00, '2025-01-01 14:45:00', 'East'),
       (3, 'Headphones', 'Electronics', 1, 180.00, '2025-01-02 10:20:00', 'East'),
       (4, 'Coffee Maker', 'Home', 1, 85.00, '2025-01-02 13:40:00', 'East'),
       (5, 'Desk Chair', 'Home', 1, 220.00, '2025-01-02 16:30:00', 'West'),
       (6, 'Laptop', 'Electronics', 1, 1250.00, '2025-01-03 09:50:00', 'East'),
       (7, 'Wireless Mouse', 'Electronics', 1, 65.00, '2025-01-03 14:10:00', 'East'),
       (8, 'T-Shirt', 'Clothing', 3, 35.00, '2025-01-03 15:55:00', 'West'),
       (9, 'Smartphone', 'Electronics', 2, 850.00, '2025-01-04 11:05:00', 'East'),
       (10, 'Gaming Keyboard', 'Electronics', 1, 120.00, '2025-01-04 17:25:00', 'West'),
       (11, 'Bookshelf', 'Home', 1, 150.00, '2025-01-05 10:10:00', 'West'),
       (12, 'Microwave', 'Home', 1, 200.00, '2025-01-05 15:00:00', 'West');

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

SELECT region,
       SUM(quantity * price) AS sales_amount_per_region
FROM sales
GROUP BY region
ORDER BY region;

Функция SUM() уменьшает число строк, возвращаемых SQL-запросом:

 region | sales_amount_per_region
--------+-------------------------
 East   |                 4540.00
 West   |                  795.00
(2 rows)

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

SELECT id,
       region,
       sale_date,
       quantity * price                                 AS sales_amount,
       SUM(quantity * price) OVER (PARTITION BY region) AS sales_amount_per_region
FROM sales
ORDER BY region, sale_date;

Результат выглядит так:

 id | region |      sale_date      | sales_amount | sales_amount_per_region
----+--------+---------------------+--------------+-------------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |                 4540.00
  2 | East   | 2025-01-01 14:45:00 |        60.00 |                 4540.00
  3 | East   | 2025-01-02 10:20:00 |       180.00 |                 4540.00
  4 | East   | 2025-01-02 13:40:00 |        85.00 |                 4540.00
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |                 4540.00
  7 | East   | 2025-01-03 14:10:00 |        65.00 |                 4540.00
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |                 4540.00
  5 | West   | 2025-01-02 16:30:00 |       220.00 |                  795.00
  8 | West   | 2025-01-03 15:55:00 |       105.00 |                  795.00
 10 | West   | 2025-01-04 17:25:00 |       120.00 |                  795.00
 11 | West   | 2025-01-05 10:10:00 |       150.00 |                  795.00
 12 | West   | 2025-01-05 15:00:00 |       200.00 |                  795.00
(12 rows)

OVER()

Пример

Если вы хотите передать все строки набора данных в оконную функцию, используйте выражение OVER с пустыми скобками после него:

SELECT id,
       region,
       sale_date,
       quantity * price              AS sales_amount,
       SUM(quantity * price) OVER () AS sales_amount_all_regions
FROM sales
ORDER BY region, sale_date;

Результат показывает общую сумму продаж для всего набора данных:

 id | region |      sale_date      | sales_amount | sales_amount_all_regions
----+--------+---------------------+--------------+--------------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |                  5335.00
  2 | East   | 2025-01-01 14:45:00 |        60.00 |                  5335.00
  3 | East   | 2025-01-02 10:20:00 |       180.00 |                  5335.00
  4 | East   | 2025-01-02 13:40:00 |        85.00 |                  5335.00
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |                  5335.00
  7 | East   | 2025-01-03 14:10:00 |        65.00 |                  5335.00
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |                  5335.00
  5 | West   | 2025-01-02 16:30:00 |       220.00 |                  5335.00
  8 | West   | 2025-01-03 15:55:00 |       105.00 |                  5335.00
 10 | West   | 2025-01-04 17:25:00 |       120.00 |                  5335.00
 11 | West   | 2025-01-05 10:10:00 |       150.00 |                  5335.00
 12 | West   | 2025-01-05 15:00:00 |       200.00 |                  5335.00
(12 rows)

Этот SELECT-запрос вычисляет накопительный итог продаж для каждого региона:

SELECT id,
       region,
       sale_date,
       quantity * price        AS sales_amount,
       SUM(quantity * price) OVER (
           PARTITION BY region
           ORDER BY sale_date) AS running_sales_amount_per_region
FROM sales
ORDER BY region, sale_date;

Ключевое отличие — использование ORDER BY sale_date внутри оконной функции, что обеспечивает накопление суммы в хронологическом порядке продаж внутри региона. Результаты видны в столбце running_sales_amount_per_region:

 id | region |      sale_date      | sales_amount | running_sales_amount_per_region
----+--------+---------------------+--------------+---------------------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |                         1200.00
  2 | East   | 2025-01-01 14:45:00 |        60.00 |                         1260.00
  3 | East   | 2025-01-02 10:20:00 |       180.00 |                         1440.00
  4 | East   | 2025-01-02 13:40:00 |        85.00 |                         1525.00
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |                         2775.00
  7 | East   | 2025-01-03 14:10:00 |        65.00 |                         2840.00
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |                         4540.00
  5 | West   | 2025-01-02 16:30:00 |       220.00 |                          220.00
  8 | West   | 2025-01-03 15:55:00 |       105.00 |                          325.00
 10 | West   | 2025-01-04 17:25:00 |       120.00 |                          445.00
 11 | West   | 2025-01-05 10:10:00 |       150.00 |                          595.00
 12 | West   | 2025-01-05 15:00:00 |       200.00 |                          795.00
(12 rows)

Этот запрос использует именованное окно, описанное в отдельном выражении WINDOW:

SELECT id,
       region,
       sale_date,
       quantity * price    AS sales_amount,
       SUM(quantity * price) OVER
           regional_window AS running_sales_amount_per_region
FROM sales
WINDOW regional_window AS (
        PARTITION BY region
        ORDER BY sale_date
        )
ORDER BY region, sale_date;

Результат выглядит так:

 id | region |      sale_date      | sales_amount | running_sales_amount_per_region
----+--------+---------------------+--------------+---------------------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |                         1200.00
  2 | East   | 2025-01-01 14:45:00 |        60.00 |                         1260.00
  3 | East   | 2025-01-02 10:20:00 |       180.00 |                         1440.00
  4 | East   | 2025-01-02 13:40:00 |        85.00 |                         1525.00
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |                         2775.00
  7 | East   | 2025-01-03 14:10:00 |        65.00 |                         2840.00
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |                         4540.00
  5 | West   | 2025-01-02 16:30:00 |       220.00 |                          220.00
  8 | West   | 2025-01-03 15:55:00 |       105.00 |                          325.00
 10 | West   | 2025-01-04 17:25:00 |       120.00 |                          445.00
 11 | West   | 2025-01-05 10:10:00 |       150.00 |                          595.00
 12 | West   | 2025-01-05 15:00:00 |       200.00 |                          795.00
(12 rows)

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

SELECT id,
       region,
       sale_date,
       quantity * price        AS sales_amount,
       SUM(quantity * price) OVER (
           PARTITION BY region
           ORDER BY sale_date) AS running_sales_amount_per_region
FROM sales
ORDER BY region, sale_date;

В данном запросе рамка по умолчанию указана явно с помощью RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:

SELECT id,
       region,
       sale_date,
       quantity * price                                       AS sales_amount,
       SUM(quantity * price) OVER (
           PARTITION BY region
           ORDER BY sale_date
           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sales_amount_per_region
FROM sales
ORDER BY region, sale_date;

Чтобы задать рамку на основе времени, запрос с RANGE берет строки за последние 24 часа до текущей строки в том же разделе:

SELECT id,
       region,
       sale_date,
       quantity * price                                                 AS sales_amount,
       SUM(quantity * price) OVER (
           PARTITION BY region
           ORDER BY sale_date
           RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW) AS sum_last_24_hours
FROM sales
ORDER BY region, sale_date;

Результат выглядит так:

 id | region |      sale_date      | sales_amount | sum_last_24_hours
----+--------+---------------------+--------------+-------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |           1200.00
  2 | East   | 2025-01-01 14:45:00 |        60.00 |           1260.00
  3 | East   | 2025-01-02 10:20:00 |       180.00 |            240.00
  4 | East   | 2025-01-02 13:40:00 |        85.00 |            325.00
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |           1515.00
  7 | East   | 2025-01-03 14:10:00 |        65.00 |           1315.00
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |           1765.00
  5 | West   | 2025-01-02 16:30:00 |       220.00 |            220.00
  8 | West   | 2025-01-03 15:55:00 |       105.00 |            325.00
 10 | West   | 2025-01-04 17:25:00 |       120.00 |            120.00
 11 | West   | 2025-01-05 10:10:00 |       150.00 |            270.00
 12 | West   | 2025-01-05 15:00:00 |       200.00 |            470.00
(12 rows)

Этот фрагмент показывает, как вычисляются суммы в 24-часовой рамке для строк с ID 3 и 12, указывая, какие строки попадают в рамку, а какие выходят за ее пределы:

  1 | East   | 2025-01-01 09:15:00 |      1200.00 |            -- вне 24-часовой рамки
  2 | East   | 2025-01-01 14:45:00 |        60.00 |            -- внутри 24-часовой рамки
  3 | East   | 2025-01-02 10:20:00 |       180.00 |    240.00  -- текущая строка
  ...
  8 | West   | 2025-01-03 15:55:00 |       105.00 |            -- вне 24-часовой рамки
 10 | West   | 2025-01-04 17:25:00 |       120.00 |            -- внутри 24-часовой рамки
 11 | West   | 2025-01-05 10:10:00 |       150.00 |            -- внутри 24-часовой рамки
 12 | West   | 2025-01-05 15:00:00 |       200.00 |    470.00  -- текущая строка

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

SELECT id,
       region,
       sale_date,
       quantity * price                              AS sales_amount,
       SUM(quantity * price) OVER (
           PARTITION BY region
           ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_last_3_sales
FROM sales
ORDER BY region, sale_date;

Результат выглядит следующим образом:

 id | region |      sale_date      | sales_amount | sum_last_3_sales
----+--------+---------------------+--------------+------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |          1200.00
  2 | East   | 2025-01-01 14:45:00 |        60.00 |          1260.00
  3 | East   | 2025-01-02 10:20:00 |       180.00 |          1440.00
  4 | East   | 2025-01-02 13:40:00 |        85.00 |           325.00
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |          1515.00
  7 | East   | 2025-01-03 14:10:00 |        65.00 |          1400.00
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |          3015.00
  5 | West   | 2025-01-02 16:30:00 |       220.00 |           220.00
  8 | West   | 2025-01-03 15:55:00 |       105.00 |           325.00
 10 | West   | 2025-01-04 17:25:00 |       120.00 |           445.00
 11 | West   | 2025-01-05 10:10:00 |       150.00 |           375.00
 12 | West   | 2025-01-05 15:00:00 |       200.00 |           470.00
(12 rows)

Этот фрагмент показывает, как вычисляется накопительный итог по текущей строке и двум предыдущим для строк с ID 4 и 12, указывая, какие строки входят в рамку, а какие — нет:

  1 | East   | 2025-01-01 09:15:00 |      1200.00 |            -- вне 3-строчной рамки
  2 | East   | 2025-01-01 14:45:00 |        60.00 |            -- внутри 3-строчной рамки
  3 | East   | 2025-01-02 10:20:00 |       180.00 |            -- внутри 3-строчной рамки
  4 | East   | 2025-01-02 13:40:00 |        85.00 |    325.00  -- текущая строка
  ...
  8 | West   | 2025-01-03 15:55:00 |       105.00 |            -- вне 3-строчной рамки
 10 | West   | 2025-01-04 17:25:00 |       120.00 |            -- внутри 3-строчной рамки
 11 | West   | 2025-01-05 10:10:00 |       150.00 |            -- внутри 3-строчной рамки
 12 | West   | 2025-01-05 15:00:00 |       200.00 |    470.00  -- текущая строка

В этом примере вычисляется скользящее среднее на основе трех последних продаж в каждом регионе:

SELECT id,
       region,
       sale_date,
       quantity * price                              AS sales_amount,
       AVG(quantity * price) OVER (
           PARTITION BY region
           ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_last_3_sales
FROM sales
ORDER BY region, sale_date;

Результат выглядит следующим образом:

 id | region |      sale_date      | sales_amount | moving_avg_last_3_sales
----+--------+---------------------+--------------+-------------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |   1200.0000000000000000
  2 | East   | 2025-01-01 14:45:00 |        60.00 |    630.0000000000000000
  3 | East   | 2025-01-02 10:20:00 |       180.00 |    480.0000000000000000
  4 | East   | 2025-01-02 13:40:00 |        85.00 |    108.3333333333333333
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |    505.0000000000000000
  7 | East   | 2025-01-03 14:10:00 |        65.00 |    466.6666666666666667
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |   1005.0000000000000000
  5 | West   | 2025-01-02 16:30:00 |       220.00 |    220.0000000000000000
  8 | West   | 2025-01-03 15:55:00 |       105.00 |    162.5000000000000000
 10 | West   | 2025-01-04 17:25:00 |       120.00 |    148.3333333333333333
 11 | West   | 2025-01-05 10:10:00 |       150.00 |    125.0000000000000000
 12 | West   | 2025-01-05 15:00:00 |       200.00 |    156.6666666666666667
(12 rows)

Функции ROW_NUMBER() и RANK() присваивают каждой строке уникальное целое число в соответствии с ее порядком в результирующем наборе.

В этом примере каждой продаже в регионе присваивается последовательный номер с помощью функции ROW_NUMBER():

SELECT id,
       region,
       sale_date,
       ROW_NUMBER() OVER (
           PARTITION BY region
           ORDER BY sale_date) AS row_num
FROM sales
ORDER BY region, sale_date;

Результат должен выглядеть так:

 id | region |      sale_date      | row_num
----+--------+---------------------+---------
  1 | East   | 2025-01-01 09:15:00 |       1
  2 | East   | 2025-01-01 14:45:00 |       2
  3 | East   | 2025-01-02 10:20:00 |       3
  4 | East   | 2025-01-02 13:40:00 |       4
  6 | East   | 2025-01-03 09:50:00 |       5
  7 | East   | 2025-01-03 14:10:00 |       6
  9 | East   | 2025-01-04 11:05:00 |       7
  5 | West   | 2025-01-02 16:30:00 |       1
  8 | West   | 2025-01-03 15:55:00 |       2
 10 | West   | 2025-01-04 17:25:00 |       3
 11 | West   | 2025-01-05 10:10:00 |       4
 12 | West   | 2025-01-05 15:00:00 |       5
(12 rows)

Этот запрос ранжирует строки по сумме продаж в пределах каждого региона с использованием функции RANK():

SELECT id,
       region,
       quantity * price                    AS sales_amount,
       RANK() OVER (
           PARTITION BY region
           ORDER BY quantity * price DESC) AS rank_by_sales_amount
FROM sales
ORDER BY region, rank_by_sales_amount;

Результат следующий:

 id | region | sales_amount | rank_by_sales_amount
----+--------+--------------+----------------------
  9 | East   |      1700.00 |                    1
  6 | East   |      1250.00 |                    2
  1 | East   |      1200.00 |                    3
  3 | East   |       180.00 |                    4
  4 | East   |        85.00 |                    5
  7 | East   |        65.00 |                    6
  2 | East   |        60.00 |                    7
  5 | West   |       220.00 |                    1
 12 | West   |       200.00 |                    2
 11 | West   |       150.00 |                    3
 10 | West   |       120.00 |                    4
  8 | West   |       105.00 |                    5
(12 rows)

В этом примере сравнивается поведение функций RANK() и DENSE_RANK():

RANK() оставляет пропуски в последовательности рангов после совпадающих значений.
DENSE_RANK() присваивает последовательные ранги без пропусков.

Следующие запросы присваивают ранги на основе значения региона:

RANK()

SELECT id,
       region,
       quantity * price AS sales_amount,
       RANK() OVER (
           ORDER BY region DESC
           )            AS rank_by_region
FROM sales
ORDER BY region, rank_by_region;

DENSE_RANK()

SELECT id,
       region,
       quantity * price AS sales_amount,
       DENSE_RANK() OVER (
           ORDER BY region DESC
           )            AS dense_rank_by_region
FROM sales
ORDER BY region, dense_rank_by_region;

Результаты показывают, что RANK() присваивает одинаковый порядковый номер строкам с совпадающими значениями, но пропускает следующий доступный порядковый номер (1 и 6), тогда как DENSE_RANK() продолжает последовательность без пропусков (1 и 2):

RANK()

 id | region | sales_amount | rank_by_region
----+--------+--------------+----------------
  2 | East   |        60.00 |              6
  9 | East   |      1700.00 |              6
  6 | East   |      1250.00 |              6
  4 | East   |        85.00 |              6
  3 | East   |       180.00 |              6
  1 | East   |      1200.00 |              6
  7 | East   |        65.00 |              6
 12 | West   |       200.00 |              1
  5 | West   |       220.00 |              1
 10 | West   |       120.00 |              1
 11 | West   |       150.00 |              1
  8 | West   |       105.00 |              1
(12 rows)

DENSE_RANK()

 id | region | sales_amount | dense_rank_by_region
----+--------+--------------+----------------------
  2 | East   |        60.00 |                    2
  9 | East   |      1700.00 |                    2
  1 | East   |      1200.00 |                    2
  4 | East   |        85.00 |                    2
  3 | East   |       180.00 |                    2
  6 | East   |      1250.00 |                    2
  7 | East   |        65.00 |                    2
  5 | West   |       220.00 |                    1
 10 | West   |       120.00 |                    1
 11 | West   |       150.00 |                    1
 12 | West   |       200.00 |                    1
  8 | West   |       105.00 |                    1
(12 rows)

Этот запрос возвращает дату первой продажи в каждом регионе с помощью функции FIRST_VALUE():

SELECT id,
       region,
       sale_date,
       FIRST_VALUE(sale_date) OVER (
           PARTITION BY region
           ORDER BY sale_date) AS first_sale_date
FROM sales
ORDER BY region, sale_date;

Результат выглядит так:

 id | region |      sale_date      |   first_sale_date
----+--------+---------------------+---------------------
  1 | East   | 2025-01-01 09:15:00 | 2025-01-01 09:15:00
  2 | East   | 2025-01-01 14:45:00 | 2025-01-01 09:15:00
  3 | East   | 2025-01-02 10:20:00 | 2025-01-01 09:15:00
  4 | East   | 2025-01-02 13:40:00 | 2025-01-01 09:15:00
  6 | East   | 2025-01-03 09:50:00 | 2025-01-01 09:15:00
  7 | East   | 2025-01-03 14:10:00 | 2025-01-01 09:15:00
  9 | East   | 2025-01-04 11:05:00 | 2025-01-01 09:15:00
  5 | West   | 2025-01-02 16:30:00 | 2025-01-02 16:30:00
  8 | West   | 2025-01-03 15:55:00 | 2025-01-02 16:30:00
 10 | West   | 2025-01-04 17:25:00 | 2025-01-02 16:30:00
 11 | West   | 2025-01-05 10:10:00 | 2025-01-02 16:30:00
 12 | West   | 2025-01-05 15:00:00 | 2025-01-02 16:30:00
(12 rows)

Этот запрос возвращает дату последней продажи в каждом регионе с помощью функции LAST_VALUE():

SELECT id,
       region,
       sale_date,
       LAST_VALUE(sale_date) OVER (
           PARTITION BY region
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM sales
ORDER BY region, sale_date;

Обратите внимание, что выражение ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING указано для того, чтобы функция LAST_VALUE() учитывала все строки раздела, поскольку окно по умолчанию — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Результат следующий:

 id | region |      sale_date      |   last_sale_date
----+--------+---------------------+---------------------
  1 | East   | 2025-01-01 09:15:00 | 2025-01-04 11:05:00
  2 | East   | 2025-01-01 14:45:00 | 2025-01-04 11:05:00
  3 | East   | 2025-01-02 10:20:00 | 2025-01-04 11:05:00
  4 | East   | 2025-01-02 13:40:00 | 2025-01-04 11:05:00
  6 | East   | 2025-01-03 09:50:00 | 2025-01-04 11:05:00
  7 | East   | 2025-01-03 14:10:00 | 2025-01-04 11:05:00
  9 | East   | 2025-01-04 11:05:00 | 2025-01-04 11:05:00
  5 | West   | 2025-01-02 16:30:00 | 2025-01-05 15:00:00
  8 | West   | 2025-01-03 15:55:00 | 2025-01-05 15:00:00
 10 | West   | 2025-01-04 17:25:00 | 2025-01-05 15:00:00
 11 | West   | 2025-01-05 10:10:00 | 2025-01-05 15:00:00
 12 | West   | 2025-01-05 15:00:00 | 2025-01-05 15:00:00
(12 rows)

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

SELECT id,
       region,
       sale_date,
       quantity * price        AS sales_amount,
       LAG(quantity * price) OVER (
           PARTITION BY region
           ORDER BY sale_date) AS prev_sales_amount
FROM sales
ORDER BY region, sale_date;

Вывод выглядит так:

 id | region |      sale_date      | sales_amount | prev_sales_amount
----+--------+---------------------+--------------+-------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |
  2 | East   | 2025-01-01 14:45:00 |        60.00 |           1200.00
  3 | East   | 2025-01-02 10:20:00 |       180.00 |             60.00
  4 | East   | 2025-01-02 13:40:00 |        85.00 |            180.00
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |             85.00
  7 | East   | 2025-01-03 14:10:00 |        65.00 |           1250.00
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |             65.00
  5 | West   | 2025-01-02 16:30:00 |       220.00 |
  8 | West   | 2025-01-03 15:55:00 |       105.00 |            220.00
 10 | West   | 2025-01-04 17:25:00 |       120.00 |            105.00
 11 | West   | 2025-01-05 10:10:00 |       150.00 |            120.00
 12 | West   | 2025-01-05 15:00:00 |       200.00 |            150.00
(12 rows)

Функция LEAD() возвращает значение из следующей строки в том же разделе:

SELECT id,
       region,
       sale_date,
       quantity * price        AS sales_amount,
       LEAD(quantity * price) OVER (
           PARTITION BY region
           ORDER BY sale_date) AS next_sales_amount
FROM sales
ORDER BY region, sale_date;

Вывод должен выглядеть следующим образом:

 id | region |      sale_date      | sales_amount | next_sales_amount
----+--------+---------------------+--------------+-------------------
  1 | East   | 2025-01-01 09:15:00 |      1200.00 |             60.00
  2 | East   | 2025-01-01 14:45:00 |        60.00 |            180.00
  3 | East   | 2025-01-02 10:20:00 |       180.00 |             85.00
  4 | East   | 2025-01-02 13:40:00 |        85.00 |           1250.00
  6 | East   | 2025-01-03 09:50:00 |      1250.00 |             65.00
  7 | East   | 2025-01-03 14:10:00 |        65.00 |           1700.00
  9 | East   | 2025-01-04 11:05:00 |      1700.00 |
  5 | West   | 2025-01-02 16:30:00 |       220.00 |            105.00
  8 | West   | 2025-01-03 15:55:00 |       105.00 |            120.00
 10 | West   | 2025-01-04 17:25:00 |       120.00 |            150.00
 11 | West   | 2025-01-05 10:10:00 |       150.00 |            200.00
 12 | West   | 2025-01-05 15:00:00 |       200.00 |
(12 rows)

В этом примере оконная функция применяется внутри CTE для поиска продаж, сумма которых превышает среднее значение по региону:

WITH sales_with_avg AS (SELECT id,
                               quantity * price                                 AS sales_amount,
                               AVG(quantity * price) OVER (PARTITION BY region) AS avg_sales_amount_per_region
                        FROM sales)
SELECT id,
       sales_amount,
       avg_sales_amount_per_region
FROM sales_with_avg
WHERE sales_amount > avg_sales_amount_per_region
ORDER BY id;

Результат выглядит так:

 id | sales_amount | avg_sales_amount_per_region
----+--------------+-----------------------------
  1 |      1200.00 |        648.5714285714285714
  5 |       220.00 |        159.0000000000000000
  6 |      1250.00 |        648.5714285714285714
  9 |      1700.00 |        648.5714285714285714
 12 |       200.00 |        159.0000000000000000
(5 rows)

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

JSON

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

Обзор оконных выражений

Расположение в запросе SELECT

Синтаксис описания окна

Рамка окна: RANGE и ROWS

Функции, поддерживаемые оконными выражениями

Оконные функции общего назначения

Примеры использования

Обзор оконных функций

Выражение WINDOW

RANGE и ROWS

Скользящее среднее

Функции ранжирования

Функции доступа к значениям

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