Функция SUM PARTITION BY: эффективное суммирование данных SQL

Для кого эта статья:

• аналитики данных и BI-специалисты
• профессионалы в области SQL и работы с базами данных
• студенты и обучающиеся по курсам аналитики данных и SQL

В мире аналитики данных скорость и точность имеют решающее значение. Представьте: вам нужно проанализировать продажи по регионам, одновременно видя долю каждой сделки в общей картине. Именно здесь на помощь приходит конструкция SUM() + PARTITION BY в SQL – мощный инструмент, позволяющий выполнять сложные расчеты без громоздких подзапросов и временных таблиц. Эта функция – настоящий game-changer для аналитиков, работающих с большими данными в 2025 году, когда каждый сэкономленный миллисекунд преобразуется в конкурентное преимущество. 🚀

Хотите овладеть полным арсеналом инструментов для эффективной аналитики данных? Курс «SQL для анализа данных» от Skypro подробно раскрывает секреты оконных функций, включая SUM PARTITION BY, и другие продвинутые техники SQL. Вы научитесь писать оптимизированные запросы, которые выполняются в разы быстрее стандартных решений – навык, за который работодатели готовы платить премиальные зарплаты. Не упускайте возможность выделиться среди конкурентов!

Что такое SUM PARTITION BY и зачем аналитикам это нужно

SUM PARTITION BY — это применение агрегатной функции SUM() с конструкцией PARTITION BY в контексте оконных функций SQL. Эта комбинация позволяет вычислять суммы по группам данных (разделам), сохраняя при этом детализацию на уровне отдельных строк — возможность, недоступная при использовании обычного GROUP BY. 📊

Главные преимущества использования SUM PARTITION BY для аналитиков данных:

• Одновременное получение агрегированных данных и детализации в одном запросе
• Вычисление нарастающих итогов и скользящих сумм
• Сравнение индивидуальных значений с групповыми показателями
• Существенное сокращение количества подзапросов
• Повышение производительности и читаемости кода

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

• Средние показатели для таблиц с 1 млн строк по данным бенчмаркинга 2025 года

Игорь Соколов, Lead BI-аналитик

В начале 2024 года я столкнулся с задачей анализа эффективности рекламных каналов для крупного ритейлера. Нужно было показать не только общую сумму продаж по каждому каналу, но и вклад каждой отдельной транзакции в общий результат. Первоначально я построил решение с использованием подзапросов и временных таблиц, но при масштабировании на полный объем данных (около 15 млн транзакций) запрос стал выполняться более 4 минут.

После оптимизации с применением SUM PARTITION BY время выполнения сократилось до 28 секунд! Это позволило нам внедрить интерактивные дашборды с автоматическим обновлением каждые 15 минут, что дало маркетологам возможность оперативно перераспределять бюджеты между каналами в течение дня. По итогам квартала эффективность маркетинговых расходов выросла на 23%.

Синтаксис SUM PARTITION BY: правила использования

Правильное применение SUM PARTITION BY начинается с понимания его синтаксиса. Эта конструкция является частью синтаксиса оконных функций, введенного в стандарт SQL:2003 и поддерживаемого большинством современных СУБД. 🖥️

SELECT 
column1, 
column2,
SUM(value_column) OVER (PARTITION BY group_column [ORDER BY sort_column] [frame_clause]) AS sum_value
FROM 
table_name
[WHERE condition];

Разберём ключевые элементы синтаксиса:

• SUM(value_column) — агрегатная функция, которая суммирует значения указанного столбца
• OVER — указывает, что мы используем оконную функцию
• PARTITION BY group_column — определяет, каким образом данные будут разделены на группы (разделы)
• ORDER BY sort_column — необязательный параметр, используемый для сортировки строк внутри раздела (критически важен для нарастающих итогов)
• frame_clause — необязательное указание границ окна (например, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW для нарастающего итога)

В зависимости от СУБД и версии синтаксис может незначительно отличаться. Важно учитывать особенности реализации в конкретной системе.

Пример базового использования SUM PARTITION BY:

SELECT 
order_id,
product_id,
category_id,
sales_amount,
SUM(sales_amount) OVER (PARTITION BY category_id) AS category_total_sales
FROM 
sales
WHERE 
order_date >= '2025-01-01';

Этот запрос вернёт все заказы с начала 2025 года, дополнив каждую строку суммой продаж по категории товара, к которой относится данная позиция.

Важно правила использования SUM PARTITION BY:

• Функция может применяться только к числовым типам данных
• NULL значения игнорируются при вычислении суммы (поведение, аналогичное стандартному SUM)
• В одном запросе можно использовать несколько различных оконных функций с разными параметрами PARTITION BY
• Результаты оконных функций нельзя использовать в WHERE, но можно в ORDER BY или в подзапросах
• Использование ORDER BY внутри OVER меняет семантику функции, превращая обычное суммирование в нарастающий итог

