GROUP BY, HAVING e agregações que resolvem 80% dos casos

-- Funções de agregação básicas
SELECT
    COUNT(*)                   AS total_pedidos,       -- conta linhas (inclui NULL)
    COUNT(DISTINCT cliente_id) AS clientes_unicos,     -- conta valores distintos
    SUM(total)                 AS receita_total,
    AVG(total)                 AS ticket_medio,
    MIN(total)                 AS menor_pedido,
    MAX(total)                 AS maior_pedido,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS mediana,
    STDDEV(total)              AS desvio_padrao
FROM pedidos
WHERE status = 'concluido';

-- GROUP BY: agrupa linhas e aplica agregação por grupo
SELECT
    DATE_TRUNC('month', criado_em) AS mes,
    status,
    COUNT(*) AS num_pedidos,
    SUM(total) AS receita
FROM pedidos
GROUP BY DATE_TRUNC('month', criado_em), status
ORDER BY mes DESC, status;

-- Regra do GROUP BY: SELECT pode ter apenas
-- ✅ Colunas do GROUP BY
-- ✅ Funções de agregação
-- ❌ Outras colunas (erro: "must appear in GROUP BY clause")

-- HAVING: filtra após agregação
SELECT
    cliente_id,
    COUNT(*) AS num_pedidos,
    SUM(total) AS total_gasto
FROM pedidos
WHERE criado_em >= '2024-01-01'         -- filtra ANTES de agrupar
GROUP BY cliente_id
HAVING SUM(total) > 1000                -- filtra DEPOIS de agrupar
ORDER BY total_gasto DESC
LIMIT 20;

-- Relatório de pedidos por status — SEM FILTER (3 subqueries ou pivô manual)
SELECT
    COUNT(*) FILTER (WHERE status = 'pendente')   AS pendentes,
    COUNT(*) FILTER (WHERE status = 'processando') AS processando,
    COUNT(*) FILTER (WHERE status = 'concluido')  AS concluidos,
    COUNT(*) FILTER (WHERE status = 'cancelado')  AS cancelados,
    SUM(total) FILTER (WHERE status = 'concluido') AS receita_realizada,
    SUM(total) FILTER (WHERE status = 'pendente')  AS receita_potencial
FROM pedidos
WHERE criado_em >= NOW() - INTERVAL '30 days';

-- Alternativa menos legível (CASE WHEN dentro de agregação):
SELECT
    COUNT(CASE WHEN status = 'pendente' THEN 1 END) AS pendentes,
    SUM(CASE WHEN status = 'concluido' THEN total ELSE 0 END) AS receita
FROM pedidos;

-- Análise de cohort: primeiros 30 dias vs depois
SELECT
    DATE_TRUNC('month', primeiro_pedido.data) AS cohort,
    COUNT(DISTINCT p.cliente_id) AS total_clientes,
    COUNT(DISTINCT p.cliente_id) FILTER (
        WHERE p.criado_em <= primeiro_pedido.data + INTERVAL '30 days'
    ) AS compraram_no_primeiro_mes
FROM pedidos p
JOIN (
    SELECT cliente_id, MIN(criado_em) AS data FROM pedidos GROUP BY cliente_id
) primeiro_pedido USING (cliente_id)
GROUP BY DATE_TRUNC('month', primeiro_pedido.data)
ORDER BY cohort;

-- ROLLUP: subtotais hierárquicos
SELECT
    COALESCE(DATE_TRUNC('year', criado_em)::TEXT, 'TOTAL') AS ano,
    COALESCE(DATE_TRUNC('month', criado_em)::TEXT, 'SUBTOTAL') AS mes,
    COUNT(*) AS pedidos,
    SUM(total) AS receita
FROM pedidos
GROUP BY ROLLUP(
    DATE_TRUNC('year', criado_em),
    DATE_TRUNC('month', criado_em)
)
ORDER BY ano NULLS LAST, mes NULLS LAST;

-- Resultado:
-- 2024-01-01 | 2024-01-01 | 150 | 45000   ← janeiro 2024
-- 2024-01-01 | 2024-02-01 | 130 | 39000   ← fevereiro 2024
-- 2024-01-01 | NULL       | 280 | 84000   ← subtotal 2024 (ROLLUP!)
-- NULL       | NULL       | 280 | 84000   ← grande total (ROLLUP!)

-- GROUPING SETS: especifica exatamente os agrupamentos que quer
SELECT
    produto,
    regiao,
    SUM(vendas) AS total
FROM fatos_vendas
GROUP BY GROUPING SETS (
    (produto, regiao),   -- por produto + região
    (produto),           -- só por produto
    (regiao),            -- só por região
    ()                   -- grande total
);

-- DISTINCT ON: primeiro registro de cada grupo (PostgreSQL específico)
SELECT DISTINCT ON (cliente_id)
    cliente_id,
    criado_em,
    total
FROM pedidos
ORDER BY cliente_id, criado_em DESC;   -- pega o pedido mais recente por cliente

-- Agregações de texto (string_agg é muito útil)
SELECT
    pedido_id,
    STRING_AGG(produto, ', ' ORDER BY produto) AS produtos_lista
FROM itens_pedido
GROUP BY pedido_id;