Construindo relatórios analíticos com PL/pgSQL

Relatórios analíticos são jeitos organizados de mostrar dados que ajudam a tomar decisões. Por exemplo:

• Os gerentes querem ver qual foi o faturamento do mês passado.
• Os analistas procuram tendências no mercado.
• Os devs monitoram a performance do app.

Imagina que você é um chef que comanda um restaurante gigante. Pra sacar quais pratos são mais pedidos, você precisa de um relatório. O PostgreSQL aqui é seu banco de dados de receitas e pedidos, e o PL/pgSQL (procedures) é tipo seu assistente na cozinha, automatizando a análise dos pedidos.

O básico de construir relatórios analíticos

Um relatório analítico é uma ferramenta pra agregar, filtrar, ordenar e organizar dados pra tirar informação útil. Normalmente, a estrutura do relatório tem essas etapas:

1. Preparação dos dados: pegar info das tabelas, filtrar e dar aquele trato inicial.
2. Agregação dos dados: calcular métricas (ticket médio, total de vendas, etc).
3. Formatação: deixar os dados num formato fácil de entender.
4. Exibir resultados: mostrar o relatório pra galera ou salvar numa tabela pra guardar.

Cada uma dessas etapas pode ser feita usando procedures em PL/pgSQL.

Criando uma procedure pra relatório analítico

Bora ver um exemplo básico de como criar um relatório analítico. Suponha que temos uma tabela orders onde ficam os dados dos pedidos:

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

Nosso objetivo: criar um relatório do total de vendas de um mês específico. Ou seja, queremos ver:

• Mês.
• Total de vendas desse mês.

Estrutura da procedure

Olha o plano da nossa procedure (relaxa, programar em PL/pgSQL não morde):

1. Recebe um parâmetro de entrada — o mês.
2. Pega os dados desse mês na tabela orders.
3. Calcula o total de vendas.
4. Retorna o resultado.

Implementação da procedure

Exemplo de código:

CREATE OR REPLACE FUNCTION monthly_sales_report(p_month DATE)
RETURNS TABLE (
    month DATE,
    total_sales NUMERIC(10, 2)
) AS $$
BEGIN
    -- Pegando os dados do mês informado e agregando
    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. Parâmetro de entrada: p_month — data. Vamos usar ele pra filtrar os dados pelo mês.
2. RETURN QUERY: essa parada mágica que devolve os dados direto da procedure.
3. DATE_TRUNC: serve pra arredondar o order_date pro começo do mês.
4. SUM: função agregadora pra somar todos os pedidos.
5. GROUP BY: agrupa os dados por mês, já que o relatório é mensal.

Agora dá pra chamar nossa função assim:

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

E vai sair algo tipo:

Essa função é só a base. Bora deixar mais interessante!

Criando um relatório mais avançado

Agora imagina que queremos separar as vendas por cliente. Ou seja, nosso relatório tem que mostrar:

• Cliente
• Mês
• Total de pedidos desse cliente no mês

Vamos mudar a procedure

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;

Agora chamando a procedure:

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

E o resultado pode ser assim:

Usando tabelas temporárias

Às vezes, pra relatórios mais complexos, é massa usar tabelas temporárias. Tipo, quando precisa tratar dados intermediários.

CREATE OR REPLACE FUNCTION temp_table_example(p_month DATE)
RETURNS VOID AS $$
BEGIN
    -- Criando tabela temporária
    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);

    -- Fazendo cálculos ou manobras extras com essa tabela
    -- Por exemplo, mostrando o top 3 clientes por valor de pedidos
    RAISE NOTICE 'Top 3 clientes do mês %:', p_month;
    FOR record IN
        SELECT customer_id, total_sales
        FROM temp_sales
        ORDER BY total_sales DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Cliente: %, Valor: %', record.customer_id, record.total_sales;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Nesse caso, a tabela temporária temp_sales serve pra guardar resultados intermediários.

Dicas úteis

1. Otimização: usa índices pra deixar as consultas mais rápidas.
2. Erros de divisão por zero: sempre confere o divisor pra não "quebrar" o relatório.
3. Formatação de data: usa funções tipo TO_CHAR pra deixar o resultado mais amigável.

Espero que você não tenha bocejado demais! Vem mais coisa cabulosa e divertida pela frente, então fica ligado!

2

Tarefa

SQL SELF, nível 59, lição 4

Bloqueado

Busca dos 3 produtos mais vendidos do mês

Busca dos 3 produtos mais vendidos do mês

Abrir

1

Pesquisa/teste

Procedures para Analytics, nível 59, lição 4

Indisponível

Procedures para Analytics

Procedures para Analytics

Abrir