Оконные функции в SQL: Инструмент для глубокого анализа данных

У нас уже была статья про оконные функции в SQL, где подробно описано что это такое, а сегодня поделимся дополнительными функциями и примерами.

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

Как создавать оконные функции?
Создание оконных функций в SQL осуществляется с использованием ключевого слова OVER(), которое определяет рамку окна. Синтаксис оконной функции выглядит следующим образом:

функция_агрегирования() OVER (PARTITION BY столбец ORDER BY столбец)

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


Примеры использования
1. Скользящая сумма продаж:

   SELECT sale_date,
          sales_amount,
          SUM(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_sum
   FROM sales;
   

Этот запрос рассчитывает сумму продаж за последние 7 дней для каждой даты.

2. Ранжирование сотрудников по зарплате:

   SELECT employee_id,
          salary,
          RANK() OVER (ORDER BY salary DESC) AS salary_rank
   FROM employees;
   

Здесь каждому сотруднику присваивается ранг в зависимости от его зарплаты.

3. Кумулятивная сумма
Кумулятивная сумма позволяет отслеживать общую сумму значений по мере их накопления. Это полезно для анализа трендов.

SELECT sale_date,
       sales_amount,
       SUM(sales_amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM sales;

В этом запросе для каждой даты рассчитывается кумулятивная сумма продаж, что позволяет увидеть, как меняется общая сумма со временем.

4. Вычисление процента от общего значения
С помощью оконных функций можно легко рассчитать процент от общего значения для каждой строки.

SELECT product_id,
       sales_amount,
       SUM(sales_amount) OVER () AS total_sales,
       (sales_amount * 100.0 / SUM(sales_amount) OVER ()) AS sales_percentage
FROM sales;

Здесь мы вычисляем процент продаж каждого продукта от общего объема продаж. Это позволяет быстро оценить вклад каждого продукта в общую выручку.

5. Сравнение текущего значения с предыдущим
Функции LAG() и LEAD() позволяют сравнивать текущее значение с предыдущими или следующими значениями в наборе данных.

SELECT sale_date,
       sales_amount,
       LAG(sales_amount, 1) OVER (ORDER BY sale_date) AS previous_day_sales,
       sales_amount - LAG(sales_amount, 1) OVER (ORDER BY sale_date) AS sales_difference
FROM sales;

В этом запросе мы получаем продажи за предыдущий день и разницу между текущими и предыдущими продажами. Это помогает быстро выявить изменения в продажах.

Сохраняйте себе, пересылайте коллегам и просто поддержите нас 🔥

Полминутки юмора 🙃

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

Всех ждем в 19:00 по МСК!

Савелий ждёт вас в 19:00 по МСК на вебинаре 😉