Оконная агрегация SUM PARTITION BY против GROUP BY

Выбор между SUM PARTITION BY и GROUP BY — один из ключевых моментов в оптимизации SQL-запросов. Несмотря на внешнее сходство (оба подхода позволяют суммировать данные по группам), их функциональность и применение радикально отличаются. 🔄

Анна Морозова, Data Engineer

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

Изначально аналитики использовали GROUP BY с последующим соединением результатов с исходной таблицей, что приводило к многократному сканированию данных. При объёме в 40+ миллионов транзакций ежедневно отчёты формировались более 30 минут.

После рефакторинга запросов с применением SUM PARTITION BY удалось не только сократить время выполнения до 4 минут, но и значительно упростить код. Особенно впечатляющим результат оказался для сложных аналитических расчётов с множественными разрезами данных — некоторые запросы ускорились в 15-20 раз!

Этот подход позволил внедрить систему real-time трекинга аномалий, что помогло выявить и предотвратить мошеннические активности на сумму около $1,2 млн в первый месяц работы.

Основные различия между этими техниками:

• Гранулярность результатов: GROUP BY агрегирует данные, сокращая число строк до количества уникальных групп; SUM PARTITION BY сохраняет все исходные строки
• Доступность детальной информации: GROUP BY "теряет" информацию о конкретных строках; SUM PARTITION BY сохраняет доступ ко всем исходным полям
• Комбинирование уровней агрегации: в GROUP BY требуются сложные конструкции с UNION или подзапросами; SUM PARTITION BY позволяет делать это в одном проходе
• Сравнение значений: GROUP BY не позволяет напрямую сравнивать индивидуальное значение с агрегированным; SUM PARTITION BY делает это естественным образом

Сравним два подхода на примере расчета процента продаж от общего объема по категории:

Подход с GROUP BY (требует JOIN):

-- Шаг 1: Вычисляем общие продажи по категориям
WITH category_totals AS (
SELECT 
category_id, 
SUM(sales_amount) AS total_sales
FROM 
sales
GROUP BY 
category_id
)
-- Шаг 2: Соединяем с исходной таблицей для расчета процентов
SELECT 
s.order_id,
s.product_id,
s.category_id,
s.sales_amount,
ct.total_sales AS category_total,
(s.sales_amount / ct.total_sales) * 100 AS percentage
FROM 
sales s
JOIN 
category_totals ct ON s.category_id = ct.category_id;

Тот же результат с SUM PARTITION BY (один запрос):

SELECT 
order_id,
product_id,
category_id,
sales_amount,
SUM(sales_amount) OVER (PARTITION BY category_id) AS category_total,
(sales_amount / SUM(sales_amount) OVER (PARTITION BY category_id)) * 100 AS percentage
FROM 
sales;

Очевидно, что второй вариант более лаконичен, читабелен и, как правило, более производителен, особенно на больших объемах данных.

Оптимизация запросов с SUM PARTITION BY

Несмотря на высокую производительность, неправильное использование SUM PARTITION BY может привести к неоптимальному выполнению запросов. Следующие методы оптимизации помогут достичь максимальной эффективности. ⚡

Ключевые стратегии оптимизации:

• Ограничение объема данных: применяйте WHERE до обработки оконных функций, чтобы уменьшить объем вычислений
• Выбор оптимальных столбцов для PARTITION BY: используйте столбцы с подходящей кардинальностью (не слишком высокой, не слишком низкой)
• Индексирование: создавайте индексы для столбцов, используемых в PARTITION BY и ORDER BY
• Материализация промежуточных результатов: для сложных запросов с множественными оконными функциями
• Оптимизация порядка операций: размещайте вычисления с SUM PARTITION BY после фильтрации и до сортировки в логике запроса

Рассмотрим неоптимизированный запрос и его улучшенную версию:

Неоптимальный вариант:

SELECT 
order_id,
customer_id,
order_date,
product_id,
sales_amount,
SUM(sales_amount) OVER (PARTITION BY customer_id) AS customer_total,
SUM(sales_amount) OVER (PARTITION BY DATE_TRUNC('month', order_date)) AS monthly_total
FROM 
sales
ORDER BY 
order_date DESC;

Оптимизированный вариант:

WITH filtered_sales AS (
SELECT 
order_id,
customer_id,
order_date,
product_id,
sales_amount,
DATE_TRUNC('month', order_date) AS order_month
FROM 
sales
WHERE 
order_date > CURRENT_DATE – INTERVAL '6 months'
)
SELECT 
fs.*,
SUM(sales_amount) OVER (PARTITION BY customer_id) AS customer_total,
SUM(sales_amount) OVER (PARTITION BY order_month) AS monthly_total
FROM 
filtered_sales fs
ORDER BY 
order_date DESC;

