Función SQL LAG()

La función LAG() - una de las funciones de ventana de SQL, es una herramienta importante para la planificación y el análisis de tendencias. En este artículo, demostraré cómo incluir la función SQL LAG() en sus consultas utilizando algunos ejemplos reales.

Lasfuncionesventanade SQL, también conocidas como funciones analíticas o funciones OVER, añaden una nueva dimensión al análisis de datos. Permiten incluir agregados o datos de otras filas junto a la fila actual.

La función LAG() permite mirar "a través de la ventana" a una fila anterior e incluir sus datos junto a la fila actual. Una función similar, LEAD(), permite mirar hacia delante, a filas posteriores. Utilizando las funciones LEAD( ) y LAG( ), puede incluir fácilmente en sus informes características útiles como comparaciones interanuales.

Si se toma en serio el análisis de datos, puede que le interese echar un vistazo al cursoFunciones de ventana de LearnSQL.es. Aprenderás resolviendo más de 200 ejercicios interactivos guiados utilizando una base de datos real a la que accederás a través de tu navegador. El curso dura unas 20 horas y, una vez finalizado, podrás utilizar tus nuevos conocimientos para potenciar tus propios proyectos de análisis de datos.

¿Qué hace la función LAG()?

Esta función le permite incluir un valor de columna de una fila anterior junto con los datos de la fila actual. Resulta especialmente útil para analizar tendencias a lo largo del tiempo, como comparaciones mes a mes o año a año.

También puede utilizarse para responder a diversas preguntas. ¿Cuál es la diferencia entre los resultados medios de la Escuela A y la Escuela B? ¿En qué medida afecta el uso de una materia prima diferente a la vida útil de un componente?

Sintaxis de la función LAG()

En su forma más simple, la sintaxis de la función LAG() de SQL es ...

LAG(column_1) OVER (ORDER BY column_2)

... donde:

• column_1 es el nombre de la columna que desea incluir de la fila anterior.
• OVER indica que está utilizando una función de ventana; esta palabra clave es obligatoria.
• ORDER BY también es obligatoria cuando se utiliza LAG(). La secuencia de filas debe ser predecible, de lo contrario la función carece de sentido. Sin embargo, el orden que elija no tiene por qué ser el mismo que el del informe final.
• column_2 es la columna que se utiliza para secuenciar las filas. Aquí puede especificar más de una columna.

SQL secuencia primero sus datos utilizando los valores de la columna_2. En cada fila, se remonta a la fila anterior de esta secuencia y recupera el valor de la columna_1. Este valor se incluye junto con cualquier otro dato. Este valor se incluye junto con cualquier otro dato que haya solicitado de la fila actual.

Ejemplo básico de la función LAG()

Veamos una sencilla comparación mes a mes. Una tabla denominada monthly_sales contiene los siguientes datos:

Si desea ver las ventas del mes pasado junto con las ventas de este mes, su consulta tendría el siguiente aspecto:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month
FROM monthly_sales;

El resultado es el siguiente:

Observa que el valor de las ventas del mes anterior se ha bajado a la fila del mes actual:

Ejemplos más complejos

Ahora que ha aprendido a realizar una consulta sencilla utilizando la función SQL LAG(), veamos otras formas de utilizarla en sus análisis.

Uso de LAG() en cálculos

A menudo querrá utilizar los resultados de LAG() en cálculos. Por ejemplo, puede que desee ver la diferencia y el cambio porcentual entre meses. Modifiquemos la consulta anterior para incluir estos cálculos.

Puede utilizar el resultado de una función en cálculos como lo haría con cualquier otra columna.

La consulta tiene el siguiente aspecto:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month,
  sales_value - LAG(sales_value) OVER (ORDER BY year, month) AS change,
  ((sales_value - LAG(sales_value) OVER (ORDER BY year, month)) * 100)
   / (LAG(sales_value) OVER (ORDER BY year, month)) AS percentage
FROM monthly_sales;

Los resultados son:

Uso de LAG() con un desplazamiento

En los ejemplos que hemos visto, la consulta recogía los datos de la fila anterior del conjunto. Este es el comportamiento por defecto. Sin embargo, puede retroceder más de una fila especificando un desplazamiento.

La sintaxis para especificar un desplazamiento es:

LAG(column_1, offset) OVER (ORDER BY column_2)

offset es un número entero que indica cuántas filas debe retroceder la consulta para encontrar los datos. Si no se especifica un desplazamiento, la base de datos asume un desplazamiento de 1.

Supongamos que desea ver las ventas de este mes, las ventas del mes pasado y las ventas del mes anterior una al lado de la otra. La consulta es:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month,
  LAG(sales_value,2) OVER (ORDER BY year, month) AS prev_month
FROM monthly_sales;

Los resultados son:

Incluido un valor predeterminado

