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

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

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

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

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

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

  1. PARTITION BY — разбивает данные на группы (аналогично GROUP BY, но без свертки).
  2. ORDER BY — сортирует строки внутри окна.
  3. Фрейм (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;

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

  1. Агрегатные функции как оконные
    Любая агрегатная функция (например, SUM(), AVG()) может быть использована с OVER().

    SELECT 
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative_sum
FROM sales;

    Здесь cumulative_sum покажет нарастающий итог выручки по дням.

  2. Специализированные оконные функции

    • 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 дня (текущий + два предыдущих).

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

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

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

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

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

  3. Удаление дубликатов
    Использование 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 и экспериментируйте с примерами!