post icon

Como simular las funciones analiticas LAG y LEAD en SQL Server 2005 y 2008

Dos de las nuevas funciones analíticas que introduce SQL Server 2012 son LAG y LEAD que permiten obtener el valor de la fila anterior y posterior respectivamente, ésta función es sumamente útil en operaciones de análisis de datos.

Es realmente sencillo implementarlo en la versión 2012 del motor, si nos fijamos en la documentación de Microsoft podemos ver el ejemplo el cual sería así (utiliza la base de datos AdventureWorks)

LAG

Query:

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

Resultado:

BusinessEntityID SalesYear   CurrentQuota          PreviousQuota
---------------- ----------- --------------------- ---------------------
275              2005        367000.00             0.00
275              2005        556000.00             367000.00
275              2006        502000.00             556000.00
275              2006        550000.00             502000.00
275              2006        1429000.00            550000.00
275              2006        1324000.00            1429000.00

LEAD

Query:

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

Resultado:

BusinessEntityID SalesYear   CurrentQuota          NextQuota
---------------- ----------- --------------------- ---------------------
275              2005        367000.00             556000.00
275              2005        556000.00             502000.00
275              2006        502000.00             550000.00
275              2006        550000.00             1429000.00
275              2006        1429000.00            1324000.00
275              2006        1324000.00            0.00

Hasta aquí no hay mucha ciencia, sólo hemos transcripto la query y el resultado, pero que sucede si queremos hacer esto mismo con una versión más antigua del motor como lo son el 2005 y 2008?

Existe un par de soluciones posibles, la que a mi particularmente me gusta por la poca pérdida de rendimiento es usar Expresión de Tabla Común o CTE.

La solución consiste en unir la tabla consigo misma y relacionarla con la fila anterior o posterior utilizando la función ROW_NUMBER(), para no alargar más y crear dos querys separadas he unido ambas en la misma, es decir, el valor LAG y LEAD se puede visualizar en distintas columnas.

with    cte
        as ( select   row_number() over ( order by BusinessEntityID ) NroFila ,
                    BusinessEntityID ,
                    year(QuotaDate) as SalesYear ,
                    SalesQuota as CurrentQuota
            from     Sales.SalesPersonQuotaHistory
            where    BusinessEntityID = 275
                    and year(QuotaDate) in ( '2005', '2006' )
            )
select  cte.BusinessEntityID ,
        cte.SalesYear ,
        cte.CurrentQuota ,
        isnull(lag.CurrentQuota, 0) 'PreviousQuota' ,
        isnull(lead.CurrentQuota, 0) 'NextQuota'
from    cte
        left join cte lag on cte.NroFila - 1 = lag.NroFila
        left join cte lead on cte.NroFila + 1 = lead.NroFila

Resultado:

BusinessEntityID SalesYear   CurrentQuota          PreviousQuota         NextQuota
---------------- ----------- --------------------- --------------------- ---------------------
275              2005        367000,00             0,00                  556000,00
275              2005        556000,00             367000,00             502000,00
275              2006        502000,00             556000,00             550000,00
275              2006        550000,00             502000,00             1429000,00
275              2006        1429000,00            550000,00             1324000,00
275              2006        1324000,00            1429000,00            0,00

Comentarios desde Facebook:

Sin Comentarios aun, puedes tú ser el primero en comentar!

Deja tu Comentario

Responder