Оконные функции в PostgreSQL: практическое руководство по анализу товарного ассортимента

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

В этой статье мы на примере таблицы товаров интернет-магазина разберём четыре практических сценария использования оконных функций в PostgreSQL.

Наши исходные данные

Представьте таблицу товаров интернет-магазина с полями:

• id — идентификатор

• product_name — название товара

• category — категория

• price — цена

• description — описание

В ассортименте есть смартфоны, ноутбуки, одежда, книги и другие категории. Именно на этих данных мы будем учиться применять оконные функции.

Пример 1. Базовая статистика по категориям

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

Запрос:

SELECT 
    category,
    product_name,
    price,
    ROUND(AVG(price) OVER (PARTITION BY category), 2) AS avg_price_in_category,
    MIN(price) OVER (PARTITION BY category) AS min_price_in_category,
    MAX(price) OVER (PARTITION BY category) AS max_price_in_category,
    ROUND(price - AVG(price) OVER (PARTITION BY category), 2) AS deviation_from_avg
FROM products
ORDER BY category, price DESC;

Как это работает:

• OVER — ключевое слово, указывающее, что функция будет оконной

• PARTITION BY category — разделяет данные на "окна" по категориям (отдельно для смартфонов, ноутбуков, одежды)

• Внутри каждого окна считаются среднее, минимум, максимум

• Отклонение считается как разница между ценой товара и средней ценой по категории

Что мы видим в результате:

Инсайты:

• iPhone 15 Pro дороже среднего по категории на 24 540₽

• Xiaomi, наоборот, значительно дешевле среднего

• Видно позиционирование товаров внутри категории

Важное отличие от GROUP BY: мы не потеряли детализацию — каждая строка осталась на месте, но обогатилась статистикой по группе.

Пример 2. Ранжирование товаров

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

Запрос:

SELECT 
    category,
    product_name,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank,
    NTILE(4) OVER (PARTITION BY category ORDER BY price DESC) AS price_quartile
FROM products
ORDER BY category, price DESC;

Сравнение функций ранжирования:

Результат для категории "Ноутбуки":

Практическое применение NTILE:

• NTILE(2) — разделение на "дорогие" и "дешёвые"

• NTILE(4) — квартили (25% самых дорогих, следующие 25% и т.д.)

• NTILE(10) — децили для детального анализа

Пример 3. Доля товара в общей стоимости категории

Следующий полезный показатель — какой процент от общей стоимости категории составляет каждый товар.

Запрос:

SELECT 
    category,
    product_name,
    price,
    ROUND(100.0 * price / SUM(price) OVER (PARTITION BY category), 2) AS percent_of_category_total,
    SUM(price) OVER (PARTITION BY category) AS category_total
FROM products
ORDER BY category, percent_of_category_total DESC;

Результат для категории "Смартфоны":

Бизнес-вывод: iPhone занимает 45% всей стоимости смартфонов — это сигнал для закупщиков о критической зависимости от одной позиции.

Пример 4. Иерархический анализ (два уровня категорий)

В реальном бизнесе категории редко бывают плоскими. Обычно есть родительские категории и подкатегории. Рассмотрим анализ с учётом иерархии.

Для начала создадим иерархическую структуру категорий с помощью CTE (Common Table Expression):

