Продвинутый PostgreSQL: аналитика и автоматизация

1. Продвинутая агрегация и группировка данных

Продвинутая агрегация и группировка данных

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

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

Точечная агрегация с помощью FILTER

Часто возникает задача посчитать агрегированные метрики (сумму, количество, среднее) не для всех строк в группе, а только для тех, которые соответствуют определенному условию.

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

Новички обычно решают эту задачу через конструкцию CASE WHEN:

Этот подход работает, но он избыточен и трудно читается при большом количестве условий. В PostgreSQL существует элегантное решение — модификатор FILTER.

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

Перепишем наш запрос с использованием нового синтаксиса:

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

Многоуровневые итоги: ROLLUP

В бизнес-отчетах редко нужна просто плоская таблица с данными. Руководители хотят видеть подытоги (subtotals) по категориям и общий итог (grand total) в самом конце.

Допустим, у нас есть данные о продажах по регионам и городам. Если использовать обычный GROUP BY region, city, мы получим продажи для каждой пары "регион-город". Но как добавить строку с суммой по всему региону и строку с суммой по всей стране?

Без продвинутых функций пришлось бы писать три отдельных запроса и объединять их через UNION ALL. Оператор ROLLUP делает это автоматически, создавая иерархическую группировку.

Результат этого запроса будет содержать три уровня детализации:

Выручка по каждому конкретному городу (например, "Сибирь", "Новосибирск", 5000).

Выручка по региону в целом ("Сибирь", NULL, 12000).

Общая выручка по всем данным (NULL, NULL, 45000).

!Иерархия агрегации данных через ROLLUP

Оператор ROLLUP всегда «сворачивает» данные справа налево. Если вы указали ROLLUP(A, B, C), база данных посчитает итоги для (A, B, C), затем для (A, B), затем для (A) и, наконец, общий итог ().

Все возможные комбинации: CUBE

Если ROLLUP строит строгую иерархию, то оператор CUBE генерирует итоги по всем возможным комбинациям столбцов. Это незаменимый инструмент для создания кросс-таблиц (сводных таблиц), где аналитику нужно смотреть на данные под любым углом.

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

Возьмем продажи по продуктам и каналам привлечения (онлайн/офлайн):

Этот запрос вернет:

Продажи конкретного продукта в конкретном канале.

Итоговые продажи каждого продукта (во всех каналах).

Итоговые продажи по каждому каналу (для всех продуктов).

Общий итог.

Идентификация итогов: функция GROUPING

При использовании ROLLUP и CUBE в результирующей таблице появляются значения NULL. Они означают «Все значения» для данного столбца. Но возникает проблема: что если в самих исходных данных есть реальный NULL? Например, город не был указан при оформлении заказа.

Как отличить NULL, означающий «Итог по всем городам», от NULL, означающего «Город неизвестен»? Для этого применяется функция GROUPING().

Она возвращает 1, если значение NULL было сгенерировано оператором ROLLUP/CUBE (то есть это итог), и 0, если это обычные данные.

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

Агрегация в массивы и JSON

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

PostgreSQL позволяет агрегировать строки не только в числа, но и в структуры данных — массивы и JSON-объекты. Для этого используются функции array_agg и json_agg.

Допустим, нам нужно получить список пользователей, где для каждого пользователя будет массив всех его покупок:

Что здесь происходит?

Мы группируем данные по пользователю.

Функция json_build_object собирает данные каждого заказа в JSON-объект.

Функция json_agg берет все эти объекты для конкретного пользователя и складывает их в единый JSON-массив.

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

Освоение этих паттернов переводит вас из категории людей, которые «умеют писать селекты», в инженеров данных, способных решать архитектурные задачи и оптимизировать бизнес-процессы на самом глубоком уровне.

2. Оконные функции для бизнес-аналитики

Оконные функции для бизнес-аналитики

В прошлой статье мы разобрали продвинутые методы агрегации данных с помощью ROLLUP и CUBE. Эти инструменты отлично подходят для создания итоговых отчетов и сводных таблиц. Однако у классической группировки через GROUP BY есть одно существенное ограничение: она неизбежно «схлопывает» исходные строки. Если вы сгруппировали продажи по месяцам, вы больше не сможете в том же запросе увидеть детали конкретной транзакции.

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

Для решения таких задач в PostgreSQL применяются оконные функции (window functions). Это мощный аналитический инструмент, который позволяет выполнять вычисления над набором строк, связанных с текущей строкой, не объединяя их в одну.

Анатомия оконной функции

