Что такое оконная функция?

Что такое оконная функция?

Оконная функция (Window Function) в SQL и обработке данных — это функция, которая выполняет вычисления на наборе строк (окне), связанных с текущей строкой, но не объединяет результаты в одну строку (как GROUP BY). Результат возвращается для каждой строки в отдельном столбце.

Синтаксис

SELECT
    column_name,
    WINDOW_FUNCTION() OVER (
        [PARTITION BY column1, column2, ...]
        [ORDER BY column3 [ASC|DESC]]
        [ROWS BETWEEN ... AND ...]
    ) AS result
FROM table_name;

Ключевые части:

• PARTITION BY — разделить данные на группы (необязательно)
• ORDER BY — сортировка внутри группы
• ROWS — определение границ окна

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

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

SELECT
    date,
    sales,
    SUM(sales) OVER (ORDER BY date) AS running_total,
    AVG(sales) OVER (PARTITION BY MONTH(date)) AS monthly_avg,
    COUNT(*) OVER (PARTITION BY region) AS region_count
FROM sales_data
ORDER BY date;

-- Результат:
-- date       sales  running_total  monthly_avg  region_count
-- 2024-01-01 100    100            150          45
-- 2024-01-02 150    250            150          45
-- 2024-01-03 200    450            150          45
-- 2024-02-01 180    630            190          45

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

SELECT
    product,
    sales,
    ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank_dense,
    RANK() OVER (ORDER BY sales DESC) AS rank_with_ties,
    DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank,
    PERCENT_RANK() OVER (ORDER BY sales) AS percentile
FROM products;

-- Результат:
-- product  sales  rank_dense  rank_with_ties  dense_rank  percentile
-- Laptop   5000   1           1               1           1.0
-- Phone    5000   2           1               1           1.0
-- Tablet   3000   3           3               2           0.67
-- Watch    2000   4           4               3           0.33

3. Функции смещения (Lead/Lag)

SELECT
    date,
    price,
    LAG(price) OVER (ORDER BY date) AS previous_price,
    LEAD(price) OVER (ORDER BY date) AS next_price,
    price - LAG(price) OVER (ORDER BY date) AS price_change,
    FIRST_VALUE(price) OVER (ORDER BY date) AS first_price,
    LAST_VALUE(price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
FROM stock_prices
ORDER BY date;

-- Результат:
-- date       price  previous_price  next_price  price_change  first_price  last_price
-- 2024-01-01 100    NULL            105         NULL          100          95
-- 2024-01-02 105    100             110         5             100          95
-- 2024-01-03 110    105             95          5             100          95
-- 2024-01-04 95     110             NULL        -15           100          95

Практические примеры с Python (Pandas)

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

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'date': pd.date_range('2024-01-01', periods=10),
    'sales': [100, 120, 110, 130, 140, 150, 145, 160, 170, 180]
})

# Аналог SQL окна
df['moving_avg_3'] = df['sales'].rolling(window=3, min_periods=1).mean()
df['running_sum'] = df['sales'].expanding().sum()
df['rank'] = df['sales'].rank(method='min', ascending=False)

print(df)

Пример 2: Расчёт прироста по группам

df = pd.DataFrame({
    'region': ['North', 'North', 'North', 'South', 'South', 'South'],
    'month': [1, 2, 3, 1, 2, 3],
    'revenue': [1000, 1100, 1200, 900, 950, 1050]
})

# Оконная функция по группам
df['prev_revenue'] = df.groupby('region')['revenue'].shift(1)
df['revenue_growth'] = df['revenue'] - df['prev_revenue']
df['region_total'] = df.groupby('region')['revenue'].transform('sum')
df['pct_of_region'] = df['revenue'] / df['region_total'] * 100

print(df)

Пример 3: Нахождение топ-N по группам

df = pd.DataFrame({
    'category': ['A', 'A', 'A', 'B', 'B', 'B'],
    'product': ['P1', 'P2', 'P3', 'P4', 'P5', 'P6'],
    'sales': [100, 200, 150, 300, 250, 400]
})

# TOP-2 по категориям
df['rank'] = df.groupby('category')['sales'].rank(method='min', ascending=False)
top_2 = df[df['rank'] <= 2]

print(top_2)
# category product sales rank
# A        P2      200   1
# A        P1      100   2
# B        P6      400   1
# B        P5      250   2

Типы окна (ROWS/RANGE)

-- Текущая строка
SUM(sales) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND CURRENT ROW)

-- Последние 3 строки (включая текущую)
SUM(sales) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

-- От начала до текущей строки (cumulative)
SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Все строки в разделе
SUM(sales) OVER (PARTITION BY region ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- ±2 строки от текущей (frame window)
AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

Примеры использования в Data Science

# Временные ряды: лаговые признаки
for i in range(1, 4):
    df[f'sales_lag_{i}'] = df['sales'].shift(i)

# Скользящая стандартная ошибка
df['rolling_std'] = df['sales'].rolling(window=7).std()

# Кумулятивное распределение
df['cumsum_pct'] = df['sales'].cumsum() / df['sales'].sum() * 100

# Z-score внутри группы
df['z_score'] = df.groupby('category')['sales'].transform(
    lambda x: (x - x.mean()) / x.std()
)

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

• Временные ряды — lag, lead, moving averages
• Ранжирование — top-N, percentiles
• Аналитические отчёты — cumulative totals, running averages
• Feature engineering — статистики по окнам для ML
• Сравнение с соседями — изменения, отклонения

Производительность

• Оконные функции в SQL быстрее GROUP BY + JOIN для большинства случаев
• В Pandas используй groupby().transform() вместо циклов
• Для скользящих операций используй rolling() и expanding()