Аналитические функции: что это такое и как их использовать

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

• специалисты по аналитике данных и бизнес-аналитике
• студенты и начинающие аналитики, желающие улучшить свои навыки
• руководители и менеджеры, принимающие решения на основе данных

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

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

Аналитические функции: основные принципы и возможности

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

Ключевые принципы аналитических функций:

• Окно данных (Window) — набор строк, над которыми выполняется вычисление
• Секционирование (Partition By) — разделение данных на логические группы
• Порядок (Order By) — определение последовательности обработки строк внутри окна
• Границы окна (Frame) — дополнительное уточнение диапазона строк для вычисления

Рассмотрим простой пример. Допустим, у нас есть таблица продаж по регионам, и мы хотим рассчитать долю каждого продукта в общем объеме продаж региона:

SELECT 
region, 
product, 
sales,
SUM(sales) OVER (PARTITION BY region) as total_region_sales,
sales / SUM(sales) OVER (PARTITION BY region) * 100 as percentage
FROM 
sales_data;

В этом запросе аналитическая функция SUM() с конструкцией OVER позволяет подсчитать сумму продаж по каждому региону и использовать её для вычисления процентного соотношения, не теряя детализации до уровня отдельных продуктов.

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

Виды аналитических функций и их практическое применение

Аналитические функции можно классифицировать в зависимости от типа выполняемых операций. Каждый вид имеет свои особенности и области применения. Рассмотрим основные из них и как они решают реальные бизнес-задачи. 💼

Анна Соколова, Ведущий аналитик данных

В моей практике был кейс с крупным интернет-магазином. Компания хотела понять, какие товары демонстрируют аномальный рост или падение продаж. Традиционный подход с GROUP BY не давал полной картины.

Мы применили аналитические функции LAG() и LEAD() для расчета процентного изменения продаж между периодами:

SQL

Скопировать код

SELECT 
product_id, 
date, 
sales,
LAG(sales) OVER (PARTITION BY product_id ORDER BY date) as prev_sales,
(sales – LAG(sales) OVER (PARTITION BY product_id ORDER BY date)) / 
LAG(sales) OVER (PARTITION BY product_id ORDER BY date) * 100 as growth_percent
FROM 
daily_sales

Это позволило моментально выявить товары с резкими скачками продаж. Один продукт показал рост на 400% после того, как попал в обзор популярного блогера. Маркетологи оперативно увеличили рекламный бюджет для этой категории, что привело к дополнительному росту продаж на 27%.

Функции ранжирования позволяют определить положение строки относительно других строк в окне. Наиболее часто используются:

• RANK() — присваивает ранг каждой строке, при этом одинаковые значения получают одинаковый ранг, а следующий ранг "прыгает" на соответствующее число позиций
• DENSE_RANK() — аналогичен RANK(), но без пропусков в нумерации
• ROW_NUMBER() — присваивает уникальный последовательный номер каждой строке, независимо от повторяющихся значений

Пример использования для определения Топ-3 сотрудников в каждом отделе:

SELECT 
department, 
employee_name, 
performance_score,
RANK() OVER (PARTITION BY department ORDER BY performance_score DESC) as rank
FROM 
employee_performance
WHERE 
RANK() OVER (PARTITION BY department ORDER BY performance_score DESC) <= 3;

Функции навигации позволяют получать доступ к данным из других строк в пределах окна вычисления:

• LAG(value, offset) — возвращает значение из предыдущей строки с заданным смещением
• LEAD(value, offset) — возвращает значение из следующей строки с заданным смещением
• FIRST_VALUE(value) и LAST_VALUE(value) — возвращают первое и последнее значение в окне соответственно

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

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

• SUM() OVER() — накопительная сумма или сумма в пределах окна
• AVG() OVER() — среднее значение в пределах окна
• COUNT() OVER() — количество строк в пределах окна
• MIN() и MAX() OVER() — минимальное и максимальное значение в пределах окна

Определение скользящего среднего за 3 дня для анализа тренда:

SELECT 
date, 
stock_price,
AVG(stock_price) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3day
FROM 
stock_data;

Функции распределения помогают понять относительное положение значения в общем наборе данных:

