Пример: расчет среднего чека по заказам за последние 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 могут помочь автоматизировать аналитические задачи. В следующей лекции мы продолжим разбирать более сложные аналитические сценарии. До встречи!