Cómo calcular la diferencia entre dos filas de SQL

Calcular la diferencia entre dos filas de SQL puede ser una tarea difícil. Pero, es posible, y hay más de una manera de hacerlo. En este artículo, explicaré cómo utilizar las funciones de ventana de SQL LEAD() y LAG() para encontrar la diferencia entre dos filas de una tabla.

Me gusta pensar que este artículo es como una "hora feliz" dos por uno; se puede aprender sobre dos temas (calcular la diferencia entre dos filas y las funciones de ventana de SQL), leyendo solo un artículo. Ahora, ¡entremos de lleno en el tema!

Si quiere aprender sobre las funciones de ventana, consulte nuestro curso interactivo Funciones de ventana. Si ya las conoce y quiere practicar la escritura de consultas de funciones de ventana, le recomiendo nuestra página Ejercicios prácticos de funciones de ventana, que tiene 100 ejercicios prácticos.

Encontrar la diferencia entre dos valores de la misma fila

Para calcular cualquier diferencia, hacen falta dos elementos; para calcular una diferencia en SQL, hacen falta dos registros. También se puede calcular la diferencia entre dos columnas del mismo registro, como mostraré más tarde. Es muy fácil. Sin embargo, me centraré principalmente en encontrar la diferencia entre dos valores de la misma columna de registros diferentes.

Primero, voy a explicar un poco sobre nuestros datos. Utilizaremos una base de datos con dos tablas, como las que utiliza el Gobierno de una provincia imaginaria para definir algunos programas sociales. La primera tabla es housing, que contiene datos sobre el número de personas que alquilan, poseen o necesitan una vivienda. Es la siguiente:

Tabla housing

Para diseñar un plan de construcción de viviendas para la gente que las necesita, el Gobierno quiere obtener algunas métricas sobre los problemas de vivienda de diferentes ciudades. Supongamos que el Gobierno quiere saber cuántas personas de cada ciudad no tienen casa. La consulta será la siguiente:

SELECT 	
    city,
    total_population, 
    total_population - population_owning_house AS people_not_owning_house
FROM housing

En esta consulta, está claro que se está calculando una diferencia, utilizando dos columnas diferentes del mismo registro. A continuación, calcularemos las diferencias, utilizando dos registros.

Cómo calcular la diferencia de dos valores de la misma columna

Normalmente, todas las operaciones que realizamos en una consulta SQL están relacionadas con un registro. Pero, en este caso, necesitamos otro registro diferente. Esta es la parte complicada. Utilizaremos una técnica que emplea las funciones de ventana LAG() y LEAD() para obtener datos de otro registro. Si quiere profundizar en el tema, le sugiero que lea este artículo sobre las funciones de ventana, que explica claramente las funciones de ventana con multitud de ejemplos.

Supongamos que, para una ciudad específica ("Bronzepolis"), se quiere obtener la variación en el número de personas que necesitan una vivienda en relación al año anterior. Esta sería la consulta:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house) AS previous_year,
  population_needing_house - LAG(population_needing_house)
    OVER (ORDER BY year ) AS difference_previous_year
FROM housing
WHERE city = ‘Bronzepolis’
ORDER BY year

En el texto azul, puede ver el cálculo del delta de SQL entre dos filas. Para calcular una diferencia, se necesita un par de registros. Esos dos registros son "el registro actual" y "el registro del año anterior". Ese registro se obtiene con la función de ventana LAG(). Con esa función, se pueden obtener los datos del registro anterior (en base a un criterio de orden, que aquí es "ORDER BY year").

A continuación, se muestra el resultado de esta consulta. Las flechas rojas muestran que el valor de la función LAG() devuelve el mismo valor de population_needing_house que el registro del año anterior. La columna de la derecha muestra el resultado de la diferencia entre ese año y el anterior.

Para la siguiente consulta, ampliaremos el ámbito del análisis de los problemas de vivienda a todas las ciudades. Queremos la diferencia entre un año y el anterior de la columna population_needing_house para cada ciudad.

En primer lugar, eliminaremos la condición city = ‘Bronzepolis’. Queremos calcular los valores para cada ciudad, por lo que necesitamos una forma de separar los datos en grupos. Por eso, añadimos la cláusula PARTITION BY city a LAG(). PARTITION BY city le permite procesar todos los registros de la misma ciudad en la misma ventana.

Volverá a utilizar LAG() para calcular la diferencia entre el número de personas que necesitan una vivienda entre este año y el anterior. Veamos la consulta:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS previous_year,
  population_needing_house - LAG(population_needing_house)
   OVER (PARTITION BY city ORDER BY year ) AS difference_previous_year
FROM housing
ORDER BY city, year

En el texto azul se puede ver cómo se calcula la diferencia. Esta es una versión en español de lo que ocurre:

difference_previous_year = 
  population_needing_house del registro actual 
  - population_needing_house del registro del año anterior

Donde:

Population_needing_house del registro actual es la columna population_needing_house

y

Population_needing_house del registro del año anterior se obtiene a través de la función LAG(population_needing_house) OVER (PARTITION BY city ORDER BY year )

Por lo que, se utiliza la cláusula PARTITION para crear un conjunto con todos los registros de la misma ciudad. A continuación, se utiliza la cláusula ORDER BY para ordenar todos estos registros según su año. Por último, se utiliza la función LAG() para obtener el valor de population_needing_house del registro anterior.

