Funções de Janela (Window Functions) em SQL: Casos de Uso Avançados

Se você já trabalha com SQL, provavelmente já se deparou com situações em que precisava analisar dados no contexto de um subconjunto específico de registros. Talvez você tenha escrito subconsultas complexas ou usado soluções alternativas para criar relatórios agregados mantendo os registros detalhados. É aí que as funções de janela entram como um recurso extremamente poderoso que, infelizmente, muitos profissionais não exploram completamente.

Lembro-me claramente de quando comecei a usar funções de janela de forma avançada. Era 2018, e eu estava liderando um projeto de análise para uma empresa de tecnologia que enfrentava um problema específico: identificar clientes cujo padrão de gastos estava mudando significativamente ao longo do tempo, para acionar campanhas de retenção personalizadas. A solução tradicional envolveria várias consultas aninhadas e junções temporárias, mas as funções de janela simplificaram drasticamente essa análise.

O que são funções de janela e por que elas são tão poderosas?

Para quem está começando, as funções de janela (ou window functions) são operações SQL que realizam cálculos em um conjunto de linhas relacionadas à linha atual, sem agrupar as linhas em um único resultado como fazem as funções de agregação comuns.

O diferencial das funções de janela é que elas permitem manter todos os registros originais enquanto adicionam informações agregadas ou calculadas com base em um determinado \"contexto\" ou \"janela\" de dados.

A sintaxe básica segue este padrão:

SELECT
    coluna1,
    coluna2,
    FUNÇÃO_JANELA() OVER (
        PARTITION BY coluna_particao
        ORDER BY coluna_ordenacao
        ROWS/RANGE BETWEEN...
    ) AS nome_coluna_resultado
FROM tabela;

Vamos explorar casos de uso avançados que demonstram o verdadeiro poder dessas funções.

Caso #1: Análise de tendências com médias móveis

Imagine que você está analisando dados de vendas diárias e precisa identificar tendências suavizando flutuações diárias. Uma média móvel de 7 dias é perfeita para isso, e as funções de janela tornam essa implementação elegante:

SELECT
    data_venda,
    valor_venda,
    AVG(valor_venda) OVER (
        ORDER BY data_venda
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS media_movel_7dias
FROM vendas
ORDER BY data_venda;

O que esta consulta faz? Para cada dia, ela calcula a média de vendas incluindo os 6 dias anteriores e o dia atual (formando a janela de 7 dias). O resultado é uma linha para cada dia original, mas com um novo campo mostrando a média móvel.

Em um projeto de varejo que conduzi, usamos esta técnica para identificar períodos de sazonalidade que não eram óbvios nos dados brutos, ajudando o cliente a otimizar seu planejamento de estoque meses antes de datas comemorativas específicas.

Caso #2: Detecção de anomalias utilizando percentis

A detecção de anomalias é um desafio comum em análise de dados. Com funções de janela, podemos criar um sistema robusto para identificar valores atípicos usando estatísticas como percentis:

WITH estatisticas_produto AS (
    SELECT
        produto_id,
        data_venda,
        quantidade_vendida,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY quantidade_vendida) OVER (PARTITION BY produto_id) AS percentil_25,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY quantidade_vendida) OVER (PARTITION BY produto_id) AS percentil_75
    FROM vendas
)
SELECT
    produto_id,
    data_venda,
    quantidade_vendida,
    CASE
        WHEN quantidade_vendida < (percentil_25 - 1.5 * (percentil_75 - percentil_25)) THEN 'Anomalia Baixa'
        WHEN quantidade_vendida > (percentil_75 + 1.5 * (percentil_75 - percentil_25)) THEN 'Anomalia Alta'
        ELSE 'Normal'
    END AS status
FROM estatisticas_produto
WHERE
    quantidade_vendida < (percentil_25 - 1.5 * (percentil_75 - percentil_25)) OR
    quantidade_vendida > (percentil_75 + 1.5 * (percentil_75 - percentil_25))
ORDER BY produto_id, data_venda;

Esta consulta calcula o intervalo interquartil (IQR) para cada produto e identifica valores que estão fora do intervalo normal (usando a regra comum de 1.5 * IQR). O interessante aqui é que cada linha é avaliada no contexto estatístico do seu respectivo produto, sem precisarmos fazer cálculos em ferramentas externas.

Implementei uma abordagem similar para uma empresa de logística que precisava identificar automaticamente picos anormais em devoluções de produtos específicos, o que permitiu acionar rapidamente equipes de controle de qualidade.

