Побудова аналітичних звітів з PL/pgSQL

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

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

Уяви собі, що ти — шеф-кухар, який керує величезним рестораном. Щоб зрозуміти, які страви замовляють найчастіше, тобі потрібен звіт. PostgreSQL у цьому випадку — це твоя база даних рецептів і замовлень, а PL/pgSQL (процедури) — твій помічник на кухні, який автоматизує процес аналізу замовлень.

Основи побудови аналітичних звітів

Аналітичний звіт — це інструмент для агрегації, фільтрації, сортування та впорядкування даних з метою отримання корисної інформації. Зазвичай структура звіту включає такі етапи:

1. Підготовка даних: вибірка інформації з таблиць, фільтрація та попередня обробка.
2. Агрегація даних: розрахунок метрик (середній чек, загальна сума продажів тощо).
3. Форматування: впорядкування даних у зручному для сприйняття вигляді.
4. Вивід результатів: представлення звіту користувачам або запис у таблицю для зберігання.

Кожен із цих етапів можна реалізувати за допомогою процедур PL/pgSQL.

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

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

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

Наша задача: створити звіт про суму продажів за вказаний місяць. Тобто, ми хочемо бачити:

• Місяць.
• Загальну суму продажів за цей місяць.

Структура процедури

Ось план нашої процедурки (не лякайся, програмування на PL/pgSQL не кусається):

1. Приймаємо вхідний параметр — місяць.
2. Вибираємо дані за цей місяць із таблиці orders.
3. Рахуємо загальну суму продажів.
4. Повертаємо результат.

Реалізація процедури

Приклад коду:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- Вибираємо дані за вказаний місяць і агрегуємо їх
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY 1;
END;
$$ LANGUAGE plpgsql;

1. Вхідний параметр: p_month — дата. Ми будемо використовувати його, щоб фільтрувати дані по місяцю.
2. RETURN QUERY: це магічна штука, яка дозволяє повернути дані прямо з процедури.
3. DATE_TRUNC: використовується для округлення order_date до початку місяця.
4. SUM: агрегатна функція для підрахунку суми всіх замовлень.
5. GROUP BY: групуємо дані по місяцю, бо звіти будуються по місяцях.

Тепер ми можемо викликати нашу функцію:

SELECT * FROM monthly_sales_report('2023-08-01');

І отримаємо щось типу:

Ця функція — база. Давай ускладнимо!

Створення більш складного звіту

Тепер уяви, що ми хочемо розбити продажі по клієнтах. Тобто, наш звіт має відображати:

• Клієнта
• Місяць
• Суму замовлень цього клієнта за місяць

Змінюємо процедуру

CREATE OR REPLACE FUNCTION customer_monthly_report(p_month DATE)
RETURNS TABLE (
    customer_id INT,
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.customer_id,
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(o.total_amount) AS total_sales
    FROM orders o
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', p_month)
    GROUP BY o.customer_id, DATE_TRUNC('month', o.order_date);
END;
$$ LANGUAGE plpgsql;

Тепер виклик процедури:

SELECT * FROM customer_monthly_report('2023-08-01');

І результат може бути таким:

Використовуємо тимчасові таблиці

Іноді при створенні складних звітів буває корисно використовувати тимчасові таблиці. Наприклад, якщо треба обробити проміжні дані.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- Створюємо тимчасову таблицю
    CREATE TEMP TABLE temp_sales AS
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', p_month)
    GROUP BY customer_id, DATE_TRUNC('month', order_date);

    -- Робимо додаткові розрахунки або маніпуляції з цією таблицею
    -- Наприклад, виводимо топ-3 клієнтів за сумою замовлень
    RAISE NOTICE 'Топ-3 клієнтів за місяць %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Клієнт: %, Сума: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

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

Корисні поради

1. Оптимізація: використовуй індекси, щоб пришвидшити вибірки даних.
2. Помилки з діленням на нуль: завжди перевіряй дільник, щоб не "завалити" звіт.
3. Форматування дати: використовуй функції типу TO_CHAR для зручного виводу.

Сподіваюсь, ти не дуже заснув! Попереду ще складніші й цікавіші задачі, тож не розслабляйся!

1

Опитування

Процедури для аналітики, рівень 59, лекція 4

Недоступний

Процедури для аналітики

Процедури для аналітики

Відкрити