LEAD() es otra función de ventana de SQL, que es similar a LAG(). Sin embargo, devuelve el siguiente registro del conjunto (en nuestro ejemplo, sería el registro del año siguiente).

Si echa un vistazo a las dos consultas anteriores, verá que utilizamos el mismo patrón para calcular la diferencia: utilizar LAG() para restar el valor anterior de la columna del valor actual (o utilizar LEAD() para restar el siguiente registro).

Las funciones de ventana de SQL son muy potentes. Para ver ejemplos de diferentes áreas de negocio, como finanzas, ventas y comercio, consulte este artículo sobre cuándo utilizar las funciones de ventana de SQL.

Cómo calcular la diferencia entre valores de fecha en SQL

En los ejemplos anteriores, calculamos el delta de dos filas, utilizando valores numéricos de columnas. Ahora, demostraré cómo calcular la diferencia entre dos valores del tipo de datos fecha.

Primero, pensemos sobre el tipo de datos del resultado. Cuando se calcula la diferencia entre dos valores de fechas, el resultado no es una fecha. Es un intervalo que representa el número de días que hay entre las dos fechas.

Supongamos que existe una tabla llamada hospital_statistics que almacena las estadísticas de los tres hospitales de la provincia. A continuación, se muestra un ejemplo de los datos de esa tabla:

Tabla hospital_statistics

Supongamos que existe una enfermedad rara llamada MDLR. El Gobierno quiere estudiar la frecuencia con la que los pacientes con MDLR ingresan en alguno de los hospitales, por lo que, pidió un informe que incluya las columnas day, hospital name, number of cases y days_since_the_last_case. Utilizaremos la siguiente consulta SQL para crear el informe:

SELECT
  day,
  hospital_name,
  number_of_patients,
  day - LAG(day) OVER (ORDER BY day) 
     AS days_since_last_case
FROM hospital_statistics
WHERE illness_name = 'MDLR'
ORDER BY day

Se puede ver que es el mismo patrón que usamos antes. Ahora, lo estamos usando para calcular los días transcurridos desde el último caso. Lo único que cambia es que estamos calculando una diferencia entre dos fechas, en lugar de entre valores numéricos.

Como se puede ver en los resultados, el valor de la columna days_since_last_case es un número entero, que representa el número de días.

SQL también permite calcular diferencias entre valores tipo timestamp. También, hay otras operaciones aritméticas interesantes con las fechas que se pueden utilizar en tipos de datos relacionados con fechas. Si quiere profundizar más en el tema, le sugiero el artículo Cómo utilizar funciones de ventana de SQL para analizar datos de series temporales sobre COVID-19.

Cómo encontrar la diferencia entre registros no consecutivos

Hasta ahora, hemos calculado las diferencias entre registros contiguos basándonos en criterios de orden específicos. En algunos casos, necesitará calcular la diferencia entre registros no contiguos.

Como ejemplo, volvamos a la primera consulta. Supongamos que queremos añadir otra columna que muestre el número de casos en los dos últimos años. Afortunadamente, LAG() y LEAD() tienen un parámetro opcional que especifica cuántos registros se saltarán antes/después del registro actual. Por defecto, este parámetro es 1 (es decir, "utilizar el registro siguiente/anterior"), pero se puede establecer otro número. Así, con este nuevo parámetro, la consulta será:

SELECT 	
  city,
  year,
  population_needing_house,
  LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS one_year_before,
  LAG(population_needing_house,2)
    OVER (PARTITION BY city ORDER BY year ) AS two_years_before,
  population_needing_house - LAG(population_needing_house)
    OVER (PARTITION BY city ORDER BY year ) AS difference_previous_year,
  population_needing_house - LAG(population_needing_house,2)
    OVER (PARTITION BY city ORDER BY year ) AS difference_last_two_years
FROM housing
ORDER BY 1, 2 DESC

El cálculo de la diferencia de dos registros no consecutivos está marcado en azul. En la siguiente imagen, podemos ver la nueva columna Difference last two years solo para los valores de 2019. Esto se debe a que 2019 es el único año para el que existen datos de dos años anteriores. Las líneas rojas conectan los resultados de la función LAG() (obteniendo los datos de 2017 y mostrando los mismos datos en el registro de 2017). Como se puede ver, las dos líneas tienen el mismo valor.

En este artículo se mostró la importancia de LAG() y LEAD(), pero hay muchas más funciones de ventana en SQL. Si quiere conocer más sobre ellas, lea este artículo sobre las funciones de ventana.

Más información sobre los cálculos en SQL y las funciones de ventana

Ya conocemos cómo utilizar SQL para calcular la diferencia entre filas consecutivas y cómo usar LAG() y LEAD() para encontrar la diferencia entre filas no consecutivas. Aunque hay otras formas de calcular esta diferencia, como, por ejemplo, a través de una autocombinación, estos métodos son muy útiles.

Si va a utilizar las funciones de ventana (o, de hecho, SQL) con frecuencia, o si simplemente quiere mejorar sus conocimientos, esta completa hoja de referencia de las funciones de ventana le ayudará enormemente. Para aprender las funciones de ventana a través de ejercicios, pruebe nuestro curso Funciones de ventana. En este artículo encontrará más información sobre el curso.