• PERCENT_RANK() — относительный ранг строки (от 0 до 1)
• CUME_DIST() — кумулятивное распределение (доля строк, значения которых меньше или равны текущему)
• NTILE(n) — разделение набора данных на n примерно равных групп

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

Аналитические функции для работы с бизнес-данными

В бизнес-аналитике аналитические функции становятся незаменимым инструментом для выявления закономерностей, анализа эффективности и принятия стратегических решений. Рассмотрим, как эти функции применяются в разных отраслях и бизнес-процессах. 📈

Одним из наиболее распространенных применений аналитических функций в бизнес-аналитике является RFM-анализ (Recency, Frequency, Monetary) — метод клиентской сегментации по трем параметрам: давность последней покупки, частота покупок и сумма покупок.

SELECT 
customer_id,
NTILE(5) OVER(ORDER BY last_purchase_date DESC) as R,
NTILE(5) OVER(ORDER BY purchase_count) as F,
NTILE(5) OVER(ORDER BY total_spent) as M
FROM 
customer_metrics;

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

В финансовом анализе аналитические функции используются для расчета комплексных показателей и выявления аномалий:

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

Дмитрий Волков, Финансовый аналитик

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

Мы разработали модель, основанную на аналитических функциях, которая анализировала не только абсолютные значения транзакций, но и их контекст:

SQL

Скопировать код

WITH transaction_metrics AS (
SELECT 
t.*,
AVG(amount) OVER (PARTITION BY customer_id ORDER BY transaction_time 
ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) as avg_recent_amount,
COUNT(*) OVER (PARTITION BY customer_id, terminal_id 
RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) as terminal_frequency
FROM 
transactions t
)
SELECT 
*,
CASE 
WHEN amount > avg_recent_amount * 5 AND terminal_frequency <= 2 THEN 'High Risk'
WHEN amount > avg_recent_amount * 3 THEN 'Medium Risk'
ELSE 'Low Risk'
END as risk_category
FROM 
transaction_metrics;

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

В HR-аналитике аналитические функции применяются для всестороннего анализа персонала:

• Анализ тенденций в заработной плате по отделам и должностям
• Выявление корреляций между показателями эффективности и другими метриками
• Прогнозирование текучести кадров на основе исторических данных
• Оценка эффективности программ обучения и развития

Маркетинговые аналитики используют аналитические функции для оценки эффективности кампаний и каналов продвижения:

SELECT 
campaign_id,
channel,
spend,
conversions,
SUM(spend) OVER(PARTITION BY campaign_id) as campaign_total_spend,
SUM(conversions) OVER(PARTITION BY campaign_id) as campaign_total_conversions,
spend / SUM(spend) OVER(PARTITION BY campaign_id) * 100 as spend_percentage,
conversions / spend as conversion_rate
FROM 
marketing_data;

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

Инструменты и платформы для реализации аналитических функций

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

Реляционные СУБД с поддержкой аналитических функций:

• PostgreSQL — открытая СУБД с мощной поддержкой оконных функций, включая все основные функции ранжирования, агрегации и навигации
• Microsoft SQL Server — предлагает расширенные аналитические функции через синтаксис OVER с дополнительными возможностями в последних версиях
• Oracle Database — один из пионеров внедрения аналитических функций с полной поддержкой сложных вычислений внутри окон
• MySQL (версии 8.0+) — относительно недавно добавлена полноценная поддержка оконных функций

Платформы для работы с большими данными:

• Apache Spark SQL — фреймворк для обработки больших данных с поддержкой аналитических функций
• Google BigQuery — облачное хранилище с мощными возможностями для аналитических вычислений
• Amazon Redshift — специализированная аналитическая СУБД с колоночным хранением
• Snowflake — облачная платформа для работы с данными, оптимизированная для аналитических запросов

Библиотеки и инструменты для языков программирования:

• pandas (Python) — реализует оконные функции через методы .rolling(), .expanding() и .transform()
• dplyr (R) — обеспечивает функции window_order(), window_frame() и другие для работы с окнами
• data.table (R) — высокопроизводительная библиотека с поддержкой оконных операций
• SQL Alchemy (Python) — позволяет использовать оконные функции SQL в коде Python

Сравнение производительности и функциональности популярных инструментов:

Выбор инструмента зависит от нескольких факторов:

