Какие знаешь виды оконных функций?

Какие знаешь виды оконных функций?

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

Категория 1: Агрегирующие функции (Aggregate Window Functions)

Используют агрегацию в рамках окна без свёртывания результата:

SUM() OVER

Нарастающая сумма по датам:

SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM sales
ORDER BY date;

-- Результат:
-- 2025-01-01 | 100 | 100
-- 2025-01-02 | 150 | 250
-- 2025-01-03 | 200 | 450

AVG() OVER

Средняя цена за последние 7 дней (скользящее окно):

SELECT 
  date,
  price,
  AVG(price) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as avg_7days
FROM stock_prices;

COUNT() OVER

Количество заказов пользователя к текущей дате:

SELECT 
  user_id,
  order_date,
  COUNT(*) OVER (PARTITION BY user_id ORDER BY order_date) as order_number
FROM orders;

-- Результат: порядковый номер заказа каждого пользователя

MIN() / MAX() OVER

Минимальная и максимальная цена в период:

SELECT 
  date,
  price,
  MIN(price) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as min_30d,
  MAX(price) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as max_30d
FROM stock_prices;

Категория 2: Функции ранжирования (Ranking Functions)

Определяют позицию строки в наборе.

ROW_NUMBER()

Уникальный номер каждой строки (без пропусков):

SELECT 
  user_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) as rank
FROM leaderboard;

-- Результат: 1, 2, 3, 4, 5... (без пропусков)

RANK()

Ранг с пропусками при равных значениях:

SELECT 
  user_id,
  score,
  RANK() OVER (ORDER BY score DESC) as rank
FROM leaderboard;

-- Если два пользователя имеют score 100:
-- rank: 1, 1, 3 (пропускается 2)

DENSE_RANK()

Ранг БЕЗ пропусков при равных значениях:

SELECT 
  user_id,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) as rank
FROM leaderboard;

-- Если два пользователя имеют score 100:
-- rank: 1, 1, 2 (без пропусков)

NTILE()

Разделяет данные на N групп (квартили, децили):

SELECT 
  user_id,
  revenue,
  NTILE(4) OVER (ORDER BY revenue DESC) as quartile
FROM users;

-- quartile: 1 (top 25%), 2 (next 25%), 3, 4

Категория 3: Функции смещения (Offset Functions)

Доступ к значениям соседних строк.

LAG()

Предыдущее значение (для сравнения день с днём):

SELECT 
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) as prev_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) as day_change
FROM daily_stats;

-- Результат: видим день в день рост/падение

LEAD()

Следующее значение (для прогноза):

SELECT 
  date,
  price,
  LEAD(price, 1) OVER (ORDER BY date) as next_day_price
FROM stock;

FIRST_VALUE() / LAST_VALUE()

Первое и последнее значение в окне:

SELECT 
  month,
  revenue,
  FIRST_VALUE(revenue) OVER (ORDER BY month) as first_month,
  LAST_VALUE(revenue) OVER (
    ORDER BY month 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as last_month
FROM monthly_revenue;

Примечание: важно указать UNBOUNDED FOLLOWING, иначе LAST_VALUE вернёт текущую строку!

NTH_VALUE()

N-ое значение в окне:

SELECT 
  date,
  price,
  NTH_VALUE(price, 3) OVER (ORDER BY date) as 3rd_price
FROM prices;

Категория 4: Функции распределения (Distribution Functions)

PERCENT_RANK()

Относительный ранг (от 0 до 1):

SELECT 
  user_id,
  score,
  PERCENT_RANK() OVER (ORDER BY score) as percentile
FROM leaderboard;

-- Результат: 0.0 (первый), 0.5 (середина), 1.0 (последний)

CUME_DIST()

Кумулятивное распределение:

SELECT 
  user_id,
  salary,
  CUME_DIST() OVER (ORDER BY salary) as cume_dist
FROM employees;

-- Показывает, какой % сотрудников зарабатывает меньше или равно текущему

Синтаксис оконной функции

function_name() OVER (
  [PARTITION BY column1, column2, ...]  -- разделить данные на группы
  [ORDER BY column1 ASC/DESC, ...]       -- порядок внутри окна
  [frame_specification]                  -- размер окна
)

PARTITION BY

Разделяет данные на независимые окна:

SELECT 
  department,
  employee,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

-- Средняя зарплата рассчитывается отдельно для каждого отдела

ORDER BY

Определяет порядок и часто указывает направление (например, для наростающей суммы):

SUM(amount) OVER (ORDER BY date)  -- нарастающая
SUM(amount) OVER (PARTITION BY user_id ORDER BY date)  -- наростающая по пользователю

Frame Specification

Определяет, какие строки включаются в расчёт:

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 7 строк (6 до + текущая)
ROWS UNBOUNDED PRECEDING AND CURRENT ROW   -- от первой до текущей
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW  -- 7 дней

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

Пример 1: Когорта анализ

Найти когорту пользователей по месяцу первой покупки:

SELECT 
  user_id,
  purchase_date,
  DATE_TRUNC('month', 
    FIRST_VALUE(purchase_date) OVER (PARTITION BY user_id ORDER BY purchase_date)
  ) as cohort_month,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) as purchase_number
FROM orders;

Пример 2: Выявить гэпы в данных

SELECT 
  date,
  LEAD(date) OVER (ORDER BY date) - date as gap_days
FROM events
WHERE LEAD(date) OVER (ORDER BY date) - date > 1;

Пример 3: Top N per Group

Топ 3 товара по выручке в каждой категории:

WITH ranked AS (
  SELECT 
    category,
    product,
    revenue,
    RANK() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
  FROM sales
)
SELECT * FROM ranked WHERE rank <= 3;

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

Плюсы оконных функций:

• Одна строка результата на одну строку входа (vs GROUP BY)
• Один scan таблицы
• Нет необходимости в JOIN к себе

Оптимизация:

-- Хорошо: с индексом на (user_id, date)
SELECT 
  user_id,
  date,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY date) as running_total
FROM orders;

-- Плохо: без индекса → full scan + sort

Когда НЕ использовать оконные функции

• Если нужна агрегация в одну строку → используй GROUP BY
• Если performance критичен и нет индексов → оптимизируй индексы
• Если нужно множество разных окон → consider materialized views

Оконные функции — это essential skill для System Analyst, работающего с большими данными и аналитикой.