Розрахунок накопичувальних сум з використанням віконних функцій: SUM(), AVG()

Давай уявимо: ти слідкуєш за доходами своєї компанії, продажами в інтернет-магазині або навіть просто аналізуєш свої витрати за рік. Тобі потрібно не тільки бачити доходи чи витрати за кожен місяць, а й розуміти, як вони накопичуються від місяця до місяця.

Звичайні агрегатні функції (GROUP BY) тут не допоможуть — вони згрупують дані і повернуть один рядок на групу. А що, якщо ми хочемо бачити кожен місяць і при цьому рахувати накопичувальну суму? Ось тут і виручає SUM() у зв'язці з віконними функціями.

Основи використання віконних функцій для накопичувальних сум

Віконні функції дозволяють виконувати агрегатні операції по віконних рамках. Завдяки цьому ми можемо, наприклад, складати значення в кожному рядку, але без видалення інших рядків. Більше ніяких жертв заради GROUP BY!

Синтаксис SUM() з віконною функцією

Ось базовий шаблон для використання накопичувальної суми:

SELECT
    column_name,
    SUM(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS cumulative_sum
FROM 
    table_name;

Тут:

• SUM(column_name) — підсумовує значення.
• OVER() — задає вікно для розрахунку.
• PARTITION BY — ділить дані на групи (опціонально).
• ORDER BY — задає порядок рядків всередині вікна.

Приклад: накопичувальний дохід по місяцях

Уявімо таблицю твоїх доходів:

Ми хочемо побачити дохід за кожен місяць і накопичувальний підсумок. Спробуємо написати SQL-запит:

SELECT
    місяць,
    дохід,
    SUM(дохід) OVER (ORDER BY місяць) AS накопичувальний_дохід
FROM 
    доходи;

Результат:

Що відбувається:

1. ORDER BY місяць всередині OVER() каже PostgreSQL, що рядки треба враховувати у хронологічному порядку.
2. Для кожного рядка сума рахується з урахуванням усіх попередніх рядків (і поточного).

Подумай уважно над тим, що тут відбувається. Для першого рядка SUM() рахує суму тільки 1-го рядка, для другого — суму двох рядків, для третього — суму трьох рядків. Саме тому важливий порядок сортування місяців!

Приклад: накопичувальний дохід по регіонах

Якщо у тебе була б таблиця продажів по регіонах, частинами вона могла б виглядати так:

Тепер ми хочемо рахувати накопичувальний дохід окремо для кожного регіону:

SELECT
    регіон,
    місяць,
    дохід,
    SUM(дохід) OVER (PARTITION BY регіон ORDER BY місяць) AS накопичувальний_дохід
FROM 
    продажі;

Результат буде таким:

Тепер кожен регіон аналізується окремо (PARTITION BY регіон), але всередині регіону рядки впорядковуються по часу (ORDER BY місяць).

Ковзаюче середнє (AVG())

Окей, накопичувальні суми — це круто, але що, якщо ти хочеш аналізувати тренди, скажімо, за останні 3 місяці? Для цього підійде ковзаюче середнє.

Приклад: ковзаюче середнє доходів

Ми знову працюємо з таблицею доходи, і ось її дані:

Запит для розрахунку 3-місячного ковзаючого середнього:

SELECT
    місяць,
    дохід,
    AVG(дохід) OVER (
        ORDER BY місяць 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ковзаюче_середнє
FROM 
    доходи;

Результат:

Пояснення:

1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW каже PostgreSQL дивитись на поточний рядок і два попередні рядки для розрахунку середнього.
2. В результаті для кожного місяця ми бачимо середній дохід за останні 3 місяці.

Тобто для кожного рядка ми задаємо вікно у 3 рядки: поточний і два попередніх. А потім по ним рахуємо середнє. Дуже зручно.

Робота ORDER BY і його вплив

Віконні функції залежать від правильного порядку рядків. Якщо порядок вказаний неправильно (або взагалі не вказаний), результати можуть бути неочікуваними.

Приклад: помилки через відсутність ORDER BY

Якщо ми приберемо ORDER BY з OVER(), то замість накопичувальної суми отримаємо суму всіх доходів для кожного рядка:

SELECT
    місяць,
    дохід,
    SUM(дохід) OVER () AS неправильна_накопичувальна_сума
FROM 
    доходи;

Результат:

Рядки не впорядковані, і замість накопичувальної суми функція просто застосовує підсумовування для всіх рядків без розбору.

Реальні кейси використання

Аналіз доходів:

• Накопичувальні суми дозволяють відслідковувати, як ростуть продажі чи доходи компанії.
• Ковзаюче середнє допомагає побачити "чистий" тренд без шумів.

Фінансове моделювання:

Банки та фінансові компанії використовують віконні функції для аналізу виплат, зростання заборгованостей та інших метрик.

Побудова часових рядів:

Часові дані, такі як кількість користувачів онлайн, перегляди сторінок, виручка і т.п., ідеально аналізуються з SUM() та AVG().