Caso #3: Análise de sessão de usuário

O rastreamento de sessões de usuário é fundamental em análises web. Geralmente, consideramos ações separadas por mais de 30 minutos como sessões diferentes. Com funções de janela, podemos identificar e analisar essas sessões elegantemente:

WITH eventos_com_dif_tempo AS (
    SELECT
        usuario_id,
        timestamp_evento,
        tipo_evento,
        EXTRACT(EPOCH FROM (timestamp_evento - LAG(timestamp_evento) OVER(PARTITION BY usuario_id ORDER BY timestamp_evento))) / 60 AS minutos_desde_ultimo_evento
    FROM eventos_usuario
),
eventos_com_sessao AS (
    SELECT
        usuario_id,
        timestamp_evento,
        tipo_evento,
        CASE
            WHEN minutos_desde_ultimo_evento IS NULL OR minutos_desde_ultimo_evento > 30 THEN 1
            ELSE 0
        END AS nova_sessao
    FROM eventos_com_dif_tempo
),
eventos_com_id_sessao AS (
    SELECT
        usuario_id,
        timestamp_evento,
        tipo_evento,
        SUM(nova_sessao) OVER (PARTITION BY usuario_id ORDER BY timestamp_evento ROWS UNBOUNDED PRECEDING) AS sessao_id
    FROM eventos_com_sessao
)
SELECT
    usuario_id,
    sessao_id,
    MIN(timestamp_evento) AS inicio_sessao,
    MAX(timestamp_evento) AS fim_sessao,
    COUNT(*) AS num_eventos,
    STRING_AGG(tipo_evento, ' > ' ORDER BY timestamp_evento) AS caminho_eventos
FROM eventos_com_id_sessao
GROUP BY usuario_id, sessao_id
ORDER BY usuario_id, inicio_sessao;

Esta consulta complexa faz algo incrivelmente útil: quebra os eventos de usuário em sessões distintas (quando há intervalo maior que 30 minutos entre eventos), atribui IDs de sessão e cria um resumo de cada sessão incluindo início, fim, número de eventos e até mesmo o caminho completo de eventos que o usuário seguiu.

Utilizei uma versão adaptada desta análise para uma plataforma educacional, permitindo entender como os estudantes navegavam pelo material de aprendizagem e onde tendiam a abandonar os cursos - insights cruciais para melhorar a experiência de aprendizado.

Caso #4: Cálculo de coortes de retenção

As análises de coorte são fundamentais para entender a retenção de usuários. Vamos criar uma matriz de retenção usando funções de janela:

WITH primeira_compra AS (
    SELECT
        cliente_id,
        MIN(DATE_TRUNC('month', data_compra)) AS mes_primeira_compra
    FROM compras
    GROUP BY cliente_id
),
compras_mensais AS (
    SELECT
        c.cliente_id,
        DATE_TRUNC('month', c.data_compra) AS mes_compra,
        fp.mes_primeira_compra,
        EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', c.data_compra), fp.mes_primeira_compra)) AS mes_retencao
    FROM compras c
    JOIN primeira_compra fp ON c.cliente_id = fp.cliente_id
),
coortes_mensais AS (
    SELECT
        mes_primeira_compra,
        mes_retencao,
        COUNT(DISTINCT cliente_id) AS clientes_ativos
    FROM compras_mensais
    GROUP BY mes_primeira_compra, mes_retencao
),
total_clientes_coorte AS (
    SELECT
        mes_primeira_compra,
        COUNT(DISTINCT cliente_id) AS total_clientes
    FROM primeira_compra
    GROUP BY mes_primeira_compra
)
SELECT
    cm.mes_primeira_compra,
    cm.mes_retencao,
    cm.clientes_ativos,
    tc.total_clientes,
    ROUND((cm.clientes_ativos::DECIMAL / tc.total_clientes) * 100, 1) AS taxa_retencao
FROM coortes_mensais cm
JOIN total_clientes_coorte tc ON cm.mes_primeira_compra = tc.mes_primeira_compra
WHERE cm.mes_retencao <= 6  -- Limitando a 6 meses de retenção
ORDER BY cm.mes_primeira_compra, cm.mes_retencao;

Esta análise cria uma matriz que mostra quantos clientes de cada coorte mensal (agrupados pelo mês da primeira compra) continuaram fazendo compras nos meses subsequentes, oferecendo uma visão clara da retenção ao longo do tempo.

