middle

Какие основные оконные функции существуют в PostgreSQL?

PostgreSQL предоставляет ранжирующие функции (ROW_NUMBER, RANK, DENSE_RANK, NTILE) и функции смещения (LAG, LEAD, FIRST_VALUE, LAST_VALUE). Вместе с агрегатными функциями (SUM, AVG, COUNT) в оконном контексте они покрывают большинство аналитических задач.

Ранжирующие функции

Пример

SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
    NTILE(4)     OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Функция	Описание	Одинаковые значения
`ROW_NUMBER()`	Уникальный номер строки	Разные номера
`RANK()`	Ранг с пропусками	Одинаковый ранг, следующий с пропуском
`DENSE_RANK()`	Ранг без пропусков	Одинаковый ранг, следующий без пропуска
`NTILE(n)`	Разбивка на n групп	Равномерное распределение

Функции смещения

Пример

SELECT
    id, amount, created_at,
    LAG(amount, 1) OVER (ORDER BY created_at) AS prev_amount,
    LEAD(amount, 1) OVER (ORDER BY created_at) AS next_amount,
    FIRST_VALUE(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS first_tx,
    LAST_VALUE(amount) OVER (
        PARTITION BY account_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_tx
FROM transactions;

Практические задачи

-- Топ-3 транзакции по каждому счёту
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC) AS rn
    FROM transactions
)
SELECT * FROM ranked WHERE rn <= 3;

-- Разница с предыдущей транзакцией
SELECT
    id, amount,
    amount - LAG(amount) OVER (ORDER BY created_at) AS diff_with_prev,
    created_at - LAG(created_at) OVER (ORDER BY created_at) AS time_gap
FROM transactions
WHERE account_id = 100;

-- Процент от общей суммы
SELECT
    account_id, amount,
    ROUND(amount * 100.0 / SUM(amount) OVER (), 2) AS pct_of_total,
    ROUND(amount * 100.0 / SUM(amount) OVER (PARTITION BY account_id), 2) AS pct_of_account
FROM transactions;

-- Нарастающий итог с ежедневной группировкой
SELECT
    date(created_at) AS day,
    SUM(amount) AS daily_total,
    SUM(SUM(amount)) OVER (ORDER BY date(created_at)) AS cumulative_total
FROM transactions
GROUP BY date(created_at);

На собеседовании: обязательный вопрос — отличие ROW_NUMBER, RANK, DENSE_RANK на одинаковых значениях. Классическая задача: «топ-N записей в каждой группе» — решается через ROW_NUMBER + PARTITION BY + CTE с фильтром.

#postgresql