Что такое оконные функции в БД?

Оконные функции (Window Functions) в БД

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

Отличие от обычных агрегирующих функций

-- ОБЫЧНАЯ агрегирующая функция (GROUP BY)
SELECT department, AVG(salary) FROM employees GROUP BY department;
-- Результат: 2 строки (по одной на отдел)

-- ОКОННАЯ функция
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;
-- Результат: все строки + добавлен столбец с средней зарплатой по отделу

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

Синтаксис

FUNCTION_NAME(...) OVER (
    [PARTITION BY column1, column2]
    [ORDER BY column3 [ASC|DESC]]
    [ROWS BETWEEN ... AND ...]
)

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

1. Агрегирующие функции

-- Сумма зарплат по каждому отделу (для каждой строки)
SELECT 
    name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) as dept_total
FROM employees;

-- Результат:
-- name    | department | salary | dept_total
-- Alice   | Sales      | 50000  | 180000
-- Bob     | Sales      | 60000  | 180000
-- Charlie | IT         | 70000  | 230000
-- David   | IT         | 80000  | 230000
-- Eve     | IT         | 80000  | 230000

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

-- ROW_NUMBER: уникальный номер в пределах окна
SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- RANK: позиция с учётом одинаковых значений (пропускает номера)
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- DENSE_RANK: позиция без пропусков
SELECT 
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

-- Результат:
-- name    | salary | rank | dense_rank
-- Alice   | 100000 | 1    | 1
-- Bob     | 100000 | 1    | 1
-- Charlie | 80000  | 3    | 2
-- David   | 70000  | 4    | 3

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

-- LAG: значение из предыдущей строки
SELECT 
    order_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY order_date) as prev_amount,
    amount - LAG(amount, 1) OVER (ORDER BY order_date) as change
FROM orders;

-- LEAD: значение из следующей строки
SELECT 
    order_date,
    amount,
    LEAD(amount, 1) OVER (ORDER BY order_date) as next_amount
FROM orders;

-- FIRST_VALUE / LAST_VALUE
SELECT 
    name,
    salary,
    FIRST_VALUE(salary) OVER (ORDER BY hire_date) as first_salary,
    LAST_VALUE(salary) OVER (ORDER BY hire_date) as last_salary
FROM employees;

4. Аналитические функции

-- PERCENT_RANK: относительная позиция
SELECT 
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) as percentile
FROM employees;

-- NTILE: распределение на N групп
SELECT 
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

FRAME (скользящее окно)

Визуальное представление:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

         ↓ current row
Row 1  []
       [Row1, Row2]  ← окно для Row 2
Row 2  []
Row 3  [Row3, Row4]  ← окно для Row 4
       []
Row 4

-- Скользящее среднее (3-дневное)
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as moving_avg
FROM orders;

-- С нарастающим итогом (кумулятивная сумма)
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_sum
FROM orders
ORDER BY order_date;

-- Результат:
-- order_date | amount | cumulative_sum
-- 2025-01-01 | 1000   | 1000
-- 2025-01-02 | 1500   | 2500
-- 2025-01-03 | 2000   | 4500
-- 2025-01-04 | 500    | 5000

Практические примеры

Пример 1: Найти сотрудников, зарабатывающих больше среднего по отделу

SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees
WHERE salary > AVG(salary) OVER (PARTITION BY department);
-- Но ВНИМАНИЕ! WHERE не может использовать оконные функции
-- Используй CTE или подзапрос:

WITH employee_stats AS (
    SELECT 
        name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) as avg_salary
    FROM employees
)
SELECT * FROM employee_stats
WHERE salary > avg_salary;

Пример 2: Top-N per group (топ 3 продукта по продажам в каждой категории)

WITH ranked_products AS (
    SELECT 
        category,
        product_name,
        sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank
    FROM products
)
SELECT category, product_name, sales
FROM ranked_products
WHERE rank <= 3;

Пример 3: Сравнение с предыдущим месяцем

SELECT 
    DATE_TRUNC('month', order_date)::date as month,
    SUM(amount) as monthly_revenue,
    LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as prev_month_revenue,
    ROUND(
        100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date))) 
        / LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)),
        2
    ) as growth_percent
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Пример 4: Сессионизация (группировка по сессиям)

WITH session_data AS (
    SELECT 
        user_id,
        event_time,
        SUM(CASE WHEN 
            event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) > INTERVAL '30 minutes'
            THEN 1 ELSE 0 END
        ) OVER (PARTITION BY user_id ORDER BY event_time) as session_id
    FROM user_events
)
SELECT 
    user_id,
    session_id,
    COUNT(*) as events_in_session,
    MIN(event_time) as session_start,
    MAX(event_time) as session_end
FROM session_data
GROUP BY user_id, session_id;

Использование в Python (SQLAlchemy + Raw SQL)

from sqlalchemy import text
from sqlalchemy.orm import Session

def get_employees_with_dept_avg(session: Session):
    query = text("""
    SELECT 
        name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) as avg_dept_salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
    FROM employees
    ORDER BY department, rank_in_dept
    """)
    
    return session.execute(query).fetchall()

# Использование с ORM
from sqlalchemy import func, over
from sqlalchemy.orm import Session

def get_ranked_employees(session: Session):
    query = session.query(
        Employee.name,
        Employee.department,
        Employee.salary,
        func.avg(Employee.salary).over(
            partition_by=Employee.department
        ).label('avg_salary'),
        func.rank().over(
            partition_by=Employee.department,
            order_by=Employee.salary.desc()
        ).label('rank')
    ).order_by(Employee.department)
    
    return query.all()

Преимущества оконных функций

1. Сложные аналитические запросы без GROUP BY
2. Скользящие и кумулятивные вычисления
3. Сравнения между строками (LAG/LEAD)
4. Ранжирование и разбиение на группы
5. Один запрос вместо нескольких джойнов

Когда использовать

• Аналитика и отчёты
• Вычисление рангов и позиций
• Скользящие средние и тренды
• Сравнение строк внутри одной таблицы
• Top-N элементы в каждой группе

Итог

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