Descripción general de la función LAG() de SQL Server

En muchos casos, al usuario le gustaría acceder a los datos de la fila anterior o cualquier fila antes de la fila anterior desde la fila actual.
Para resolver este problema, se puede utilizar la función de ventana LAG() de SQL Server.

LAG():
SQL Server proporciona la función LAG(), que es muy útil en caso de que los valores de fila actuales deban compararse con los datos/valores del registro anterior o cualquier registro anterior al registro anterior. El valor anterior se puede devolver en el mismo registro sin el uso de autocombinación, lo que facilita la comparación.

Sintaxis:

LAG (scalar_expression [, offset] [, default])  
OVER ( [ partition_by ] order_by )  

Dónde :

1. escalar_expresión:
   el valor que se devolverá en función del desplazamiento especificado.
2. desplazamiento:
   el número de filas hacia atrás desde la fila actual de la que se obtiene un valor. Si no se especifica, el valor predeterminado es 1.
3. predeterminado:
   predeterminado es el valor que se devolverá si el desplazamiento va más allá del alcance de la partición. Si no se especifica un valor predeterminado, se devuelve NULL.
4. over ([partition_by] order_by):
   partición_by divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Si omite la cláusula PARTITION BY, la función trata el conjunto de resultados completo como un solo grupo. Por defecto, la cláusula order_by ordena en orden ascendente.

Ejemplo 1:

SELECT Organisation, [Year], Revenue,
LAG (Revenue, 1, 0) 
OVER (PARTITION BY Organisation ORDER BY [Year]) AS PrevYearRevenue  
FROM Org 
ORDER BY Organisation, [Year]; 

Producción –

Organización	Año	Ingresos	Ingresos del año anterior
ABCD Noticias	2013	440000	0
ABCD Noticias	2014	480000	440000
ABCD Noticias	2015	490000	480000
ABCD Noticias	2016	500000	490000
ABCD Noticias	2017	520000	500000
ABCD Noticias	2018	525000	520000
ABCD Noticias	2019	540000	525000
ABCD Noticias	2020	550000	540000
Noticias Z	2016	720000	0
Noticias Z	2017	750000	720000
Noticias Z	2018	780000	750000
Noticias Z	2019	880000	780000
Noticias Z	2020	910000	880000

En el ejemplo anterior, tenemos 2 canales de noticias de TV cuyos ingresos del año actual y del año anterior se presentan en la misma fila mediante la función LAG(). Como puede ver, el primer registro de cada uno de los canales de TV News no tiene ingresos del año anterior, por lo que muestra el valor predeterminado de 0. Esta función puede ser muy útil para generar datos para informes de BI cuando desee comparar valores. en períodos consecutivos, por ejemplo, año tras año o trimestre a trimestre o comparaciones diarias.

Ejemplo-2:

SELECT Z.*,  (Z.Revenue - z.PrevYearRevenue) as YearonYearGrowth
from (SELECT Organisation, [Year], Revenue,
      LAG (Revenue, 1) 
      OVER (PARTITION BY Organisation ORDER BY [Year] ) AS PrevYearRevenue 
      FROM Org) Z ORDER BY Organisation, [Year]; 

Producción –

Organización	Año	Ingresos	Ingresos del año anterior	AñoEnCrecimientoAño
ABCD Noticias	2013	440000	NULO	NULO
ABCD Noticias	2014	480000	440000	40000
ABCD Noticias	2015	490000	480000	10000
ABCD Noticias	2016	500000	490000	10000
ABCD Noticias	2017	520000	500000	20000
ABCD Noticias	2018	525000	520000	5000
ABCD Noticias	2019	540000	525000	15000
ABCD Noticias	2020	550000	540000	10000
Noticias Z	2016	720000	NULO	NULO
Noticias Z	2017	750000	720000	30000
Noticias Z	2018	780000	750000	30000
Noticias Z	2019	880000	780000	100000
Noticias Z	2020	910000	880000	30000

En el ejemplo anterior, podemos calcular de manera similar el crecimiento anual para el canal de noticias de televisión. Además, una cosa a tener en cuenta en este ejemplo es que no hemos proporcionado ningún parámetro predeterminado a LAG() y, por lo tanto, la función LAG() devuelve NULL en caso de que no haya valores anteriores.

La función LAG() se puede implementar a nivel de base de datos y las soluciones de informes de BI como Power BI y Tableau pueden evitar el uso de medidas engorrosas en la capa de informes.