Главный маркер использования оконной функции в SQL-запросе — это ключевое слово OVER. Именно оно говорит базе данных, что агрегатную функцию нужно применить не ко всему сгруппированному результату, а к определенному «окну» данных.

Базовый синтаксис выглядит так:

Если оставить скобки пустыми OVER (), функция будет применена ко всем строкам, которые вернул запрос (после фильтрации WHERE).

Рассмотрим простейший пример. У нас есть таблица продаж sales. Мы хотим вывести каждую транзакцию и рядом — общую сумму всех продаж в базе.

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

!Сравнение классической группировки и оконных функций

Сегментация данных: PARTITION BY

Чаще всего нам нужна не общая сумма по всей базе, а итоги по определенным категориям. Для этого внутри конструкции OVER используется оператор PARTITION BY. Он разбивает данные на независимые группы (партиции), и оконная функция вычисляется отдельно внутри каждой группы.

Представьте задачу: нужно проанализировать продажи менеджеров и понять, какой вклад каждый из них вносит в выручку своего отдела.

Без оконных функций пришлось бы писать запрос с группировкой по отделу, сохранять его во временную таблицу или подзапрос, а затем соединять (JOIN) с исходной таблицей менеджеров. С оконными функциями это делается в один проход:

Что здесь происходит?

База данных берет строку конкретного сотрудника.

Смотрит на его department (например, «IT-продажи»).

Находит все остальные строки с таким же отделом.

Суммирует их amount и записывает результат в колонку dept_total_sales.

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

| employee_name | department | employee_sales | dept_total_sales | contribution_percent | | :--- | :--- | :--- | :--- | :--- | | Иванов | B2B | 5000 | 15000 | 33.33 | | Петров | B2B | 10000 | 15000 | 66.67 | | Смирнова | Retail | 2000 | 2000 | 100.00 |

Ранжирование и топы: ORDER BY

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

В PostgreSQL есть три основные функции для ранжирования:

ROW_NUMBER() — присваивает уникальный порядковый номер каждой строке (1, 2, 3, 4).

RANK() — присваивает ранг. Если значения равны, они получают одинаковый ранг, но следующий номер пропускается (1, 1, 3, 4).

DENSE_RANK() — работает как RANK, но не пропускает номера (1, 1, 2, 3).

Классическая бизнес-задача: найти топ-3 самых продаваемых товаров в каждой категории.

> Оконные функции вычисляются после оператора WHERE. Поэтому мы не можем написать WHERE DENSE_RANK() <= 3 в одном запросе. Для фильтрации по результату оконной функции всегда используется обобщенное табличное выражение (CTE) с помощью ключевого слова WITH или вложенный подзапрос.

Анализ временных рядов: LAG и LEAD

Одна из самых частых задач продуктового или финансового аналитика — сравнение показателей с предыдущим периодом. Например, расчет MoM (Month-over-Month) — изменения выручки текущего месяца к предыдущему.

Для обращения к другим строкам относительно текущей используются функции смещения:

LAG(столбец, смещение) — берет значение из предыдущих строк.

LEAD(столбец, смещение) — берет значение из следующих строк.

Посчитаем динамику выручки по месяцам:

В этом примере мы сначала агрегируем данные по месяцам (обычный GROUP BY), а затем с помощью LAG «подтягиваем» выручку прошлого месяца в текущую строку.

Формула расчета темпа прироста стандартна:

Где — значение текущего периода, а — значение предыдущего периода. Если в январе мы заработали 100 долл., а в феврале 120 долл., то рост составит 20%.

Скользящие окна (Фреймы)

Иногда окно нужно ограничить не просто категорией (PARTITION BY), а динамическим диапазоном строк. Это называется фреймом (window frame). Фреймы незаменимы для расчета скользящих средних (moving average), которые сглаживают краткосрочные колебания данных и показывают реальный тренд.

Синтаксис фрейма добавляется после ORDER BY и обычно использует ключевое слово ROWS BETWEEN.

Рассчитаем скользящую среднюю выручку за последние 7 дней:

Конструкция ROWS BETWEEN 6 PRECEDING AND CURRENT ROW говорит базе данных: «Для расчета среднего значения возьми текущую строку и ровно 6 строк перед ней». По мере движения запроса сверху вниз, эта «рамка» скользит вместе с текущей строкой.

Другие полезные границы фреймов:

UNBOUNDED PRECEDING — от самого начала партиции.

UNBOUNDED FOLLOWING — до самого конца партиции.

1 FOLLOWING — одна строка после текущей.

Например, чтобы посчитать нарастающий итог (running total) с начала года до текущего дня, используется фрейм от начала до текущей строки:

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