Padrões SQL úteis: Sumarização condicional com CASE

Ao começar a codificar em SQL, você usará algumas declarações e técnicas repetidas vezes. Nós chamamos estes "padrões SQL". Esta série analisará os padrões SQL mais comuns e considerará como utilizá-los.

Anteriormente, nós olhamos para o padrão SQL de correspondência NULLs. Isto é importante quando você está comparando colunas contendo valores NULL. Hoje, vamos considerar outra prática SQL: sumarização condicional com operador CASE.

O que é a Resumação Condicional?

Quando você estiver usando funções agregadas para criar consultas de relatórios, você se encontrará freqüentemente usando a compactação condicional com o operador CASE. Lembre-se de que CASE retorna um valor baseado em critérios definidos. (Para saber mais sobre a expressão CASE, veja este posto e este aqui.) Quando você faz um resumo com CASE, você está simplesmente somando (resumindo) valores que atendem à expressão CASE.

Obviamente, você estará usando a parte SUM da consulta para agregar estes valores. Eu sei que isto parece complicado, mas não é. Vamos usar um exemplo simples para explicar isso.

Vamos começar considerando um transactions tabela que contém dados transacionais de uma pequena empresa.

O transactions A tabela tem estas colunas:

• id - Um identificador único para cada transação
• datetime - O carimbo da hora para a transação
• customer - A identificação do cliente
• creditcard - A identificação do tipo de cartão de crédito utilizado
• amount - O valor da transação, em dólares
• account - O número de conta do cliente
• type - O tipo de transação

Aqui estão os dados que encontraríamos em uma tabela como esta:

Queremos encontrar a soma dos valores das transações e o número de transações concluídas antes de 1º de abril. Além disso, queremos estes dados listados por cliente individual. Poderíamos conseguir isso usando a seguinte consulta :

	SELECT
 customer,
 SUM(
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
 THEN amount
 ELSE 0
 END
 )        AS sum_amount_after,
 SUM(CASE WHEN datetime >= TIMESTAMP '2017-04-01'
 THEN 1
 ELSE 0
 END) AS transaction_count_after,
 SUM(CASE WHEN datetime < TIMESTAMP '2017-04-01'
 THEN amount
 ELSE 0
 END) AS sum_amount_prior,
 SUM(CASE WHEN datetime < TIMESTAMP '2017-04-01'
 THEN 1
 ELSE 0
 END) AS transaction_count_prior
 FROM transactions
 WHERE datetime BETWEEN TIMESTAMP '2017-01-01'  AND '2017-05-01'
 GROUP BY customer

O resultado desta consulta é:

Espere! Como isso funcionou?

Esta longa consulta pode ser confusa, então vamos dividi-la um pouco. Vamos nos concentrar primeiro na parte que trata do corte de 1 de abril (2017-04-01).

👾 [NOVO] O que acontece quando você combina #CASE com #Dados que modificam as declarações #SQL? Descubra!! ➽ https://t.co/7IuBoDT85z... pic.twitter.com/wNiDiv5hSa

- Vertabelo (@Vertabelo) 6 de junho de 2017

Abaixo, estamos analisando o valor da transação para o cliente '1'. Qualquer valor de transação que tenha sido lançado antes de 01.04.2017 será definido como "0". Daremos um nome a esta coluna amount_after.

SELECT
 customer,
 datetime,
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
  THEN amount
 ELSE 0
 END
  AS amount_after,
 amount
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
      AND customer = '1'
ORDER BY datetime;

Alternativamente, podemos substituir os zeros mostrados nos resultados por um NULL na declaração ELSE:

SELECT
 customer,
 datetime,
 CASE WHEN datetime >= TIMESTAMP '2017-04-01'
  THEN amount
 ELSE null
 END
  AS amount_after,
 amount
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
      AND customer = '1'
ORDER BY datetime;

Agora, se resumirmos estas colunas, obteremos:

1. O total de todas as transações para sum(amount).
2. A soma de todas as transações que foram postadas após 01.04. Todas as transações postadas antes de 01.04 são definidas como zero (ou NULL) para sum(amount_after).

Se quisermos contar quantas transações foram postadas após 01.04, podemos modificar a consulta e criar uma declaração COUNT que usa o mesmo CASE com NULL no ELSE.

SELECT
  customer,
  count(CASE WHEN datetime >= TIMESTAMP '2017-04-01'
    THEN amount
        ELSE NULL
        END)
    AS count_after
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
GROUP BY customer
ORDER BY customer;

Nota: Esta consulta é realmente rápida, pois o RDBMS só precisa acessar uma tabela. Construir consultas de agregação que usam apenas uma tabela é uma boa maneira de obter resultados rapidamente.

Tente usar CASE com um zero na declaração COUNT. Qual é o resultado e por quê? Diga-nos na seção de comentários abaixo.