Ключевые улучшения в оптимизированной версии:

1. Предварительная фильтрация данных (WHERE) до применения оконных функций
2. Вычисление order_month один раз, а не в каждой оконной функции
3. Более эффективное использование WITH для материализации промежуточных данных

Производительность оконных функций зависит от множества факторов, включая объем данных, распределение значений и настройки базы данных. При оптимизации запросов с SUM PARTITION BY следует учитывать:

• Чем больше разделов (партиций), тем больше памяти требуется для выполнения
• Оконные функции выполняются после большинства других операций в логическом порядке выполнения запроса
• Современные оптимизаторы (2025) в PostgreSQL, Oracle и SQL Server умеют оптимизировать многие случаи использования оконных функций
• Для очень больших таблиц может быть эффективнее использовать материализованные представления с предварительным расчетом агрегатов

Расширьте свои знания о SQL и оптимизации запросов – пройдите Тест на профориентацию от Skypro! Узнайте, насколько ваши аналитические навыки соответствуют требованиям рынка и какие компетенции стоит развить для карьерного роста в области работы с данными. Тест анализирует ваши текущие навыки SQL, включая владение оконными функциями типа SUM PARTITION BY, и дает персонализированные рекомендации по дальнейшему профессиональному развитию.

Практические кейсы применения SUM PARTITION BY

SUM PARTITION BY находит широкое применение в реальных бизнес-задачах. Рассмотрим наиболее востребованные сценарии использования этой мощной конструкции. 💼

1. Расчет процентного соотношения

Вычисление доли продаж каждого продукта в общем объеме категории:

SELECT 
product_id,
product_name,
category_id,
sales_amount,
SUM(sales_amount) OVER (PARTITION BY category_id) AS category_total,
ROUND(100.0 * sales_amount / SUM(sales_amount) OVER (PARTITION BY category_id), 2) AS percentage
FROM 
product_sales
WHERE 
sale_date >= '2025-01-01';

2. Нарастающие итоги (running totals)

Расчет накопленной суммы продаж по дням для каждого менеджера:

SELECT 
sale_date,
manager_id,
manager_name,
daily_sales,
SUM(daily_sales) OVER (
PARTITION BY manager_id 
ORDER BY sale_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sales
FROM 
manager_daily_performance
WHERE 
sale_date BETWEEN '2025-01-01' AND '2025-03-31'
ORDER BY 
manager_id, sale_date;

3. Скользящие суммы (moving averages)

Расчет суммы продаж за последние 7 дней для выявления тенденций:

SELECT 
sale_date,
daily_revenue,
SUM(daily_revenue) OVER (
ORDER BY sale_date 
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7day_sum
FROM 
daily_sales
WHERE 
sale_date >= '2025-01-01'
ORDER BY 
sale_date;

4. Отклонение от среднего по группе

Анализ эффективности сотрудников относительно средних показателей их отделов:

SELECT 
employee_id,
employee_name,
department_id,
performance_score,
SUM(performance_score) OVER (PARTITION BY department_id) AS dept_total_score,
COUNT(*) OVER (PARTITION BY department_id) AS dept_employee_count,
ROUND(
performance_score – 
(SUM(performance_score) OVER (PARTITION BY department_id) / 
COUNT(*) OVER (PARTITION BY department_id)),
2
) AS deviation_from_avg
FROM 
employee_performance
WHERE 
evaluation_year = 2025;

5. Детализация вклада в общий результат

Анализ структуры доходов по странам и регионам:

SELECT 
country,
region,
product_category,
revenue,
SUM(revenue) OVER (PARTITION BY country) AS country_total,
SUM(revenue) OVER (PARTITION BY country, region) AS region_total,
SUM(revenue) OVER () AS grand_total,
ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY country), 2) AS pct_of_country,
ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY country, region), 2) AS pct_of_region,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM 
global_sales
WHERE 
sale_date BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY 
country, region, pct_of_region DESC;

Сравнение эффективности различных подходов для типичных аналитических задач:

• По результатам бенчмарков на датасетах объемом 10+ миллионов строк в PostgreSQL 16 (2025)

Практический совет: при работе со сложными аналитическими запросами, содержащими несколько оконных функций, рассмотрите возможность их материализации через CTE или временные таблицы, особенно если разные оконные функции используют различные PARTITION BY. Это может значительно ускорить выполнение запроса, позволяя оптимизатору работать с меньшими наборами данных на каждом этапе.

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