En los resultados que hemos visto, la función LAG() devolvía NULL en la primera fila porque no había ninguna fila anterior. Puede que no siempre quiera que haga eso.

Por ejemplo, supongamos que una empresa abrió en diciembre de 2023. Usted puede querer mostrar esto teniendo cero en las ventas del mes pasado para diciembre de modo que sea obvio que las ventas han aumentado de nada a 7380 durante ese mes.

El cero se denomina valor por defecto - un valor que se muestra cuando no existen cifras.

Para incluir un valor por defecto en su consulta, la sintaxis es:

LAG(column_1, offset, default) OVER (ORDER BY column_2)

Tenga en cuenta que siempre tiene que especificar un desplazamiento si incluye un valor por defecto. El desplazamiento sería 1 si desea consultar la fila inmediatamente anterior a la fila actual.

La consulta del ejemplo anterior es

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value, 1, 0) OVER (ORDER BY year, month) AS last_month
FROM monthly_sales;

Los resultados son:

Uso de LAG() con PARTITION BY

Supongamos que los datos originales se ampliaron para incluir más de un departamento:

Probablemente querrá que cada departamento se mantenga separado en la comparación. Esto se conoce como partición. La sintaxis para dividir los resultados en particiones es ...

LAG(column_1) OVER (PARTITION BY column_2 ORDER BY column_3)

... donde column_2 es la columna que desea utilizar para la partición.

Intentemos una consulta que dividirá el informe por departamento pero que seguirá ordenando las filas por año y mes dentro de cada departamento:

SELECT 
  department, 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month) AS last_month
FROM monthly_sales;

Los resultados son los siguientes:

Observe que las ventas del mes pasado vuelven a ser NULL en la primera fila del nuevo departamento.

SQL utiliza department como clave de ordenación primaria porque se especificó en la cláusula PARTITION BY. Dentro de department, secuencia los datos por year y luego por month, según la cláusula ORDER BY.

En cada fila, mira hacia atrás para ver si hay una fila anterior que pertenezca al mismo department. En caso afirmativo, recupera el contenido de la columna sales_value de la fila anterior. Éste se incluye en la fila actual como last_month. Si no había ninguna fila anterior, last_month se establece en un valor NULL.

Utilización de los resultados de LAG() para ordenar un informe

El orden final del informe no tiene por qué ser el mismo que la secuencia utilizada en la cláusula OVER. Puede utilizar la cláusula normal ORDER BY al final de la consulta para especificar una secuencia diferente.

Supongamos que desea ver los resultados secuenciados por el aumento del valor de las ventas entre meses.

Su consulta podría tener este aspecto:

SELECT 
  department, 
  year, 
  month, 
  sales_value, 
  sales_value - LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month) AS change
FROM monthly_sales
ORDER BY
  department, 
  sales_value - LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month);

Los resultados tienen este aspecto:

Esto puede ser útil si desea ver cuándo le fue mal a la empresa y cuándo le fue bien. Esta información puede ayudarle a localizar el origen de cualquier problema.

Dónde obtener más información sobre la función LAG() de SQL

Si desea obtener más información sobre la función LAG() - y sobre las funciones de ventana en general - aquí tiene algunos artículos que ofrecen información adicional:

• Las funciones LAG() y LEAD() en SQL
• Función LEAD() en SQL
• Cómo calcular las diferencias interanuales en SQL

Si necesita repasar las funciones de ventana, estos recursos pueden ayudarle:

• Este Funciones de ventana Cheat Sheet es ideal para tenerlo contigo mientras escribes consultas.
• Cuando eres nuevo en el tema, ver estos Ejemplos de Funciones de Ventana es realmente útil.
• Si estás buscando trabajo, aquí tienes algunas preguntas para entrevistas enFunciones de ventana . Estúdialas y te ayudarán a superar la entrevista SQL.

Como he mencionado antes, si realmente quieres convertirte en un experto, te recomiendo el cursoFunciones de ventana de LearnSQL.es. Aprenderás exactamente cómo utilizar toda la potencia de las funciones de ventana de SQL. Y ganarás confianza resolviendo un montón de ejercicios prácticos.

No hay nada como la práctica para aumentar tus conocimientos y habilidades. Si quieres algunos ejemplos guiados para trabajar por tu cuenta, aquí tienes algunos recursos:

• El artículo Funciones de ventana Practice Exercises contiene 11 ejercicios con soluciones.
• Este Funciones de ventana Practice Set es una pista de aprendizaje que contiene más de 100 ejercicios prácticos. Trabajarás con tres bases de datos diferentes para aprender a resolver distintos tipos de problemas.

La función LAG() de SQL es una herramienta increíble para analizar sus datos con el fin de identificar rápidamente tendencias y mantener la competitividad de su organización. Como analista de datos, merece la pena dedicar tiempo a convertirse en un experto en la función ventana.