SQL Academy · Aula

Escrevendo Consultas Analíticas

Segmente, detalhe e agregue métricas.

Escrevendo Consultas Analíticas é uma aula grátis de SQL Academy no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de SQL Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de SQL Academy inclui 4 aulas no total.

O que são consultas analíticas?

As consultas analíticas vão além de simples buscas de linhas. Em vez de perguntar qual pedido o cliente 42 fez?, elas perguntam qual é a receita total por região e trimestre? ou como este mês se compara ao mês passado?

Em um armazém de dados construído com um esquema estrela, as consultas analíticas fatiam (filtram uma dimensão), recortam (filtram várias dimensões) e consolidam (agregam em uma granularidade mais ampla) os fatos para revelar informações úteis para o negócio.

Revisão do esquema estrela

Um esquema estrela tem uma tabela de fatos central (por exemplo, fact_sales) cercada por tabelas de dimensão (por exemplo, dim_date, dim_product, dim_store). As consultas analíticas fazem junções entre a tabela de fatos e as dimensões necessárias para a análise atual.

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

Fatiamento: filtrando uma dimensão

Fatiamento significa restringir o conjunto de resultados a um único valor de uma dimensão — por exemplo, analisar somente os dados do ano de 2024. A cláusula WHERE é sua ferramenta de fatiamento.

Ao fazer o fatiamento logo no início, você reduz o número de linhas que o banco de dados precisa agregar, mantendo as consultas rápidas em tabelas de fatos grandes.

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Recorte: filtrando várias dimensões

Recorte significa aplicar filtros a duas ou mais dimensões ao mesmo tempo — por exemplo, analisar as vendas de eletrônicos na região Norte durante o T1. Cada condição WHERE adicional recorta um cubo de dados menor.

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

Consolidação: agregando em uma granularidade superior

Consolidação significa passar de uma granularidade detalhada (vendas diárias por loja) para uma granularidade mais ampla (vendas mensais por região). Para isso, remova as colunas de GROUP BY de nível inferior e faça uma nova agregação.

O modificador ROLLUP permite produzir subtotais e totais gerais em uma única consulta, em vez de escrever vários blocos UNION ALL.

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date  d ON d.date_id  = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

Comparações entre períodos com LAG

Um dos padrões analíticos mais comuns é comparar uma métrica com a mesma métrica em um período anterior. A função de janela LAG() permite trazer o valor da linha anterior diretamente para a linha atual, sem uma junção da tabela consigo mesma.

Aqui, calculamos o crescimento da receita mês a mês como uma porcentagem.

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

Totais acumulados com SUM OVER

Um total acumulado (soma cumulativa) adiciona o valor de cada linha ao total de todas as linhas anteriores em uma ordem definida. Isso é ideal para acompanhar a receita acumulada ao longo de um ano ou monitorar a redução progressiva de um orçamento.

A cláusula de moldura ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW torna a janela explícita e sem ambiguidades.

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Classificação de dimensões com DENSE_RANK

A classificação permite encontrar os melhores ou piores resultados dentro de um grupo. DENSE_RANK() atribui classificações consecutivas sem lacunas quando há empates, o que a torna a opção preferida para quadros de classificação em relatórios de BI.

Envolver o resultado classificado em uma CTE e filtrar pela classificação torna o padrão dos N primeiros limpo e legível.

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

Percentual de contribuição com SUM em janela

Saber a receita absoluta de um produto é útil, mas saber que ele contribui com 38 % da receita da categoria é mais prático para a tomada de decisões. Um SUM() em janela sobre toda a partição fornece o denominador sem uma junção com uma subconsulta.

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

Médias móveis para suavizar tendências

Os valores de vendas diários ou semanais são ruidosos. Uma média móvel suaviza as flutuações de curto prazo para que você consiga perceber a tendência subjacente. Aqui, uma média móvel de 3 meses é calculada usando uma moldura de janela deslizante.

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

CUBE para todas as combinações de dimensões

CUBE amplia ROLLUP ao calcular subtotais para todas as combinações possíveis das dimensões listadas, não apenas para o caminho hierárquico de consolidação. Isso produz o resumo completo entre dimensões em uma única passagem — útil para painéis multidimensionais nos quais os usuários podem alterar livremente a perspectiva.

NULL em uma coluna de agrupamento significa todos os valores daquela dimensão — use GROUPING() para distinguir NULLs intencionais nos dados de NULLs gerados pela consolidação.

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

Qual operação restringe os resultados a um único valor de dimensão?

Teste seus conhecimentos sobre a terminologia de consultas analíticas usada no armazenamento de dados.

Recapitulação: escrevendo consultas analíticas

Nesta lição, você explorou os principais padrões para escrever consultas analíticas em um esquema estrela:

Fatiamento — filtre uma dimensão com WHERE para se concentrar em um segmento específico.
Recorte — filtre várias dimensões simultaneamente para delimitar um cubo de dados preciso.
Consolidação — agregue em uma granularidade mais ampla; use ROLLUP ou CUBE para subtotais em vários níveis.
LAG / LEAD — comparações entre períodos sem junções da tabela consigo mesma.
Totais acumulados & médias móveis — métricas cumulativas e suavizadas por meio de molduras de janela.
DENSE_RANK — classificações limpas dos N primeiros dentro de partições.
Percentual de contribuição — SUM em janela como denominador para calcular participações.

A combinação desses padrões abrange a grande maioria dos requisitos de BI e de relatórios que você encontrará em armazéns de dados de produção.

Perguntas Frequentes

A aula “Escrevendo Consultas Analíticas” é grátis?

Sim — o texto completo de “Escrevendo Consultas Analíticas” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de SQL Academy, atualize para CoddyKit PRO. O curso de SQL Academy inclui 4 aulas no total.

O que vou aprender em “Escrevendo Consultas Analíticas”?

Segmente, detalhe e agregue métricas. Você pratica SQL Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar SQL Academy?

Nenhuma experiência prévia é necessária. SQL Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.

Quanto tempo leva a aula “Escrevendo Consultas Analíticas”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de SQL Academy?

Sim. Cada aula de SQL Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

← Voltar para SQL Academy