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

У цій лекції розглянемо цікавий практичний приклад.

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

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

Постановка задачі

Уяви, що у нас є база даних з таблицею orders, де зберігаються замовлення. Наша ціль:

1. Порахувати середній чек для замовлень, зроблених за останні три місяці.
2. Автоматизувати цей розрахунок за допомогою процедури.
3. Зберегти результат в окрему таблицю для подальшого аналізу.

Розширюємо нашу базу даних: структура таблиці orders

Для початку давай переконаємось, що у нас є таблиця з потрібними даними. Ось як може виглядати структура таблиці orders:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

• order_id — унікальний ідентифікатор замовлення.
• customer_id — клієнт, який зробив замовлення.
• order_date — дата, коли було оформлено замовлення.
• total_amount — загальна сума замовлення.

Для прикладу додамо кілька записів у таблицю, щоб було з чим працювати:

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

Розрахунок середнього чека вручну

Перш ніж автоматизувати процес, давай напишемо базовий запит, який порахує середній чек за останні 3 місяці. Ми будемо використовувати поточну дату (CURRENT_DATE) і функцію AVG() для розрахунку середнього.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

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

• AVG(total_amount) — агрегатна функція, яка рахує середнє значення total_amount.
• CURRENT_DATE - INTERVAL '3 months' — визначає замовлення, зроблені за останні три місяці.
• ROUND(..., 2) — округлює результат до двох знаків після коми.

Результат запиту буде виглядати приблизно так:

Автоматизація за допомогою процедури

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

Створення таблиці log_analytics

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — дата і час запису.
• metric_name — назва метрики (у нашому випадку "averagecheck_3_months").
• metric_value — пораховане значення метрики.

Створення процедури

Тепер напишемо процедуру, яка:

1. Рахує середній чек за останні три місяці.
2. Зберігає результат у таблицю log_analytics.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- Крок 1: Розрахунок середнього чека
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- Крок 2: Логування результату
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Вивід інформації для відладки (опціонально)
    RAISE NOTICE 'Середній чек: %', avg_check;
END;
$$ LANGUAGE plpgsql;

Тепер ти можеш викликати цю функцію, і вона автоматично запише результат у таблицю log_analytics:

SELECT calculate_average_check();

Автоматизація за допомогою планувальника задач

У минулій лекції ми вже встановили планувальник задач. Якщо ти працюєш у Linux — це було розширення pg_cron; якщо використовуєш Windows або macOS — ти, ймовірно, налаштував запуск через системний планувальник (cron або Task Scheduler). Тепер, коли все готово, давай підключимо нашу процедуру до розкладу.

Якщо ти на Linux і використовуєш pg_cron переконайся, що це розширення активовано в потрібній базі даних:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Нагадуємо: встановлення самого pg_cron і налаштування параметра shared_preload_libraries вже були розглянуті на попередньому занятті.)

Тепер можна запланувати виконання нашої функції calculate_average_check() — наприклад, кожного дня опівночі:

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

Пояснення:

• 'daily_avg_check' — ім'я задачі;
• '0 0 * * *' — cron-вираз для запуску о 00:00 щодня;
• команда всередині $$ — SQL, який буде виконано.

Якщо ти на Windows або macOS, pg_cron у цих системах не працює (у Windows — зовсім, у macOS — вимагає ручної збірки). Але ти вже налаштував системний планувальник — залишилось тільки підключити SQL-файл.

1. Створи файл із запитом:

   echo "SELECT calculate_average_check();" > /path/to/script.sql
   

2. Використовуй psql для виконання файлу за розкладом:

   • У Linux/macOS:

       0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

     (додається через crontab -e)
   • У Windows Task Scheduler:
     • Вкажи шлях до psql.exe.
     • В аргументах:
       -U postgres -d your_database -f "C:\path\to\script.sql"

Таким чином, незалежно від твоєї системи, процедура буде виконуватись автоматично і регулярно фіксувати середній чек у таблиці log_analytics. Якщо ти не впевнений, який спосіб використовуєш, повернись до попередньої лекції — там описані і встановлення, і налаштування планувальника для різних платформ.

Перевірка і аналіз результатів

Подивимось, що у нас вийшло. Запросимо дані з таблиці log_analytics:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Приклад результату:

Тепер у нас є лог усіх розрахунків середнього чека! Ці дані можна використовувати для генерації звітів або аналізу змін метрики з часом.

Часті помилки і як їх уникнути

Робота з аналітичними процедурами по розрахунку середнього чека може бути пов'язана з кількома типовими помилками.

Одна з них — забути врахувати порожні результати. Якщо за останні три місяці не було замовлень, функція AVG() поверне NULL, що може призвести до проблем при логуванні. Щоб уникнути цього, можна використати COALESCE():

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

Ще одна помилка — некоректні дані в таблиці orders. Наприклад, від'ємні суми замовлень або невалідні дати. Рекомендується регулярно перевіряти дані або додавати обмеження на рівні бази даних (наприклад, CHECK (total_amount > 0)).

Вітаю, тепер у тебе є повноцінна процедура, яка автоматично рахує середній чек за останні три місяці і зберігає результат для подальшого аналізу. Це лише один із багатьох прикладів того, як PostgreSQL і PL/pgSQL можуть допомогти автоматизувати аналітичні задачі. У наступній лекції ми продовжимо розбирати більш складні аналітичні сценарії. До зустрічі!