Сравнение оконных функций с агрегатными функциями: GROUP BY vs PARTITION BY

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

Агрегатные функции (GROUP BY)

Агрегатные функции работают следующим образом:

• Они группируют строки по указанным столбцам.
• После группировки каждая группа превращается в одну строку результата.
• Пример: вы хотите узнать суммарный доход по каждому региону.

SELECT region, SUM(sales) AS total_sales
FROM sales_data
GROUP BY region;

Особенность: GROUP BY "сжимает" данные. Если вы используете группировку, то все строки, входящие в одну группу, исчезают — остаётся только результат агрегации.

Оконные функции (PARTITION BY)

Оконные функции, напротив:

• Соблюдают изначальную структуру данных (никаких сжатий или исчезновений строк!).
• Могут выполнять вычисления внутри "окон" — логически обособленных групп строк.

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

SELECT
    region,
    city,
    sales,
    SUM(sales) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales_data;

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

Пример: SUM() с GROUP BY vs SUM() с PARTITION BY

Чтобы лучше понять разницу, давайте рассмотрим, как SUM() работает в обоих случаях. Представим, что у нас есть таблица sales_data следующего вида:

Суммирование с помощью GROUP BY

Мы хотим узнать общий объём продаж по каждому региону:

SELECT region, SUM(sales) AS total_sales
FROM sales_data
GROUP BY region;

Результат будет выглядеть так:

Что произошло: строки сгруппировались по region, и каждая группа была "сжата" в одну строку с суммой продаж.

Суммирование с помощью PARTITION BY

Теперь сделаем то же самое с оконной функцией:

SELECT
    region,
    city,
    sales,
    SUM(sales) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales_data;

Результат:

Что произошло: PARTITION BY не "сжал" строки. Вместо этого он выполнил расчёт суммы внутри определённых окон (каждый регион — это отдельное окно).

Когда использовать GROUP BY и когда — PARTITION BY?

GROUP BY: подходит для финальных отчётов

GROUP BY полезен, когда вы хотите сократить объём данных и получить итоговые результаты на уровне групп. Например:

• Суммарные продажи по месяцам.
• Подсчёт количества заказов по категориям товаров.

Пример:

SELECT category, COUNT(*) AS total_orders
FROM orders
GROUP BY category;

PARTITION BY: идеален для анализа и детализации

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

• Определить долю продаж каждого товара в категории.
• Нумерация строк внутри каждой группы.

Пример расчёта доли продаж:

SELECT
    category,
    product,
    sales,
    ROUND(
        (sales * 100.0) / SUM(sales) OVER (PARTITION BY category),
        2
    ) AS sales_percentage
FROM sales_data;

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

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

SELECT
    region,
    city,
    sales,
    SUM(sales) OVER (PARTITION BY region) AS total_sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank
FROM sales_data;

Результат:

Преимущества оконных функций над GROUP BY

Сохранение оригинальных данных: GROUP BY "сжимает" строки, а оконные функции позволяют сохранить оригинальную структуру таблицы.

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

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

Пример гибкости

Попробуем объединить несколько функций:

SELECT
    region,
    city,
    sales,
    SUM(sales) OVER (PARTITION BY region) AS total_sales,
    AVG(sales) OVER (PARTITION BY region) AS avg_sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank
FROM sales_data;

Результат:

Ограничения и типичные ошибки

Одной из распространённых ошибок является попытка использовать PARTITION BY, когда требуется "сжать" данные. Например, вместо:

SELECT region, SUM(sales) AS total_sales
FROM sales_data
GROUP BY region;

Некоторые пытаются написать так:

SELECT
    region,
    SUM(sales) OVER (PARTITION BY region) AS total_sales
FROM sales_data;

Однако это вернёт все строки, не сократив объём данных (что не всегда то, что нужно).

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

2

Задача

SQL SELF, 30 уровень, 0 лекция

Недоступна

Сумма продаж по регионам с сохранением всех строк (`PARTITION BY`)

Сумма продаж по регионам с сохранением всех строк (`PARTITION BY`)

Открыть