• Объем данных — для больших объемов данных (от терабайт) стоит обратить внимание на специализированные решения вроде Spark или облачных платформ
• Требования к производительности — если критична скорость выполнения, приоритет отдается решениям с оптимизированным движком
• Имеющаяся экосистема — часто выбор ограничивается уже существующей инфраструктурой
• Бюджет — открытые решения могут быть предпочтительнее при ограниченном бюджете

Для тех, кто начинает работать с аналитическими функциями, рекомендуется сначала освоить их в PostgreSQL или pandas, которые имеют низкий порог вхождения и хорошую документацию. По мере роста объемов данных и сложности задач можно переходить к более специализированным инструментам.

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

import pandas as pd

# Создаем датафрейм с данными продаж
sales_df = pd.DataFrame({
'region': ['East', 'East', 'West', 'West', 'East', 'West'],
'product': ['A', 'B', 'A', 'B', 'C', 'C'],
'sales': [100, 200, 150, 250, 300, 350]
})

# Расчет доли продаж каждого продукта в регионе
sales_df['region_total'] = sales_df.groupby('region')['sales'].transform('sum')
sales_df['percentage'] = sales_df['sales'] / sales_df['region_total'] * 100

# Ранжирование продуктов по продажам внутри региона
sales_df['rank'] = sales_df.groupby('region')['sales'].rank(method='dense', ascending=False)

print(sales_df)

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

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

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

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

Этапы освоения аналитических функций:

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

Рекомендуемая последовательность изучения аналитических функций:

1. Начните с понимания концепции окон и конструкции OVER
2. Освойте агрегатные функции (SUM, AVG, COUNT) в контексте окон
3. Изучите функции ранжирования (RANK, DENSE_RANK, ROW_NUMBER)
4. Перейдите к функциям навигации (LEAD, LAG, FIRST_VALUE)
5. Углубитесь в функции распределения (PERCENT_RANK, CUME_DIST)
6. Изучите различные способы определения границ окна
7. Научитесь комбинировать аналитические функции для решения комплексных задач

Типичные ошибки при изучении аналитических функций и как их избежать:

• Непонимание отличий от GROUP BY — четко разграничивайте, когда использовать каждый подход
• Игнорирование порядка выполнения — помните, что WHERE выполняется до аналитических функций
• Избыточное секционирование — используйте только необходимые столбцы для партиционирования
• Неучет границ окна — по умолчанию границы окна зависят от упорядочивания, изучите различные варианты определения границ
• Проблемы производительности — следите за эффективностью запросов, особенно при работе с большими данными

Для эффективного освоения аналитических функций рекомендуется следующий подход:

• Изучайте документацию используемой СУБД или инструмента
• Практикуйтесь на реальных или учебных наборах данных
• Решайте постепенно усложняющиеся задачи
• Анализируйте запросы опытных специалистов
• Участвуйте в сообществах и обсуждениях

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

Начальный уровень:

-- Вычислите накопительную сумму продаж по месяцам
SELECT 
month, 
sales,
SUM(sales) OVER (ORDER BY month) as running_total
FROM 
monthly_sales;

Средний уровень:

-- Найдите отклонение продаж каждого магазина от среднего по региону
SELECT 
store_id, 
region, 
sales,
AVG(sales) OVER (PARTITION BY region) as region_avg,
sales – AVG(sales) OVER (PARTITION BY region) as deviation
FROM 
store_performance;

Продвинутый уровень:

-- Рассчитайте скользящее среднее за 3 месяца с учетом сезонности
SELECT 
year,
month, 
sales,
AVG(sales) OVER (
PARTITION BY EXTRACT(MONTH FROM date) 
ORDER BY EXTRACT(YEAR FROM date)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as seasonal_moving_avg
FROM 
monthly_sales;

Для перехода на уровень эксперта необходимо не только свободно владеть всеми типами аналитических функций, но и понимать внутренние механизмы их работы, уметь оптимизировать производительность запросов и создавать собственные аналитические решения на их основе.

В 2025 году специалисты по аналитике данных, свободно владеющие аналитическими функциями, остаются одними из самых востребованных на рынке труда. Согласно последним исследованиям, средняя заработная плата таких специалистов на 25-30% выше, чем у аналитиков, ограничивающихся базовым SQL.

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

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