Transformação com agregação condicional

Uma das tarefas mais comuns em entrevistas sobre relatórios é: transformar linhas em colunas. Você tem uma tabela longa como sales(region, quarter, amount), e o entrevistador quer um relatório amplo com uma coluna por trimestre.

A resposta portátil e independente de dialeto que eles esperam ouvir é a agregação condicional: uma expressão CASE colocada dentro de uma função agregadora, como SUM. Domine esse padrão e você poderá fazer uma tabela dinâmica em qualquer banco de dados, até mesmo naqueles que não têm a palavra-chave PIVOT.

Antes de criar a tabela dinâmica, nomeie os formatos. O formato longo armazena um fato por linha: cada par região/trimestre ocupa sua própria linha. O formato amplo distribui uma categoria entre colunas.

• Longo: fácil de inserir, difícil de ler lado a lado.
• Amplo: excelente para um relatório voltado a pessoas.

Uma tabela dinâmica transforma o formato longo em amplo. Os entrevistadores gostam disso porque testa se você entende agregação, e não apenas sintaxe.

O truque é: para cada coluna de saída, escreva um CASE que retorne o valor quando a linha corresponder àquela coluna e NULL caso contrário. Envolva-o em uma função agregadora para que o agrupamento seja reduzido a uma linha por chave.

Leia assim: some o valor, mas somente para as linhas do primeiro trimestre. Como SUM ignora NULL, as linhas que não correspondem não contribuem com nada.

Esse padrão funciona por causa de um fato que os entrevistadores investigarão: as funções agregadoras ignoram NULL. Um CASE sem ELSE retorna NULL quando nenhuma ramificação corresponde, portanto SUM(CASE WHEN ... THEN amount END) soma somente as linhas selecionadas.

Se você escrevesse ELSE 0, também funcionaria com SUM (adicionar zero não altera nada), mas quebraria AVG, MIN e COUNT.

Aqui está a consulta completa sobre os dados de exemplo. Cada região se torna uma linha; cada trimestre, uma coluna.

O GROUP BY region é o que reduz as quatro linhas de entrada a duas linhas de saída. Sem ele, você obteria uma linha por linha de entrada, com principalmente valores NULL.

A agregação que envolve o CASE deve corresponder à pergunta:

• SUM quando cada célula totaliza valores.
• MAX ou MIN quando cada par região/trimestre tem exatamente um valor e você só quer exibi-lo.
• COUNT quando cada célula conta linhas correspondentes.

Os entrevistadores costumam perguntar a variante com COUNT: quantos pedidos há por status e por mês?

Quando cada par chave/categoria contém um único valor (uma verdadeira tabela cruzada, não um total), use MAX ou MIN. Ambos retornam o único valor não NULL e ignoram os valores NULL das ramificações sem correspondência.

Essa é a escolha segura quando você está remodelando atributos em vez de somar valores monetários, por exemplo, transformando uma tabela de configurações de chave/valor em uma linha por entidade.

Se uma região não teve vendas no 2º trimestre, sua célula q2 resulta em NULL. Os entrevistadores podem pedir que você mostre 0 no lugar. Envolva toda a agregação em COALESCE.

Coloque COALESCE fora da agregação, não dentro do CASE, para substituir o valor somente quando o grupo inteiro não tiver linhas correspondentes.

Uma pergunta complementar comum é adicionar um total de todas as colunas da tabela dinâmica. Não é necessário somar as colunas pelo nome. Um SUM(amount) simples sobre o mesmo grupo fornece o total da linha, pois ignora completamente a filtragem do CASE.

Isso mostra ao entrevistador que você entende que cada agregação no SELECT é calculada independentemente sobre o mesmo grupo.

PostgreSQL e o padrão SQL oferecem FILTER (WHERE ...), uma forma mais limpa de escrever a agregação condicional. A leitura fica melhor e evita o código repetitivo do CASE.

Mencione isso em uma entrevista para demonstrar conhecimento amplo, mas saiba que MySQL e SQL Server não oferecem suporte a esse recurso, portanto CASE continua sendo a resposta portável.

Há uma ressalva sobre a agregação condicional que os entrevistadores insistirão em abordar: você precisa listar manualmente todas as colunas de saída. Se os trimestres ou as categorias não forem conhecidos antecipadamente, essa consulta estática não poderá se adaptar.

Esse problema é chamado de operação PIVOT dinâmica e requer SQL gerado. Para um conjunto fixo e conhecido de categorias, porém, a agregação condicional é a opção mais limpa e portável.

Teste seu domínio do padrão de agregação condicional.

A agregação condicional é a forma portável de criar tabelas dinâmicas que todo entrevistador aceita:

• Um CASE para cada coluna de saída, envolvido em uma agregação.
• SUM para totais, MAX/MIN para células de valor único, COUNT para contagens.
• Funciona porque as agregações ignoram o NULL das ramificações sem correspondência.
• Use COALESCE para transformar células vazias em 0.
• Limitação: as colunas precisam ser fixadas no código, o que nos leva às operações PIVOT dinâmicas a seguir.