WITH RECURSIVE category_tree AS (
    -- Базовая выборка: корневые категории
    SELECT 
        id,
        name,
        parent_id,
        name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Рекурсивная часть: дочерние категории
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ct.path || ' -> ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

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

WITH product_analysis AS (
    SELECT 
        p.category_id,
        pc.name AS child_category,
        pp.name AS parent_category,
        p.product_name,
        p.price,
        -- Статистика по дочерней категории
        ROUND(AVG(p.price) OVER (PARTITION BY p.category_id), 2) AS avg_in_child,
        ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS rank_in_child,
        -- Статистика по родительской категории
        ROUND(AVG(p.price) OVER (PARTITION BY pp.id), 2) AS avg_in_parent,
        ROW_NUMBER() OVER (PARTITION BY pp.id ORDER BY p.price DESC) AS rank_in_parent
    FROM products p
    JOIN categories pc ON p.category_id = pc.id
    JOIN categories pp ON pc.parent_id = pp.id
)
SELECT * FROM product_analysis
ORDER BY parent_category, child_category, price DESC;

Результат для категории "Одежда":

Инсайты:

• Джинсы в мужской категории занимают 2 место по цене

• В родительской категории "Одежда" — только 5 место (есть более дорогие женские товары)

• Средняя цена в мужской одежде (5 820) ниже, чем в женской (6 450)

Такой анализ позволяет понять позиционирование товаров не только в узкой категории, но и в более широком контексте.

Пример 5. Дашборд в одной таблице

Соберём все возможные метрики в один универсальный запрос — готовый отчёт для бизнес-пользователей.

WITH product_analysis AS (
    SELECT 
        category,
        product_name,
        price,
        -- Количество товаров в категории
        COUNT(*) OVER (PARTITION BY category) AS products_in_category,
        -- Общая стоимость категории
        SUM(price) OVER (PARTITION BY category) AS category_total_value,
        -- Средняя цена по категории
        ROUND(AVG(price) OVER (PARTITION BY category), 2) AS avg_category_price,
        -- Ранг товара по цене (от самого дорогого)
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank_desc,
        -- Ранг товара по цене (от самого дешёвого)
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price ASC) AS price_rank_asc,
        -- Квартиль по цене
        NTILE(4) OVER (PARTITION BY category ORDER BY price DESC) AS price_quartile,
        -- Отклонение от средней цены
        ROUND(price - AVG(price) OVER (PARTITION BY category), 2) AS deviation_from_avg,
        -- Доля в стоимости категории
        ROUND(100.0 * price / SUM(price) OVER (PARTITION BY category), 2) AS percent_of_category,
        -- Накопительный процент стоимости (от самых дорогих к дешёвым)
        ROUND(100.0 * SUM(price) OVER (PARTITION BY category ORDER BY price DESC) / 
              SUM(price) OVER (PARTITION BY category), 2) AS cumulative_percent
    FROM products
)
SELECT 
    category,
    product_name,
    price,
    products_in_category,
    category_total_value,
    avg_category_price,
    CASE 
        WHEN price_rank_desc = 1 THEN 'Самый дорогой'
        WHEN price_rank_desc <= products_in_category * 0.1 THEN 'Топ-10% дорогих'
        WHEN price_quartile = 1 THEN 'Дорогой сегмент'
        WHEN price_quartile = 4 THEN 'Бюджетный сегмент'
        WHEN price_rank_asc = 1 THEN 'Самый дешёвый'
        ELSE 'Средний сегмент'
    END AS price_segment,
    deviation_from_avg,
    percent_of_category || '%' AS category_share,
    cumulative_percent || '%' AS cumulative_share
FROM product_analysis
ORDER BY category, price DESC;

Результат для категории "Смартфоны":

Ключевой инсайт: топ-2 товара (iPhone и Samsung) дают 77.7% всей стоимости категории — классический принцип Парето (80/20) в действии.

Сравнение GROUP BY и оконных функций

Практические рекомендации

1. Всегда используйте PARTITION BY для осмысленной группировки, иначе расчёт пойдёт по всей таблице

2. Комбинируйте оконные функции в одном запросе — это эффективнее, чем делать отдельные запросы

3. Используйте CTE (WITH) для сложных расчётов — код становится читаемее

4. Проверяйте производительность на больших данных — создавайте индексы на полях, участвующих в PARTITION BY и ORDER BY

5. Экспериментируйте с разными функциями — RANK, DENSE_RANK, NTILE дают разные перспективы на данные

Заключение

Оконные функции в PostgreSQL — это инструмент, который превращает SQL из простого языка запросов в полноценный аналитический инструмент. Мы рассмотрели:

✅ Базовую статистику по категориям с PARTITION BY
✅ Различные способы ранжирования (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
✅ Расчёт долей и процентов
✅ Иерархический анализ с двумя уровнями группировки
✅ Комплексный дашборд в одном запросе

Освоив эти приёмы, вы сможете отвечать на сложные бизнес-вопросы без выгрузки данных в Excel и без написания сложных программ.

Попробуйте применить эти запросы к своим данным. Возьмите любую таблицу с категориями и числовыми показателями — и вы откроете для себя много нового о своём бизнесе.

Хотите увидеть все эти примеры в действии — с пошаговым выполнением, разбором синтаксиса и демонстрацией реальных результатов? В нашем видео-руководстве мы:

• Подробно разбираем каждый из пяти примеров

• Показываем, как меняются результаты при использовании разных функций

• Демонстрируем работу с иерархическими данными

• Объясняем, как оптимизировать запросы с оконными функциями

Статья подготовлена по материалам обучающего видео для вашего удобства.