Агрегация с оконными функциями: OVER(), PARTITION BY, ORDER BY

Когда обычных агрегатов недостаточно 🧐

Стандартные агрегатные функции (SUM, AVG, COUNT и др.) хороши, но у них есть ограничение — они сворачивают множество строк в одну. А что если нам нужно сохранить все исходные строки, но добавить к ним вычисленные значения? Здесь на помощь приходят оконные функции!

-- Обычный SUM сворачивает таблицу
SELECT department, SUM(salary) 
FROM employees
GROUP BY department;

-- Оконная функция сохраняет строки
SELECT name, department, salary,
       SUM(salary) OVER(PARTITION BY department) as dept_total
FROM employees;

Основы оконных функций: OVER() 🏗️

Ключевое слово OVER() — это то, что превращает обычную агрегатную функцию в оконную. Оно определяет "окно" — набор строк, к которым применяется функция.

SELECT product, month, revenue,
       AVG(revenue) OVER() as avg_revenue
FROM sales;

В этом примере AVG(revenue) OVER() считает среднее по всем строкам таблицы.

Группировка без GROUP BY: PARTITION BY 🎯

PARTITION BY внутри OVER() работает аналогично GROUP BY, но не сворачивает строки:

SELECT employee_id, department, salary,
       SUM(salary) OVER(PARTITION BY department) as dept_sum
FROM employees;

💡 Важно: Каждая стрлота сохраняет свою индивидуальность, но получает доступ к данным всей группы!

Управление порядком: ORDER BY в окнах ⏳

Добавляя ORDER BY в оконную функцию, мы меняем её поведение:

SELECT date, product, revenue,
       SUM(revenue) OVER(ORDER BY date) as running_total
FROM daily_sales;

Теперь SUM будет считать нарастающий итог по датам! Это мощный инструмент для анализа временных рядов.

Комбинируем PARTITION BY и ORDER BY 🧩

Совместное использование этих операторов даёт максимальную гибкость:

SELECT department, employee, salary,
       SUM(salary) OVER(PARTITION BY department ORDER BY hire_date) as dept_running_total
FROM employees;

Здесь мы получаем накопительную сумму зарплат по отделам с учётом даты найма!

Практический пример: Анализ продаж 🛒

Применим знания на реальном кейсе:

SELECT 
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(PARTITION BY salesperson ORDER BY sale_date) AS running_total,
    AVG(amount) OVER(PARTITION BY salesperson) AS avg_sale,
    amount - AVG(amount) OVER(PARTITION BY salesperson) AS diff_from_avg
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-03-31';

Этот запрос показывает: 1. Накопительный итог продаж по каждому сотруднику 2. Средний чек сотрудника за период 3. Отклонение каждой продажи от среднего чека

Производительность: Что важно знать ⚡

Оконные функции мощные, но требуют ресурсов:

Избегайте сложных окон в огромных таблицах
PARTITION BY по колонке с низкой кардинальностью (мало уникальных значений) работает быстрее
Индексы на колонках в ORDER BY могут ускорить выполнение

Готовимся к продвинутому уровню 🚀

После освоения базовых возможностей изучите:

Ранжирующие функции (RANK, DENSE_RANK, ROW_NUMBER)
Функции смещения (LAG, LEAD)
Кастомные рамки окон (ROWS BETWEEN)

Но сначала отточите мастерство работы с OVER(), PARTITION BY и ORDER BY — это фундамент оконных функций!