Что такое оконные функции и какие кейсы ими решают?

1️⃣ Как кратко ответить

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

2️⃣ Подробное объяснение темы

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

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

1. Функция: Это может быть любая из встроенных функций, таких как SUM(), AVG(), ROW_NUMBER(), RANK(), LEAD(), LAG() и другие.
2. OVER(): Ключевое слово, которое определяет окно строк, к которым применяется функция.
3. PARTITION BY: Опциональная часть, которая разделяет набор данных на части, к каждой из которых применяется оконная функция.
4. ORDER BY: Определяет порядок строк в каждом разделе окна.

Пример использования оконной функции

Рассмотрим таблицу sales, содержащую данные о продажах:

Задача: Вычислить кумулятивную сумму продаж для каждого продукта.

SELECT
  id,
  product,
  amount,
  SUM(amount) OVER (PARTITION BY product ORDER BY sale_date) AS cumulative_sum
FROM
  sales;

Объяснение кода:

• SELECT id, product, amount: Выбираем столбцы id, product и amount из таблицы sales.
• SUM(amount) OVER (...) AS cumulative_sum: Используем оконную функцию SUM() для вычисления кумулятивной суммы.
• PARTITION BY product: Разделяем данные по продуктам, чтобы кумулятивная сумма считалась отдельно для каждого продукта.
• ORDER BY sale_date: Определяем порядок строк по дате продажи, чтобы сумма накапливалась в хронологическом порядке.

Применение оконных функций

1. Скользящие средние: Вычисление среднего значения по определенному количеству предыдущих строк.
2. Ранжирование данных: Присвоение рангов строкам в наборе данных.
3. Кумулятивные суммы: Накопление суммы значений по строкам.
4. Сравнение строк: Использование функций LEAD() и LAG() для сравнения значений соседних строк.

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