Построение аналитических отчетов с 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 для удобного вывода.

Надеюсь, вам не пришлось слишком много зевать! Впереди еще более сложные и интересные задачи, так что не расслабляйтесь!

2

Задача

SQL SELF, 59 уровень, 4 лекция

Недоступна

Поиск топ-3 самых продаваемых продуктов за месяц

Поиск топ-3 самых продаваемых продуктов за месяц

Открыть

1

Опрос

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

Недоступен

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

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

Открыть