Логування аналітичних даних в окремі таблиці

Уяви, ти будуєш звіт про продажі за тиждень. Розрахунки зроблені, клієнти задоволені. Але через місяць тебе питають: "А можеш показати, що було в тому звіті?" Якщо ти заздалегідь не зберіг дані, доведеться або відновлювати їх вручну, або відповідати "не можу". Це не тільки незручно, а й може вплинути на твою репутацію.

Логування аналітичних даних вирішує кілька важливих задач:

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

Основна ідея: таблиця log_analytics

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

Приклад структури таблиці

У таблиці log_analytics будемо зберігати дані про звіти. Ось структура (DDL — Data Definition Language):

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY, -- Унікальний ідентифікатор запису
    report_name TEXT NOT NULL, -- Назва звіту або метрики
    report_date DATE DEFAULT CURRENT_DATE, -- Дата, до якої відноситься звіт
    category TEXT, -- Категорія даних (наприклад, регіон, продукт)
    metric_value NUMERIC NOT NULL, -- Значення метрики
    created_at TIMESTAMP DEFAULT NOW() -- Дата і час логування
);

• log_id: основний ідентифікатор запису.
• report_name: назва звіту або метрики, наприклад, "Weekly Sales".
• report_date: дата, до якої відноситься метрика. Наприклад, якщо це продажі за 1 жовтня, то тут буде 2023-10-01.
• category: допомагає групувати дані, наприклад, по регіонах.
• metric_value: числове значення для звітної метрики.
• created_at: часова мітка логування.

Приклад запису даних у log_analytics

Припустимо, ми порахували дохід за жовтень по регіону "Північ". Як зберегти це значення?

INSERT INTO log_analytics (report_name, report_date, category, metric_value)
VALUES ('Monthly Revenue', '2023-10-01', 'North', 15000.75);

Результат:

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

Звісно, ми не можемо записувати дані вручну кожного тижня чи місяця. Тому автоматизуємо процес за допомогою процедури.

Давай створимо просту процедуру для логування даних про дохід:

CREATE OR REPLACE FUNCTION log_monthly_revenue(category TEXT, revenue NUMERIC)
RETURNS VOID AS $$
BEGIN
    INSERT INTO log_analytics (report_name, report_date, category, metric_value)
    VALUES ('Monthly Revenue', CURRENT_DATE, category, revenue);
END;
$$ LANGUAGE plpgsql;

Тепер процедура log_monthly_revenue приймає два параметри:

• category: категорія даних, наприклад, регіон.
• revenue: значення доходу

Ось як викликати цю функцію для запису доходу:

SELECT log_monthly_revenue('North', 15000.75);

Результат буде таким же, як і при вставці через INSERT.

Додаткові ідеї для структури логів

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

Оновимо структуру таблиці:

CREATE TABLE log_analytics_extended (
    log_id SERIAL PRIMARY KEY,
    report_name TEXT NOT NULL,
    report_date DATE DEFAULT CURRENT_DATE,
    category TEXT,
    metric_values JSONB NOT NULL, -- Зберігання метрик у форматі JSONB
    created_at TIMESTAMP DEFAULT NOW()
);

Тут важливе нововведення — використання типу JSONB для зберігання кількох метрик в одному полі.

Приклад запису в розширену таблицю

Припустимо, треба зберегти одразу три метрики: дохід, кількість замовлень і середній чек. Ось приклад запиту:

INSERT INTO log_analytics_extended (report_name, category, metric_values)
VALUES (
    'Monthly Revenue',
    'North',
    '{"revenue": 15000.75, "orders": 45, "avg_check": 333.35}'::jsonb
);

Результат:

Приклади використання логів: аналіз доходів

Припустимо, ми хочемо дізнатися загальний дохід по всіх регіонах за жовтень. Ось запит:

SELECT SUM((metric_values->>'revenue')::NUMERIC) AS total_revenue
FROM log_analytics_extended
WHERE report_date BETWEEN '2023-10-01' AND '2023-10-31';

Приклади використання логів: тренди по регіонах

Аналізуємо зміни доходу по регіонах:

SELECT category, report_date, (metric_values->>'revenue')::NUMERIC AS revenue
FROM log_analytics_extended
ORDER BY category, report_date;

Обробка типових помилок

Під час логування аналітичних даних можна допустити кілька помилок. Давай обговоримо їх і способи їх уникнення.

• Помилка: забули вказати категорію або дату. Рекомендується задавати значення за замовчуванням у таблиці, наприклад DEFAULT CURRENT_DATE.
• Помилка: дублювання записів. Щоб уникнути дублювання, можна додати унікальний індекс:

  CREATE UNIQUE INDEX unique_log_entry
  ON log_analytics (report_name, report_date, category);
  

• Помилка: розрахунок метрик з діленням на нуль. Завжди перевіряй дільник! Використовуй NULLIF:

  SELECT revenue / NULLIF(order_count, 0) AS avg_check FROM orders;
  

Застосування в реальних проектах

Логування аналітичних даних корисне в різних сферах:

• Рітейл: відстеження доходів і продажів по категоріях товарів.
• Сервіси: аналіз завантаження серверів чи додатків.
• Фінанси: контроль транзакцій і витрат.

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