Exemplo: cálculo do ticket médio dos pedidos dos últimos 3 meses

Nessa aula, vamos ver um exemplo prático bem legal.

O ticket médio é uma métrica que mostra quanto, em média, um cliente gasta em uma compra. É uma das principais métricas de negócio, que permite:

• analisar mudanças no poder de compra dos clientes,
• identificar tendências de vendas,
• avaliar a efetividade das campanhas de marketing.

Definição do problema

Imagina que a gente tem um banco de dados com a tabela orders, onde ficam os pedidos. Nosso objetivo:

1. Calcular o ticket médio dos pedidos feitos nos últimos três meses.
2. Automatizar esse cálculo usando uma procedure.
3. Salvar o resultado em uma tabela separada pra análise futura.

Expandindo nosso banco de dados: estrutura da tabela orders

Primeiro, vamos garantir que temos uma tabela com os dados necessários. Olha como pode ser a estrutura da tabela orders:

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

• order_id — identificador único do pedido.
• customer_id — cliente que fez o pedido.
• order_date — data em que o pedido foi feito.
• total_amount — valor total do pedido.

Pra facilitar, vamos adicionar alguns registros na tabela pra ter com o que brincar:

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (1, '2023-07-15', 100.00),
    (2, '2023-08-10', 200.50),
    (3, '2023-09-01', 150.75),
    (1, '2023-09-20', 300.00),
    (4, '2023-09-25', 250.00),
    (5, '2023-10-05', 450.00);

Cálculo manual do ticket médio

Antes de automatizar o processo, bora escrever uma query básica que calcula o ticket médio dos últimos 3 meses. Vamos usar a data atual (CURRENT_DATE) e a função AVG() pra calcular a média.

SELECT ROUND(AVG(total_amount), 2) AS avg_check
FROM orders
WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

O que tá rolando aqui:

• AVG(total_amount) — função agregadora que calcula o valor médio de total_amount.
• CURRENT_DATE - INTERVAL '3 months' — pega os pedidos feitos nos últimos três meses.
• ROUND(..., 2) — arredonda o resultado pra duas casas decimais.

O resultado da query vai ser mais ou menos assim:

Automatizando com procedure

Agora, nosso objetivo é criar uma procedure que faz esse cálculo automaticamente e registra o resultado numa tabela separada. Primeiro, vamos criar a tabela pra guardar os logs de analytics.

Criando a tabela log_analytics

CREATE TABLE log_analytics (
    log_id SERIAL PRIMARY KEY,
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(50),
    metric_value NUMERIC(10, 2)
);

• log_date — data e hora do registro.
• metric_name — nome da métrica (no nosso caso "averagecheck_3_months").
• metric_value — valor calculado da métrica.

Criando a procedure

Agora vamos escrever a procedure que:

1. Calcula o ticket médio dos últimos três meses.
2. Salva o resultado na tabela log_analytics.

CREATE OR REPLACE FUNCTION calculate_average_check()
RETURNS VOID AS $$
DECLARE
    avg_check NUMERIC(10, 2);
BEGIN
    -- Passo 1: Calcular o ticket médio
    SELECT ROUND(AVG(total_amount), 2)
    INTO avg_check
    FROM orders
    WHERE order_date >= (CURRENT_DATE - INTERVAL '3 months');

    -- Passo 2: Registrar o resultado
    INSERT INTO log_analytics (metric_name, metric_value)
    VALUES ('average_check_3_months', avg_check);

    -- Print pra debug (opcional)
    RAISE NOTICE 'Ticket médio: %', avg_check;
END;
$$ LANGUAGE plpgsql;

Agora você pode chamar essa função e ela vai registrar o resultado automaticamente na tabela log_analytics:

SELECT calculate_average_check();

Automatizando com agendador de tarefas

Na aula passada a gente já instalou o agendador de tarefas. Se você tá no Linux — era a extensão pg_cron; se usa Windows ou macOS — provavelmente configurou o agendamento pelo sistema (cron ou Task Scheduler). Agora que tá tudo pronto, bora conectar nossa procedure ao agendamento.

Se você tá no Linux e usa pg_cron, garante que a extensão tá ativada no banco certo:

CREATE EXTENSION IF NOT EXISTS pg_cron;

(Lembrando: a instalação do pg_cron e a configuração do parâmetro shared_preload_libraries já foram vistas na aula anterior.)

Agora dá pra agendar a execução da nossa função calculate_average_check() — por exemplo, todo dia à meia-noite:

SELECT cron.schedule(
    'daily_avg_check',
    '0 0 * * *',
    $$ SELECT calculate_average_check(); $$
);

Explicando:

• 'daily_avg_check' — nome da tarefa;
• '0 0 * * *' — expressão cron pra rodar todo dia às 00:00;
• o comando dentro do $$ — SQL que vai ser executado.

Se você tá no Windows ou macOS, o pg_cron não funciona nessas plataformas (no Windows — nem rola, no macOS — só se compilar na mão). Mas você já configurou o agendador do sistema — só falta conectar o arquivo SQL.

1. Crie um arquivo com a query:

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

2. Use o psql pra rodar o arquivo no horário agendado:

   • No Linux/macOS:

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

     (adiciona com crontab -e)
   • No Windows Task Scheduler:
     • Coloca o caminho do psql.exe.
     • Nos argumentos:
       -U postgres -d your_database -f "C:\path\to\script.sql"

Assim, independente do seu sistema, a procedure vai rodar automaticamente e registrar o ticket médio na tabela log_analytics de forma regular. Se não lembra qual método tá usando, volta na aula anterior — lá tem a instalação e configuração do agendador pra cada plataforma.

Verificando e analisando os resultados

Bora ver como ficou. Consulta os dados da tabela log_analytics:

SELECT * FROM log_analytics ORDER BY log_date DESC;

Exemplo de resultado:

Agora a gente tem o log de todos os cálculos do ticket médio! Esses dados podem ser usados pra gerar relatórios ou analisar como a métrica muda com o tempo.

Erros comuns e como evitar

Trabalhar com procedures analíticas pra calcular ticket médio pode trazer alguns erros clássicos.

Um deles é esquecer de tratar resultados vazios. Se não teve pedido nos últimos três meses, a função AVG() vai retornar NULL, o que pode dar ruim na hora de registrar o log. Pra evitar isso, usa o COALESCE():

SELECT ROUND(COALESCE(AVG(total_amount), 0), 2) AS avg_check

Outro erro é ter dados errados na tabela orders. Tipo, valores negativos de pedido ou datas inválidas. Recomendo sempre conferir os dados ou colocar restrições no banco (tipo CHECK (total_amount > 0)).

Parabéns, agora você tem uma procedure completa que calcula automaticamente o ticket médio dos últimos três meses e salva o resultado pra análise futura. Esse é só um dos muitos exemplos de como o PostgreSQL e o PL/pgSQL podem ajudar a automatizar tarefas analíticas. Na próxima aula, vamos ver cenários analíticos mais avançados. Até lá!