Оконные функции без потери строк — PostgreSQL 18 с

Оконные функции: вычисления без потери строк

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

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

#Из каких частей состоит окно

Выражение после OVER задаёт окно:

PARTITION BY делит строки на независимые группы;
ORDER BY задаёт порядок внутри каждой группы;
рамка (frame) определяет, какие строки группы доступны текущему вычислению.

PARTITION BY user_id не объединяет строки, а лишь начинает отдельное окно для каждого пользователя.

#Номер и место в рейтинге

SELECT
    product_id,
    category_id,
    price,
    row_number() OVER (
        PARTITION BY category_id
        ORDER BY price DESC, product_id
    ) AS row_no,
    rank() OVER (
        PARTITION BY category_id
        ORDER BY price DESC
    ) AS price_rank,
    dense_rank() OVER (
        PARTITION BY category_id
        ORDER BY price DESC
    ) AS dense_price_rank
FROM products;

Для каждой категории:

row_number выдаёт уникальные номера 1, 2, 3 и далее;
rank даёт одинаковое место товарам с равной ценой и оставляет пропуск после ничьей: 1, 1, 3;
dense_rank даёт одинаковое место без пропуска: 1, 1, 2.

Строки с одинаковым значением сортировки называют равными по порядку (peers). Если нужны ровно три товара, используйте row_number и полный порядок с product_id. Если нужны все товары на третьем ценовом месте, подходит rank или dense_rank по бизнес-правилу.

#Первые N строк каждой группы

Результат оконной функции ещё недоступен в WHERE того же уровня запроса. Сначала вычислим номера в CTE, затем отфильтруем:

WITH ranked AS (
    SELECT
        p.*,
        row_number() OVER (
            PARTITION BY category_id
            ORDER BY price DESC, id
        ) AS rn
    FROM products AS p
)
SELECT *
FROM ranked
WHERE rn <= 3
ORDER BY category_id, rn;

Так мы получаем три самых дорогих товара каждой категории, не теряя остальные столбцы выбранных строк.

#Накопительный итог и рамка

SELECT
    id,
    user_id,
    created_at,
    total_amount,
    sum(total_amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_revenue
FROM orders;

Рамка читается как «от первой строки группы до текущей включительно». ROWS считает именно строки в полном порядке created_at, id.

Если рамку не указать, окно с ORDER BY использует правила RANGE и включает все равные по сортировке строки. При одинаковом времени накопительная сумма может прыгнуть сразу на сумму нескольких заказов. Поэтому для построчного итога рамку ROWS лучше писать явно.

#Почему last_value часто удивляет

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

SELECT
    id,
    last_value(status) OVER (
        PARTITION BY user_id
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS final_status
FROM orders;

UNBOUNDED FOLLOWING означает последнюю строку группы. Без этой границы last_value часто совпадает со значением текущей строки.

#Предыдущая и следующая строка

lag берёт значение предыдущей строки, а lead — следующей:

SELECT
    id,
    user_id,
    created_at,
    lag(created_at) OVER (
        PARTITION BY user_id ORDER BY created_at, id
    ) AS previous_order_at,
    total_amount - lag(total_amount) OVER (
        PARTITION BY user_id ORDER BY created_at, id
    ) AS amount_delta
FROM orders;

У первого заказа пользователя нет предыдущего, поэтому lag вернёт NULL. Не заменяйте его нулём без причины: «предыдущей строки нет» и «предыдущее значение равно нулю» — разные факты.

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

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

SELECT
    id,
    row_number() OVER timeline AS order_no,
    lag(created_at) OVER timeline AS previous_at
FROM orders
WINDOW timeline AS (
    PARTITION BY user_id ORDER BY created_at, id
);

Именованное окно уменьшает повторение текста. Не объединяйте разные окна только ради краткости: у них могут различаться порядок или рамка.

#Стоимость оконных функций

Окну обычно нужна сортировка по столбцам группы и порядка. Если памяти не хватает, PostgreSQL использует временный файл.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT user_id,
       row_number() OVER (PARTITION BY user_id ORDER BY created_at, id)
FROM orders;

В плане смотрите число строк, способ сортировки, память, диск и число узлов WindowAgg. Один индекс не обязан подходить нескольким окнам с разным порядком. Глобальное увеличение work_mem опасно при множестве одновременных запросов.

#Практика

В лаборатории 12 вы построите историю заказов с номером строки, предыдущим временем и накопительной суммой. Проверка зафиксирует полный порядок и результат второй строки пользователя.

Подробнее: оконные функции, вызов оконной функции.