En ocasiones necesitaremos acceder a las filas anteriores y/o posteriores de un conjunto de resultados en una consulta SQL, por ejemplo para comparar los valores de un mes (fila actual), con los valores de un mes anterior o posterior. Esto es algo que en versiones anteriores de SQL Server podíamos conseguir realizando un SELF JOIN (ej: un JOIN de una tabla consigo misma), y que en SQL Server 2012 se ha vuelto más sencillo gracias a las nuevas funciones analíticas LAG y LEAD.
Las funciones LAG y LEAD utilizan una sintaxis similar a la utilizada por las ya conocidas funciones de Ranking, especificando un criterio de ordenación, y opcionalmente un criterio de particionamiento.
Para verlo en un caso práctico, tengamos la siguiente consulta SQL:
SELECT Sale.SalesPersonId, DATEPART(yyyy, SaleDate) AS Year, City, Country, SUM(Amount) AS Total
,LAG( SUM(Amount) ) OVER ( PARTITION BY Sale.SalesPersonId ORDER BY DATEPART(yyyy, SaleDate)) AS LastYear
,LEAD( SUM(Amount) ) OVER ( PARTITION BY Sale.SalesPersonId ORDER BY DATEPART(yyyy, SaleDate)) AS NextYear
FROM dbo.Sale INNER JOIN dbo.SalesPerson ON Sale.SalesPersonId = SalesPerson.SalesPersonId
GROUP BY Sale.SalesPersonId, DATEPART(yyyy, SaleDate), City, Country
ORDER BY DATEPART(yyyy, SaleDate), Sale.SalesPersonId
|
Viendo sólo la consulta, quizás no nos diga mucho. Pero si vemos dicha consulta SQL junto con el conjunto de resultados que produce, la cosa cambia:
Como podemos ver en este ejemplo, en lugar de simplemente acceder a la fila anterior (LAG) o a la fila siguiente (LEAD), estamos accediendo a la fila del año anterior para cada comercial (es decir, particionamos por el comercial, y ordenamos por año). De este modo, conseguimos nuestro objetivo de una forma muy sencilla, y sin necesidad de realizar un SELF JOIN, ya que sólo existe una fila para cada combinación de año y comercial.
De hecho, para conseguir una consulta similar a la anterior sin LAG ni LEAD, deberíamos realizar un SELF JOIN para conseguir los datos del año anterior, y otro SELF JOIN para conseguir los datos del año siguiente. Esta técnica del SELF JOIN puede parecer muy sencilla, pero al final volvemos a lo de siempre, cuando la consulta SQL de marras con la que estamos trabajando es bastante compleja y necesitamos hacer varios SELF JOIN, la historia se complica, ya que el tiempo de codificación aumenta considerablemente (igual que el riesgo de meter la pata), y sobre todo, el día que tengamos que meter mano ahí para incluir alguna modificación, también tendremos que asumir un esfuerzo mayor. Definitivamente, el tamaño (de la consulta) importa ;-)
Las funciones LAG y LEAD también permiten especificar qué valor deseamos mostrar en lugar de NULL (ej: de utilidad pare representar el valor anterior a la primera fila, o el valor posterior a la última fila). Además, también podemos especificar el OFFSET (que por defecto es 1), lo cual nos permite especificar si deseamos obtener la fila inmediatamente anterior o la tercera fila anterior (en el caso de LAG, e ídem para LEAD).
Para más detalles, podemos consultar los Libros en Pantalla (Books-On-Line) o bien la ayuda OnLine.
Sin duda, las funciones LAG y LEAD son otra de las grandes novedades de SQL Server 2012.
Poco más por hoy. Como siempre, confío que la lectura resulte de interés.
