Funciones para análisis Lag y Lead

Funciones LAG y LEAD

A partir de SQL Server 2017 se incorporan nuevas funciones que simplifican la forma de como  podemos analizar algunos registros, unas de estas funciones (LAG y LEAD) sirven para consultar registros anteriores o posteriores a el registro donde estamos posicionados en un momento, esto es muy útil en algunas ocasiones cuando queremos tomar un valor que viene de otras filas y relacionarlo en un  calculo.

Para poder demostrarlo hagamos un pequeño ejemplo de la siguiente manera:

1.- En nuestro Management Studio creemos una tabla con la siguiente estructura:


CREATE TABLE [dbo].[HistoricoPrecios](
[Cod_producto] [varchar](20) NOT NULL,
[PrecioActual] [decimal](18, 4) NOT NULL,
[PrecioAnterior] [decimal](18, 4) NOT NULL,
[PrecioSiguiente] [decimal](18, 4) NOT NULL
) ON [PRIMARY]
GO

Una vez creada la tabla procedemos a insertar regustros en ella de la siguiente manera:

insert into HistoricoPrecios(Cod_producto,PrecioActual,PrecioAnterior,PrecioSiguiente)
  values('010001',20.00,0,0)

insert into HistoricoPrecios(Cod_producto,PrecioActual,PrecioAnterior,PrecioSiguiente)
  values('010001',22.25,0,0)

insert into HistoricoPrecios(Cod_producto,PrecioActual,PrecioAnterior,PrecioSiguiente)
  values('010001',23.50,0,0)

insert into HistoricoPrecios(Cod_producto,PrecioActual,PrecioAnterior,PrecioSiguiente)
  values('010001',25.75,0,0)

insert into HistoricoPrecios(Cod_producto,PrecioActual,PrecioAnterior,PrecioSiguiente)

  values('010001',27.30,0,0)

Habiendo creado esto, podemos consultar nuestra tabla y ver que los campos de Precio Anterior y siguiente todos los insertamos en cero, la idea es que actualicemos nuestros campos con los respectivos valores que le corresponden, pero primero hagamos una consulta usando Lag y Lead para asegurarnos que tenemos la información como se necesita:

select Cod_producto, PrecioActual,
   LAG(PrecioActual) over(order by PrecioActual) PrecioAnterior,
   LEAD(PrecioActual) over(order by PrecioActual) PrecioSiguiente

from HistoricoPrecios

El resultado de la consulta sera el siguiente:

Si deseamos podemos mejorar nuestra consulta añadiendo unos condicionales para los campos donde tenemos valores Null, cuya causa es por que son los primeros y últimos registros, para ello lo haremos empleando un condicional "Case When", de la siguiente manera:

select Cod_producto, PrecioActual,
   case when LAG(PrecioActual) over(order by PrecioActual)  is null
then 0
else LAG(PrecioActual) over(order by PrecioActual)  end PrecioAnterior,
   case when LEAD(PrecioActual) over(order by PrecioActual) is null
then 0
else LEAD(PrecioActual) over(order by PrecioActual) end PrecioSiguiente

from HistoricoPrecios

Lo que hicimos fue condicionar los campos PrecioAnterior y PrecioSiguiente para que detecte que si estos son Null entonces me cambie por cero y si no me coloque el valor que trae el Lag y el Lead, tambien podemos apreciar que las funciones Lag y Lead deben ir acompañadas de algo que les indique bajo que condición se asignaran los registros anterior o siguiente y es lo que hace over, su función es decirles que el registro anterior depende del orden que se le esta indicando. Hecho esto la consulta mejorada nos quedaría de la siguiente forma:

Ahora si puedo confiar en los registros y actualizar la tabla para que se fijen los valores en ella, y lo hago de la siguiente forma:

update HistoricoPrecios set PrecioActual = resultado.PrecioActual, PrecioSiguiente = resultado.PrecioSiguiente
from (
select Cod_producto, PrecioActual,
   case when LAG(PrecioActual) over(order by PrecioActual)  is null
then 0
else LAG(PrecioActual) over(order by PrecioActual)  end PrecioAnterior,
   case when LEAD(PrecioActual) over(order by PrecioActual) is null
then 0
else LEAD(PrecioActual) over(order by PrecioActual) end PrecioSiguiente

           from HistoricoPrecios

) resultado

Notese que para no salirnos de lo que venimos haciendo colocamos nuestra consulta como sub consulta del update y aprovechar la potencia de SQL reciclando nuestro código y aprovechando sus campos. Una vez corrido estas lineas ya podemos verificar nuestra tabla y ver como quedo su contenido:

Para mas información del tema, puedes consultar el sitio oficial de SQL Server: Lag/Lead