LAG, LEAD: Obtener el valor de filas anteriores o posteriores.

Hola, algunas de las funciones que fueron introducidas con la versión SQL SERVER 2012 son LAG y LEAD, anteriormente ya muchos habíamos tenido la necesidad de obtener los valores de filas anteriores o posteriores para realizar cálculos simulando estas funciones.

LAG sirve para extraer valores previos de una columna en un recordset, con la posibilidad de especificar si queremos obtener el valor de la fila anterior o la 2da o 3ra, etc.. El número de filas previas por default es 1.

Usaré la base de datos AdventureWorks2012

SELECT
LAG( ProductSubcategoryID ) OVER( ORDER BY ProductSubCategoryID ) as lag1,
ProductSubCategoryID,
LAG( ProductSubcategoryID , 2 ) OVER( ORDER BY ProductSubCategoryID ) as lag2,
ProductSubCategoryID,
LAG( ProductSubcategoryID , 2, 100 ) OVER( ORDER BY ProductSubCategoryID ) as lag3,
 * FROM Production.ProductSubcategory

En la columna lag1 aplicamos la función ordenado por la columna ProductSubCategoryID, en la columna lag2 aplicamos nuevamente la función pero ahora con la diferencia que extraerá el valor de la columna ProductSubCategoryID de la segunda fila anterior. La columna lag3 extraemos los valores de la segunda fila anterior también, pero ahora reemplazaremos el valor NULL por 100.

APLICANDO PARTITION BY

En el post RANKING FUNCTIONS expliqué el funcionamiento del PARTITION BY; sirve para dividir un recordset en bloques que a partir de los cuales se puede realizar el cálculo de las funciones, en nuestro caso LAG.

SELECT
LAG( ProductSubcategoryID ) OVER( PARTITION BY ProductCategoryID ORDER BY ProductSubCategoryID ) as lag1,
ProductSubCategoryID,
LAG( ProductSubcategoryID , 2 ) OVER( PARTITION BY ProductCategoryID ORDER BY ProductSubCategoryID ) as lag2,
ProductSubCategoryID,
LAG( ProductSubcategoryID , 2, 100 ) OVER( PARTITION BY ProductCategoryID ORDER BY ProductSubCategoryID ) as lag3,
 * FROM Production.ProductSubcategory

En el primer ejercicio donde no utilizamos la clausula PARTITION BY, el recordset era un solo bloque por lo que hacía el cálculo de LAG en base a ese bloque, ahora, divide el recordset en bloques de acuerdo a la columna ProductCategoryID y por cada bloque aplica la función LAG.

LEAD similar a la función LAG pero ahora extrae los valores de las filas posteriores.

SELECT
LEAD( ProductSubcategoryID ) OVER( ORDER BY ProductSubCategoryID ) as lag1,
ProductSubCategoryID,
LEAD ( ProductSubcategoryID , 2 ) OVER( ORDER BY ProductSubCategoryID ) as lag2,
ProductSubCategoryID,
LEAD ( ProductSubcategoryID , 2, 100 ) OVER(ORDER BY ProductSubCategoryID ) as lag3,
 * FROM Production.ProductSubcategory

Ahora como estamos obteniendo las filas posteriores, es hasta el final del bloque donde obtendremos NULL.

APLICANDO PARTITION BY

Divide el resultado en bloques y aplica la función.

SELECT
LEAD( ProductSubcategoryID ) OVER( PARTITION BY ProductCategoryID ORDER BY ProductSubCategoryID ) as lag1,
ProductSubCategoryID,
LEAD ( ProductSubcategoryID , 2 ) OVER( PARTITION BY ProductCategoryID ORDER BY ProductSubCategoryID ) as lag2,
ProductSubCategoryID,
LEAD ( ProductSubcategoryID , 2, 100 ) OVER( PARTITION BY ProductCategoryID ORDER BY ProductSubCategoryID ) as lag3,
 * FROM Production.ProductSubcategory

En otra entrega les explicaré como simular estas funciones para versiones anteriores a SQL SERVER 2012.

SALUDOS!

COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE