Кумулятивная сумма с условием и оконные функции

Кумулятивная сумма (или накопительная сумма) — это сумма значений, которая накапливается по мере продвижения через набор данных. В SQL кумулятивную сумму можно вычислить с помощью оконных функций, таких как SUM() в сочетании с ключевым словом OVER().

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

Основы кумулятивной суммы

Кумулятивную сумму можно вычислить следующим образом:

SELECT column_name,
       SUM(column_name) OVER (
           ORDER BY order_column
       ) AS cumulative_sum
FROM table_name;

• ORDER BY order_column: Определяет порядок строк для вычисления кумулятивной суммы.

Кумулятивная сумма с условием

Чтобы вычислить кумулятивную сумму с условием, можно использовать CASE в сочетании с SUM() и оконными функциями.

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

Рассмотрим таблицу sales с полями sale_date и amount. Мы хотим вычислить кумулятивную сумму продаж только для тех записей, где сумма продаж больше 100.

Запрос:

SELECT sale_date, amount,
       SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) OVER (
           ORDER BY sale_date
       ) AS cumulative_sum
FROM sales;

• CASE WHEN amount > 100 THEN amount ELSE 0 END: Применяет условие, чтобы включить только те значения, которые больше 100.

• ORDER BY sale_date: Определяет порядок строк по дате продажи.

Результат: Кумулятивная сумма продаж будет вычислена только для записей, где сумма продаж превышает 100.

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

Рассмотрим таблицу employees с полями department, salary, и hire_date. Мы хотим вычислить кумулятивную сумму зарплат для сотрудников в каждом отделе, но только для тех сотрудников, которые были наняты после 1 января 2023 года.

Запрос:

SELECT department, employee_id, salary, hire_date,
       SUM(CASE WHEN hire_date > '2023-01-01' THEN salary ELSE 0 END) OVER (
           PARTITION BY department
           ORDER BY hire_date
       ) AS cumulative_salary
FROM employees;

• PARTITION BY department: Разделяет данные на группы по отделам.

• CASE WHEN hire_date > '2023-01-01' THEN salary ELSE 0 END: Применяет условие, чтобы включить только те зарплаты, которые относятся к сотрудникам, нанятым после 1 января 2023 года.

• ORDER BY hire_date: Определяет порядок строк по дате найма.

Результат: Кумулятивная сумма зарплат будет вычислена в пределах каждого отдела, учитывая только сотрудников, нанятых после 1 января 2023 года.

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

Рассмотрим таблицу financials с полями quarter, revenue, и expenses. Мы хотим вычислить кумулятивную сумму доходов по кварталам, но только для тех кварталов, где расходы превышают 5000.

Запрос:

SELECT quarter, revenue, expenses,
       SUM(CASE WHEN expenses > 5000 THEN revenue ELSE 0 END) OVER (
           ORDER BY quarter
       ) AS cumulative_revenue
FROM financials;

• CASE WHEN expenses > 5000 THEN revenue ELSE 0 END: Применяет условие, чтобы включить только те доходы, которые связаны с кварталами, где расходы превышают 5000.

• ORDER BY quarter: Определяет порядок строк по кварталам.

Результат: Кумулятивная сумма доходов будет вычислена только для тех кварталов, где расходы превышают 5000.

Заключение

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