Las funciones LAG y LEAD en SQL

LAG() y LEAD() son funciones posicionales. Una función posicional es una función de tipo ventana. Si no sabes cuándo o cómo usarlas, cuál es la sintaxis de cada función, por qué podrías querer usarlas y cuáles son las diferencias, ¡sigue leyendo!

LAG() y LEAD() son funciones posicionales. Son funciones de ventana y resultan muy útiles en la creación de informes, ya que pueden hacer referencia a datos de filas superiores o inferiores a la fila actual. En este artículo, veremos ambas en detalle.

Sintaxis de la función LAG

La función LAG() permite acceder a un valor almacenado en una fila diferente por encima de la fila actual. La fila superior puede ser adyacente o estar un número de filas por encima, ordenada por una columna o conjunto de columnas especificado.

Veamos su sintaxis:

LAG(expresión [,desplazamiento[,valor_predeterminado]]) OVER(ORDER BY columnas)

LAG() toma tres argumentos: el nombre de la columna o una expresión de la que se obtiene el valor, el número de filas a saltar (desplazamiento) por encima, y el valor predeterminado a devolver si el valor almacenado obtenido de la fila de arriba está vacío. Sólo se requiere el primer argumento. El tercer argumento (valor predeterminado) sólo se permite si se especifica el segundo argumento, el desplazamiento.

Al igual que otras funciones de ventana, LAG() requiere la cláusula OVER. Puede tomar parámetros opcionales, que explicaremos más adelante. Con LAG(), se debe especificar un ORDER BY en la cláusula OVER, con una columna o una lista de columnas por las que deben ordenarse las filas.

Consideremos la siguiente tabla, venta:

Y la siguiente consulta con una función LAG():

SELECT nombre_vendedor, valor_venta,
  LAG(valor_venta) OVER(ORDER BY valor_venta) as valor_venta_anterior
FROM venta;

Este es el resultado:

Este uso más sencillo de LAG() muestra el valor de la fila adyacente anterior. Por ejemplo, el segundo registro muestra el valor de la venta de Alice ($12.000) con el monto de la venta de Stef ($7.000) de la fila anterior, en las columnas valor_venta y valor_venta_anterior, respectivamente. Observa que la primera fila no tiene una fila adyacente por encima, y en consecuencia el campo valor_venta_anterior está vacío (NULL), ya que la fila de la que se debería obtener el valor de valor_venta no existe.

Si sólo se especifica el argumento requerido (el nombre de la columna u otra expresión), como hemos hecho en este ejemplo, el argumento del desplazamiento es 1 de manera predeterminada, y el tercer argumento es NULL de manera predeterminada. En nuestro ejemplo, la primera fila del conjunto de resultados tiene NULL en valor_venta_anterior y en las demás filas están los valores de las respectivas filas inmediatamente anteriores, porque el desplazamiento es 1.

Con LAG() se puede ver el valor de la fila actual, así como el valor de la fila adyacente anterior. Esto se puede utilizar, por ejemplo, para comprobar el valor de la venta de una fila determinada con el de la fila anterior, con el valor de la venta ordenado de menor a mayor.

La siguiente ilustración muestra cómo el valor de la fila adyacente anterior se añade a la fila actual.

La función LAG() está incluida en nuestra práctica "Hoja de trucos SQL: Funciones de ventana".

Sintaxis de la función LEAD

LEAD() es similar a LAG(). Mientras que LAG() accede a un valor almacenado en una fila superior, LEAD() accede a un valor almacenado en una fila inferior.

La sintaxis de LEAD() es igual que la de LAG():

LEAD(expresión [,desplazamiento[,valor_predeterminado]]) OVER(ORDER BY columnas)

Al igual que LAG(), la función LEAD() toma tres argumentos: una expresión o el nombre de una columna, el desplazamiento que se debe saltar por debajo y el valor predeterminado que se debe devolver si el valor almacenado obtenido de la fila de abajo está vacío. Sólo se requiere el primer argumento. El tercer argumento, el valor predeterminado, sólo puede especificarse si se especifica el segundo argumento, el desplazamiento.

Al igual que LAG(), LEAD() es una función de ventana y requiere una cláusula OVER. Y al igual que LAG(), LEAD() debe ir acompañada de un ORDER BY en la cláusula OVER.

Volvamos a ver la tabla venta:

He aquí una consulta con la función LEAD():

SELECT nombre_vendedor, valor_venta,
  LEAD(valor_venta) OVER(ORDER BY valor_venta) as valor_de_la_proxima_venta
FROM venta;

Este es el conjunto de resultados:

