Оконные функции в реальных задачах: ранжирование, агрегация, сравнение

Почему оконные функции — это суперсила аналитика? 💪

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

Ранжирование: кто в топе? 🏆

Когда нужно вывести рейтинг продавцов, топ товаров или места студентов — тут нам помогут функции ROW_NUMBER(), RANK() и DENSE_RANK().

-- Топ 5 продавцов по выручке в каждом регионе
WITH sales_ranking AS (
  SELECT 
    seller_id,
    region,
    revenue,
    RANK() OVER(PARTITION BY region ORDER BY revenue DESC) as rank
  FROM sales
)
SELECT * FROM sales_ranking WHERE rank <= 5;

🔥 Что важно:

• PARTITION BY — это «окно» (группа строк), внутри которого работает функция
• ORDER BY определяет порядок ранжирования
• RANK() пропускает места при одинаковых значениях, DENSE_RANK() — нет

Агрегация: умные суммы и средние 🧮

Оконные функции позволяют считать агрегаты без группировки, сохраняя исходные строки. Это особенно полезно для расчёта долей и накопительных итогов.

-- Доля каждого товара в общей выручке и накопительный итог
SELECT 
  product_id,
  revenue,
  revenue / SUM(revenue) OVER() * 100 as revenue_percent,
  SUM(revenue) OVER(ORDER BY revenue DESC) as running_total
FROM products;

💡 Профессиональный лайфхак:
Используйте ROWS BETWEEN для точного управления окном:

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

Сравнение: как меняются показатели? 🔍

Оконные функции идеальны для анализа динамики. Сравниваем текущую строку с предыдущей или следующей с помощью LAG() и LEAD().

-- Рост выручки по месяцам в процентах
WITH monthly_growth AS (
  SELECT 
    month,
    revenue,
    LAG(revenue) OVER(ORDER BY month) as prev_revenue
  FROM monthly_sales
)
SELECT 
  month,
  revenue,
  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) as growth_pct
FROM monthly_growth;

🚀 Продвинутый приём:
Можно сравнивать не только с соседями, но и с первым/последним значением в окне:

-- Отклонение от среднего по отделу
SELECT 
  employee,
  salary,
  salary - AVG(salary) OVER(PARTITION BY department) as diff_from_avg
FROM employees;

Где это реально применяется? 🌎

1. Финансы: расчёт скользящих средних для акций
2. E-commerce: определение топ-N товаров в категории
3. HR: анализ зарплат относительно средних по отделу
4. Логистика: сравнение времени доставки с предыдущими заказами

Три главных правила мастерства 🛠️

1. Всегда проверяйте PARTITION BY — это основа правильного окна
2. Используйте ORDER BY в агрегирующих функциях для накопительных итогов
3. Для сложных окон применяйте именованные подзапросы (WITH)