Автоматична генерація звітів за розкладом

Коли ти працюєш з невеликими базами даних, нічого страшного немає в тому, щоб вручну запускати запити чи процедури для побудови звітів. Але в реальному світі бази даних виростають до таких розмірів, що будь-які повторювані задачі треба автоматизувати. Уяви, що тебе щодня просять підготувати звіт про продажі. Навіть якщо запит до бази займає дві хвилини, за рік ти витратиш понад 12 годин на це. Краще б ці години провести з кавою, поки автоматична процедура все зробить сама.

Автоматизація допоможе:

• Зменшити ручну працю.
• Забезпечити регулярність звітності (наприклад, щоденні, щотижневі звіти).
• Мінімізувати ймовірність помилок через людський фактор.
• Підвищити довіру до твоїх звітів: вони завжди створюються за заданими параметрами.

Основні кроки автоматичної генерації звітів

Автоматичне виконання звітів включає такі етапи:

1. Створення процедури в PL/pgSQL, яка генерує звіт.
2. Налаштування логування результатів (якщо потрібно).
3. Використання планувальника задач, щоб запускати процедуру за розкладом.

Давай крок за кроком реалізуємо це!

Створення процедури для генерації звіту

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

CREATE TABLE sales_report_log (
    report_date DATE NOT NULL,
    total_sales NUMERIC NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Тепер створимо процедуру на мові PL/pgSQL:

CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
    -- Порахувати загальну виручку за поточний день
    INSERT INTO sales_report_log (report_date, total_sales)
    SELECT CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE;

    RAISE NOTICE 'Звіт за % успішно створено', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

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

• Ми використовуємо агрегатну функцію SUM() для підрахунку загальної виручки з таблиці orders.
• Дата звіту (report_date) завжди дорівнює поточній даті (CURRENT_DATE).
• Результат зберігається у таблицю sales_report_log.
• Повідомлення RAISE NOTICE додано для відлагодження: воно каже, що звіт успішно створено.

Тестування процедури

Перед тим як автоматизувати виконання цієї процедури, завжди корисно протестувати її вручну. Виконаємо функцію:

SELECT generate_daily_sales_report();

А тепер перевіримо вміст таблиці sales_report_log:

SELECT * FROM sales_report_log;

Якщо ти бачиш рядок з поточною датою і правильним значенням загальної виручки — вітаю, твоя функція працює!

Автоматизація задач у PostgreSQL

Іноді корисно, щоб база даних щось робила сама: запускала звіти, чистила старі записи чи оновлювала агрегати за розкладом. PostgreSQL дозволяє це реалізувати за допомогою розширення pg_cron або зовнішнього планувальника задач — системного cron чи Task Scheduler.

Якщо ти працюєш у Linux, найкращий вибір — pg_cron. Це розширення запускає SQL прямо всередині PostgreSQL, без необхідності звертатися до shell чи скриптів.

Встановити pg_cron можна так (не забудь замінити XX на свою версію PostgreSQL):

sudo apt install postgresql-XX-cron

Після встановлення треба підключити його в конфігурації. Відкрий postgresql.conf і додай рядок:

shared_preload_libraries = 'pg_cron'

Потім перезапусти PostgreSQL і активуй розширення у своїй базі даних:

CREATE EXTENSION pg_cron;

Тепер можна запланувати задачу. Наприклад, запуск функції generate_daily_sales_report() щодня опівночі:

SELECT cron.schedule(
    'daily_sales_report',
    '0 0 * * *',
    $$ SELECT generate_daily_sales_report(); $$
);

Тут:

• 'daily_sales_report' — ім'я задачі;
• '0 0 * * *' — розклад у стилі cron (у цьому випадку — щодня о 00:00);
• SQL між $$ — код, який буде виконуватись.

Щоб подивитись усі заплановані задачі, використовуй:

SELECT * FROM cron.job;

Якщо ти використовуєш Windows або macOS, то pg_cron або взагалі не підтримується (у Windows), або вимагає ручної збірки з вихідних кодів (у macOS). Це незручно, і в більшості випадків простіше використовувати системний планувальник.

Ось як це зробити:

1. Створи SQL-файл з потрібною командою:

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

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

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

3. Додай запуск цієї команди у планувальник задач:

   • У Linux/macOS: через crontab -e:

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

   • У Windows: через Task Scheduler, створивши задачу, яка запускає psql.exe з потрібними параметрами.

• Якщо ти на Linux, використовуй pg_cron — він зручний і вбудований у PostgreSQL.
• Якщо ти на Windows чи Mac, розумніше покладатися на системний планувальник (cron або Task Scheduler) і запускати SQL через psql.

Так ти зможеш автоматизувати будь-які задачі у PostgreSQL без зайвих зусиль.

Приклади автоматичної звітності

1. Щоденний звіт по регіонах

Припустимо, ти хочеш автоматично створювати звіт по виручці в кожному регіоні. Можеш розширити нашу функцію:

CREATE OR REPLACE FUNCTION generate_regional_sales_report()
RETURNS VOID AS $$
BEGIN
    INSERT INTO regional_sales_report_log (region, report_date, total_sales)
    SELECT region, CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE
    GROUP BY region;

    RAISE NOTICE 'Регіональний звіт за % успішно створено', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

2. Щомісячний звіт

Схожим чином можна створити процедуру для генерації звіту за місяць. Просто зміни фільтр у запиті:

WHERE order_date BETWEEN date_trunc('month', CURRENT_DATE)
                     AND date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day';

Типові помилки та їх уникнення

Під час автоматичної генерації звітів можуть виникати проблеми:

• Помилка синтаксису у функції: завжди тестуй функції вручну перед автоматизацією.
• Частота виконання задач: якщо задача запускається занадто часто, це може перевантажити базу. Налаштовуй розклад з розумом.
• Дублювання даних: якщо звіт запускається кілька разів на день, можливі дублікати. Використовуй унікальні ключі для уникнення повторів.

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