Las filas están ordenadas por la columna especificada en ORDER BY (valor_venta). La función LEAD() toma el valor de la venta de la fila inferior. Por ejemplo, el valor de la venta del propio Stef es de $7.000 en la columna valor_venta, y la columna valor_de_la_proxima_venta del mismo registro contiene $12.000. Este último proviene de la columna valor_venta de Alice, la vendedora de la fila siguiente. Observa que la última fila no tiene una fila siguiente, por lo que el campo valor_de_la_proxima_venta está vacío (NULL) para la última fila.

Si especificas sólo el argumento requerido, es decir, sólo el nombre de la columna u otra expresión, el desplazamiento predeterminado es 1 y el tercer argumento predeterminado es NULL. En nuestro ejemplo, el valor para valor_de_la_proxima_venta de Alice es de la columna valor_venta de la fila adyacente inferior, ya que el desplazamiento predeterminado es 1.

Con LEAD() se pueden comparar valores entre filas. La siguiente ilustración muestra cómo la cantidad devuelta por LEAD() se añade a la fila actual.

Uso de LAG() y LEAD() para comparar valores

Un uso importante de LAG() y LEAD() en informes es comparar los valores de la fila actual con los valores de la misma columna pero en una fila superior o inferior.

Consideremos la siguiente tabla, venta_anual, que se muestra a continuación:

Como puedes ver, esta tabla contiene el valor total de las ventas por año. Utilizando LAG() y LEAD(), podemos comparar los importes de las ventas anuales entre años.

Veamos esta consulta:

SELECT  ano, venta_total_actual,
   LAG(venta_total) OVER(ORDER BY ano) AS venta_total_anterior,
   venta_total - LAG(venta_total) OVER(ORDER BY ano) AS diferencia
FROM venta_anual;

Este es el conjunto de resultados:

Esta consulta toma el valor de las ventas del año anterior y lo pone en la columna venta_total_anterior utilizando la función LAG(). El ORDER BY en la cláusula OVER ordena los registros por año, asegurando que la fila adyacente de arriba representa el año anterior. A continuación, toma el valor de la columna venta_total_actual de la fila anterior y lo traslada a la fila actual.

Esta consulta también calcula la diferencia en el valor de las ventas entre el año actual y el anterior. Esto puede ayudarnos a entender si hubo un aumento (diferencia positiva) o una disminución (diferencia negativa) en las ventas de un año a otro.

Para 2015, no tenemos información sobre el año anterior. Por lo tanto, el valor devuelto por la función LAG() es NULL y también la diferencia. Ahora, la venta total en 2018 fue de $32.000 y de $34.000 en 2017 (el año anterior), como se muestra en la columna venta_total_anterior. La diferencia es de -$2.000, lo que indica que en 2018 hubo una disminución de las ventas de $2.000 en comparación con el año 2017.

Uso de LAG() y LEAD() con un desplazamiento específico

Puedes utilizar las funciones LAG() y LEAD() con dos argumentos: el nombre de la columna y el desplazamiento.

Consideremos la siguiente tabla, empleado:

La siguiente consulta selecciona el bono del empleado con ID=1 para cada trimestre de cada año. A continuación, identifica los bonos del trimestre correspondiente del año anterior y del año posterior.

SELECT ano, trimestre,
  LAG(bono,4) OVER(ORDER BY ano,trimestre) AS bono_anterior,
 bono AS bono_actual,
  LEAD(bono,4) OVER(ORDER BY ano,trimestre) AS bono_siguiente
FROM empleado
WHERE id_del_empleado=1;

Esta consulta devuelve el siguiente conjunto de resultados:

Las filas resaltadas en verde son los registros del primer trimestre de cada año, las filas en blanco el segundo trimestre de cada año, etc. En cualquier fila, los importes de los bonos anterior y siguiente se toman del trimestre correspondiente del año anterior y del año siguiente, se asignan a las columnas bono_anterior y bono_siguiente, respectivamente.

Por ejemplo, el empleado ID=1 recibió un bono de $80 en el primer trimestre de 2018. Para el mismo empleado, el bono del primer trimestre de 2017 fue de $100, y el bono del primer trimestre de 2019 fue de $0. ORDER BY especifica que las filas se ordenen por año y trimestre. Un desplazamiento de 4 indica a LEAD() y LAG() que se salten 4 filas antes y después de la fila actual, respectivamente. Con este desplazamiento, se pueden comparar valores del mismo trimestre de diferentes años, porque hay 4 trimestres en un año. La imagen siguiente ilustra esta idea.

Puedes encontrar más información sobre las funciones LAG() y LEAD() en nuestros artículos "Funciones de ventana comunes en SQL: Funciones posicionales", de Aldo Zelen y "¿Cuándo se utilizan las Funciones de ventana en SQL?", de Tihomir Babic.

Uso de LAG() y LEAD() con un valor predeterminado

