Основы оконных функций

Оконные функции в PostgreSQL: мощный инструмент аналитики

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

Основы оконных функций

Ключевым элементом оконных функций является конструкция OVER(), которая определяет «окно» — набор строк, используемых для вычислений. Внутри OVER() можно задать:

PARTITION BY — разбивает данные на группы (аналогично GROUP BY, но без свертки).
ORDER BY — сортирует строки внутри окна.
Фрейм (ROWS/RANGE) — задает диапазон строк относительно текущей записи.

Пример структуры:

SELECT
    column1,
    column2,
    WINDOW_FUNCTION() OVER (
        PARTITION BY column3
        ORDER BY column4
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS result
FROM table_name;

Типы оконных функций

Агрегатные функции как оконные
Любая агрегатная функция (например, SUM(), AVG()) может быть использована с OVER().
```
SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative_sum
FROM sales;
```
Здесь cumulative_sum покажет нарастающий итог выручки по дням.
Специализированные оконные функции
- ROW_NUMBER() — порядковый номер строки в окне.
- RANK() и DENSE_RANK() — ранжирование с пропусками и без.
- LEAD() и LAG() — доступ к данным следующей или предыдущей строки.
- FIRST_VALUE() и LAST_VALUE() — первое и последнее значение в окне.
Пример ранжирования:
```
SELECT
    product,
    price,
    RANK() OVER (ORDER BY price DESC) AS rank
FROM products;
```
Ранжирование товаров по убыванию цены.

Фреймы: контроль диапазона

Фрейм определяет, какие строки внутри окна участвуют в вычислениях. Используются ключевые слова:

ROWS BETWEEN N PRECEDING AND M FOLLOWING — физические строки.
RANGE BETWEEN … — логические интервалы (например, по значениям).

Пример скользящего среднего:

SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales;

Расчет среднего выручки за последние 3 дня (текущий + два предыдущих).

Практические кейсы

Сравнение с предыдущим периодом

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS delta
FROM monthly_sales;

Процент от итога группы

SELECT
    department,
    employee,
    salary,
    salary * 100.0 / SUM(salary) OVER (PARTITION BY department) AS percent
FROM employees;

Удаление дубликатов
Использование ROW_NUMBER() для выборки уникальных записей:

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Оптимизация производительности

Индексы: Используйте индексы для столбцов в PARTITION BY и ORDER BY.
Фреймы: Узкие фреймы (например, ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) работают быстрее.
Избегайте избыточных сортировок: Если несколько окон используют одинаковый ORDER BY, вынесите его в отдельное выражение WINDOW.

Заключение

Оконные функции в PostgreSQL открывают возможности для сложной аналитики прямо на уровне СУБД, уменьшая необходимость обработки данных на стороне приложения. Они идеальны для задач, требующих сохранения контекста каждой строки: ранжирования, расчетов скользящих показателей, сравнений и трендов. Освоив этот инструмент, вы сможете писать более эффективные и лаконичные запросы, раскрывая полный потенциал ваших данных.

Для углубленного изучения обратитесь к документации PostgreSQL и экспериментируйте с примерами!

# Основы оконных функций