Теория: Агрегация с помощью SUM и COUNT

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

Накопленный итог SUM

Оконная функция SUM() может использоваться для подсчета накопительного итога. Каждая запись будет содержать накопленный итог и за предыдущий период

SELECT
    s.id,
    s.sale_date,
    s.quantity,
    s.price,
    (s.quantity * s.price) AS amount,
    SUM(s.quantity * s.price) OVER (
        PARTITION BY s.sale_date
        ORDER BY
            s.id,
            s.sale_date,
            s.quantity
    ) AS amount_total
FROM
    sales AS s
ORDER BY s.sale_date;

View on DB Fiddle

Накопительный итог COUNT

Рассмотрим запрос с оконной функцией COUNT() для подсчета общего количества записей для каждой даты продажи (sale_date) в таблице sales.

SELECT
    s.id,
    s.sale_date,
    quantity,
    COUNT(s.id) OVER (
        PARTITION BY s.sale_date
        ORDER BY
        s.sale_date,
        quantity
    ) AS total
FROM
    sales AS s
WHERE sale_date = '2023-01-01'
ORDER BY s.sale_date;

View on DB Fiddle

Давай разберем, как работает этот запрос:

1. Сначала мы выбираем столбцы id, sale_date и quantity из таблицы sales.
2. Мы также добавляем выражение COUNT(s.id) OVER(), чтобы посчитать общее количество записей для каждой даты продажи.
3. PARTITION BY s.sale_date указывает, что мы хотим разделить данные на разные группы по sale_date.
4. ORDER BY s.sale_date, quantity указывает порядок сортировки внутри каждой группы.
5. Затем мы фильтруем только записи, где sale_date равен '2023-01-01'.
6. И, наконец, мы сортируем результат по sale_date.

Результат запроса показывает, что для даты продажи '2023-01-01' у нас есть следующие записи:

• id 1, quantity 1, total 3
• id 3, quantity 1, total 3
• id 4, quantity 1, total 3
• id 2, quantity 2, total 4

Результат 3 в столбце total появляется несколько раз, потому что этот запрос использует функцию оконного агрегирования COUNT() с PARTITION BY по дате продажи. Это означает, что подсчет количества выполнится отдельно для каждой уникальной даты продажи.

Поскольку для всех строк с датой продажи '2023-01-01' используется одинаковое значение COUNT(s.id) (а именно 3), то это значение будет повторяться для каждой строки с этой датой, пока не изменится дата продажи. Когда появляется строка с датой продажи '2023-01-01' и другим количеством продаж, то COUNT(s.id) меняется на новое значение 4.

Результирующая таблица отображает итоговое количество продаж по каждой уникальной дате продажи, и поэтому для '2023-01-01' мы видим как 3, так и 4 в столбце total.

Поиск точки безубыточности

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

SELECT * FROM purchases;

View on DB Fiddle

Напишем запрос с поиском накопляемой суммы.

SELECT
    *,
    sum(purchase_amount)
        OVER (PARTITION BY product_name ORDER BY purchase_date, purchase_amount)
FROM purchases;

View on DB Fiddle

Для товара Product A точка безубыточности была достигнута четвертой продажей 2022-01-04. Но чтобы найти точку безубыточности для других товаров считать вручную дату неудобно. Воспользуемся фильтрацией и подзапросом.

WITH tab AS (
    SELECT
        *,
        SUM(purchase_amount)
            OVER (
                PARTITION BY product_name
                ORDER BY purchase_date, purchase_amount
            )
        AS amount_sum
    FROM purchases
)

SELECT
    product_name,
    purchase_date,
    amount_sum
FROM tab
WHERE amount_sum >= 1500;

View on DB Fiddle

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

WITH tab AS (
    SELECT
        *,
        SUM(purchase_amount)
            OVER (
                PARTITION BY product_name
                ORDER BY purchase_date, purchase_amount
            )
        AS amount_sum
    FROM purchases
),

rn_tab AS (
    SELECT
        product_name,
        purchase_date,
        amount_sum,
        ROW_NUMBER() OVER (PARTITION BY product_name ORDER BY amount_sum) AS rn
    FROM tab
    WHERE amount_sum >= 1500
)

SELECT
    product_name,
    purchase_date,
    amount_sum
FROM rn_tab
WHERE rn = 1;

View on DB Fiddle

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

Выводы

Агрегирующие оконные функции в SQL позволяют выполнять агрегатные операции над группами строк в пределах определенного окна. Оконные функции могут использоваться для вычисления накопительных сумм, средних значений, лидирующих значений и других агрегатов. Для определения окна используются ключевые слова PARTITION BY и ORDER BY, которые определяют границы окна.

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