En la sección anterior, vimos cómo utilizar el argumento de desplazamiento en LAG() y LEAD(). Ahora, consideramos los casos con un tercer argumento: el valor predeterminado a asignar cuando el valor obtenido es NULL. Para especificar este argumento, se debe especificar también el segundo argumento, el desplazamiento. El desplazamiento predeterminado es 1, así que especifica 1 para mantener el desplazamiento predeterminado o algún otro valor apropiado para tu caso.

Veamos otro ejemplo. La siguiente tabla, venta_producto contiene los ID de los productos, el mes (1 = enero, 2 = febrero, etc.) y la cantidad de ventas por mes.

A continuación, se muestran los registros cuyo ID de producto es 1.

La siguiente consulta:

SELECT id_de_producto, mes,
  LAG(cantidad,1,0) OVER(ORDER BY mes) AS cantidad_anterior,
  cantidad AS cantidad_actual,
  cantidad - LAG(cantidad,1,0) OVER(ORDER BY mes) AS diferencia
FROM venta_producto
WHERE id_de_producto=1;

Devuelve el siguiente resultado:

Para el producto con ID=1, seleccionamos el mes de venta, la cantidad de ventas para este mes (cantidad_actual), y la cantidad de ventas del mes anterior (el valor de la fila anterior devuelto por LAG()).

Nos gustaría mostrar cero en lugar de NULL cuando LAG() intente obtener valores de filas más allá de las que existen en nuestro conjunto de datos. Tanto para LAG() como para LEAD(), esto se hace especificando un tercer argumento, el valor predeterminado. Recuerda que el argumento desplazamiento es necesario para especificar el argumento del valor por defecto; aquí, especificamos un desplazamiento de 1 para buscar en la fila de arriba. A continuación, especificamos 0 como tercer argumento. Esto pone a cero cualquier intento de obtener valores de filas que no existen, como es el caso aquí para la primera fila (no hay ninguna fila por encima de la primera fila).

Observa que el valor predeterminado cero se asigna sólo a las filas que no existen; las filas adyacentes por encima que sí existen pero con NULLs en cantidad_actual se dejan sólo como NULL en lugar de cambiarlas a 0. Puedes ver esto en la fila donde mes=4: aunque cuenta_actual para la fila por encima (mes=3) es NULL, no se reemplaza con un cero, ya que la fila anterior sí existe y sólo ocurre que contiene un NULL en cantidad_actual.

Uso de LAG() y LEAD() con particiones

Sigamos con el mismo ejemplo, pero ahora examinemos un caso en el que necesitamos PARTITION BY en la cláusula OVER. A continuación, se muestra la siguiente parte de la tabla venta_producto con otro producto cuyo ID=2.

La siguiente consulta:

SELECT id_de_producto, mes,
  LAG(cantidad,1,0) OVER(PARTITION BY id_de_producto ORDER BY mes) AS cantidad_anterior,
  cantidad AS cantidad_actual,
  cantidad - LAG(cantidad,1,0) OVER(PARTITION BY id_de_producto ORDER BY mes) AS diferencia
FROM venta_producto;

Devuelve el siguiente resultado:

Tenemos varios productos en esta tabla. Para calcular las diferencias entre la cantidad de ventas actuales y anteriores por separado para cada producto, especificamos PARTITION BY antes de ORDER BY en la cláusula OVER.

Puedes nombrar una columna o una lista de columnas en PARTITION BY. Aquí, utilizamos la columna id_de_producto para dividir los registros en particiones y luego ordenar por mes dentro de cada partición. Como resultado, cada partición comienza con el mes 1 y termina con el mes 4.

PARTITION BY es similar a GROUP BY en el sentido de que agrupa los mismos valores. Sin embargo, a diferencia de GROUP BY, PARTITION BY no contrae las filas originales en una sola fila; las filas originales siguen siendo accesibles. Puedes leer más sobre la diferencia entre PARTITION BY y ORDER BY en el artículo "¿Cuál es la diferencia entre GROUP BY y PARTITION BY?".

¡Las funciones LAG y LEAD son muy útiles!

Las funciones posicionales como LAG() y LEAD() son útiles en muchas situaciones. Suelen utilizarse en la creación de informes, porque pueden referirse a las filas de arriba o de abajo, como hemos visto en estos ejemplos. Espero que este artículo te ayude a ampliar tus conocimientos de SQL sobre las funciones de ventana. Lee más sobre las funciones posicionales en los artículos "Funciones de ventana comunes en SQL: Funciones posicionales", de Aldo Zelen y "¿Cuándo se utilizan las Funciones de ventana en SQL?", de Tihomir Babic. Si te interesa aprender más sobre las funciones de ventana, prueba nuestro curso interactivo "Funciones de ventana" en la plataforma LearnSQL.es.