Patrones SQL útiles: Resumen condicional con CASE

Cuando comience a codificar en SQL, utilizará algunas sentencias y técnicas una y otra vez. Los llamamos "patrones SQL". Esta serie examinará los patrones SQL más comunes y considerará cómo utilizarlos.

Anteriormente, vimos El patrón SQL de coincidencia de NULLs. Esto es importante cuando se comparan columnas que contienen valores NULL. Hoy, vamos a considerar otra práctica de SQL: la integración condicional con el operador CASE.

¿Qué es el resumen condicional?

Cuando se utilizan funciones de agregación para crear consultas de informes, es frecuente que se utilice la integración condicional con el operador CASE. Recuerde que CASE devuelve un valor basado en criterios definidos. (Para obtener más información sobre la expresión CASE, consulte este artículo y este otro.) Cuando se realiza una integración con CASE, simplemente se están sumando (integrando) los valores que cumplen la expresión CASE.

Obviamente, usarás la parte SUM de la consulta para agregar estos valores. Sé que parece complicado, pero no lo es. Utilicemos un ejemplo sencillo para explicarlo.

Empezaremos por considerar una tabla básica transactions que contiene datos transaccionales de una pequeña empresa.

La tabla transactions tabla tiene estas columnas:

• id - Un identificador único para cada transacción
• datetime - La marca de tiempo de la transacción
• customer - El ID del cliente
• creditcard - El ID del tipo de tarjeta de crédito utilizada
• amount - El importe de la transacción, en dólares
• account - El número de cuenta del cliente
• type - El tipo de transacción

Estos son los datos que encontraríamos en una tabla como ésta:

Queremos encontrar la suma de los importes de las transacciones y el número de transacciones realizadas antes del 1 de abril. Además, queremos que se listen por cliente individual. Podríamos conseguirlo con la siguiente 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

El resultado de esta consulta es:

Espera, ¿cómo ha funcionado?

Esta larga consulta puede ser confusa, así que vamos a desglosarla un poco. Nos concentraremos primero en la parte que tiene que ver con el corte del 1 de abril (2017-04-01).

👾 [NUEVO] ¿Qué pasa cuando combinas #CASE con las sentencias modificadoras de #datos de #SQL? ¡¡¡Descúbrelo!!! ➽ https://t.co/7IuBoDT85z... pic.twitter.com/wNiDiv5hSa

- Vertabelo (@Vertabelo) 6 de junio de 2017

A continuación, estamos viendo el importe de la transacción para el cliente "1". Cualquier importe de transacción que se haya contabilizado antes del 01.04.2017 se pondrá a "0". Llamaremos a esta columna 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 reemplazar los ceros mostrados en los resultados con un NULL en la sentencia 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;

Ahora si resumimos estas columnas, obtendremos:

1. El total de todas las transacciones de sum(amount).
2. La suma de todas las transacciones que se contabilizaron después del 01.04. Cualquier transacción que se haya contabilizado antes del 01.04 se pone a cero (o NULL) para sum(amount_after).

Si queremos contar cuántas transacciones se contabilizaron después del 01.04, podemos modificar la consulta y crear una sentencia COUNT que utilice el mismo CASE con NULL en el 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 es realmente rápida, ya que el RDBMS sólo necesita acceder a una tabla. Construir consultas de agregación que usen sólo una tabla es una buena manera de obtener resultados rápidamente.

Pruebe a utilizar CASE con un cero en la sentencia COUNT. ¿Cuál es el resultado y por qué? Cuéntanoslo en la sección de comentarios más abajo.