Implementei este tipo de análise para uma startup de SaaS, onde descobrimos que clientes que contratavam durante promoções tinham uma taxa de retenção significativamente menor que aqueles que chegavam através de indicações. Isso levou a uma completa reformulação da estratégia de aquisição.

Caso #5: Identificação de sequências e padrões

Um problema fascinante que resolvi para uma empresa de comércio eletrônico foi identificar clientes que seguiam um padrão específico de compra. Para exemplificar, vamos detectar clientes que compraram produtos em uma sequência específica (A, depois B, depois C):

WITH compras_ordenadas AS (
    SELECT
        cliente_id,
        data_compra,
        produto_id,
        ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data_compra) AS ordem_compra
    FROM compras
),
clientes_padrao AS (
    SELECT
        c1.cliente_id
    FROM compras_ordenadas c1
    JOIN compras_ordenadas c2 ON c1.cliente_id = c2.cliente_id AND c2.ordem_compra = c1.ordem_compra + 1
    JOIN compras_ordenadas c3 ON c2.cliente_id = c3.cliente_id AND c3.ordem_compra = c2.ordem_compra + 1
    WHERE
        c1.produto_id = 'A' AND
        c2.produto_id = 'B' AND
        c3.produto_id = 'C'
)
SELECT
    cp.cliente_id,
    c.nome_cliente,
    c.email
FROM clientes_padrao cp
JOIN clientes c ON cp.cliente_id = c.id;

Esta consulta identifica clientes que seguiram exatamente a sequência de compra A→B→C. O uso da função ROW_NUMBER() cria uma sequência numerada para cada cliente, permitindo então verificar se o padrão específico ocorre.

No caso real, usamos esta técnica para identificar padrões como \"visualizou categoria → adicionou ao carrinho → abandonou → retornou dias depois\", o que ajudou a empresa a criar campanhas de email altamente segmentadas e eficazes.

Desempenho e Considerações

As funções de janela são poderosas, mas é importante considerar o desempenho, especialmente em conjuntos de dados grandes. Aqui estão algumas dicas que aprendi com a experiência:

1. Use índices apropriados nas colunas usadas em PARTITION BY e ORDER BY.
2. Limite o tamanho da janela quando possível (por exemplo, use ROWS BETWEEN 30 PRECEDING AND CURRENT ROW em vez de ROWS UNBOUNDED PRECEDING).
3. Quando possível, materialize resultados intermediários em tabelas temporárias ou CTEs materializadas se seu banco de dados suportar.
4. Teste suas consultas com volumes de dados similares aos de produção - o desempenho pode variar drasticamente.

Em um dos projetos que mencionei, uma consulta com funções de janela reduziu o tempo de processamento de mais de 40 minutos (usando junções e subconsultas múltiplas) para cerca de 3 minutos. A diferença foi tão impressionante que o cliente inicialmente suspeitou que estávamos pulando registros!

Limitações e Diferenças entre Bancos de Dados

É importante notar que a implementação de funções de janela varia entre os sistemas de banco de dados:

• O PostgreSQL tem uma das implementações mais completas e está alinhado com o padrão SQL.
• O MySQL adicionou suporte completo apenas na versão 8.0.
• O SQL Server tem uma implementação robusta, mas com algumas peculiaridades sintáticas.
• O BigQuery da Google tem suporte excelente, o que o torna uma escolha sólida para análises complexas.

Uma das diferenças mais notáveis é o suporte para funções de janela em cláusulas WHERE. No PostgreSQL, você não pode usar diretamente uma função de janela em uma cláusula WHERE, necessitando de uma subconsulta ou CTE.

Conclusão

As funções de janela são uma das ferramentas mais poderosas do SQL moderno, permitindo realizar análises sofisticadas de forma elegante e eficiente. Dominar estas funções eleva suas habilidades de análise de dados a um novo patamar, permitindo resolver problemas complexos diretamente no banco de dados, sem necessidade de ferramentas externas ou processamento em aplicações.

Durante minha carreira, tenho visto como o domínio dessas funções distingue analistas de dados experientes daqueles que estão apenas começando. É uma habilidade que constantemente impressiona em entrevistas técnicas e permite entregar insights de maior valor aos stakeholders.

Se você está interessado em aprofundar seus conhecimentos em SQL, incluindo funções de janela, meu curso de SQL para Análise de Dados cobre extensivamente este tópico, com exemplos práticos e exercícios que ajudarão você a dominar estas técnicas avançadas.

Você já usou funções de janela em seus projetos? Compartilhe suas experiências e dúvidas nos comentários!