Diario de SQL Server

LAG y LEAD

 

Entre las nuevas funcionalidades de la siguiente versión de SQL Server, Denali, hay una que está llamándome mucho la atención, como son las bases de datos autocontenidas. Es una de esas cosas que saldrán en todas las presentaciones de tipo "What's new?", que las habrá y muchas.

Pero luego hay un par de funciones nuevas, en lo tocante a T-SQL, que vienen a resolver una problemática de lo más común: comparar un registro con el siguiente o el anterior. Ejemplo: obtener en la misma línea las ventas de Agosto y su comparativa con las del mes de Julio. Supongamos el siguiente caso:

 

use tempdb

go

 

CREATE TABLE MonthlyProfit

(

yearid int,

Monthid int,

Profit bigint,

constraint PK_MonthlyProfit primary key clustered (yearid, monthid)

)

 

go

 

INSERT INTO MonthlyProfit (yearid, Monthid, Profit)

VALUES

(2010,1,1766), (2010,2,100), (2010,3,1500), (2010,4,15000), (2010,5,900), (2010,6,45),

(2010,7,1766), (2010,8,9100), (2010,9,-100), (2010,10,50), (2010,11,900000), (2010,12,6575)

 

go

 

Hasta ahora, lo que tocaba era cruzar la tabla consigo misma, pero para el periodo anterior, lo que comúnmente se llama running total:

 

SELECT

t1.yearid,

t1.Monthid,

t1.Profit as ProfitThisMonth,

isnull(t2.Profit, 0) as ProfitLastMonth

FROM MonthlyProfit as t1

LEFT JOIN MonthlyProfit as t2 on (t1.yearid = t2.yearid) AND (t1.Monthid = t2.Monthid+1)

 

Pero ahora tenemos acceso a las funciones LAG (la fila que viene antes) y LEAD (la fila que viene después), esto es, el registro anterior y el registro siguiente, obviamente agrupando y ordenando por lo que se precise:

 

SELECT

yearid,

monthid,

Profit as ProfitThisMonth,

LAG(Profit, 1, 0) OVER(Order by yearid, monthid) as ProfitLastMonth

FROM MonthlyProfit

 

Es decir, dame el campo Profit, pero 1 registro antes, y si no existe, le ponemos un 0. Se puede ver detalle de la sintaxis en:

    LAG: http://msdn.microsoft.com/en-us/library/hh231256%28v=sql.110%29.aspx

    LEAD: http://msdn.microsoft.com/en-us/library/hh213125(v=sql.110).aspx

Y encima, rinde estupendamente, mejor que cruzando la tabla consigo misma, con lo que no habrá que restringir su uso a las demos. Menos lecturas que la consulta del running total:

 

(12 row(s) affected)

Table 'MonthlyProfit'. Scan count 2, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(12 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MonthlyProfit'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Ahora bien, el observador atento habrá notado que estamos hablando del registro anterior o siguiente dentro del recordset que se devuelve. Entonces, si yo únicamente quiero los datos de un mes, pero que también me salga el dato del mes anterior en la misma línea, ¿cómo lo hago, ya que filtré otras líneas del conjunto de registros? Bueno, la respuesta es simple, con una cte que nos devuelva previamente los registros que se precisen y luego filtrándola, es decir, con una subconsulta:

 

;with cte as (

SELECT

yearid,

monthid,

Profit as ProfitThisMonth,

LAG(Profit, 1, 0) OVER(Order by yearid, monthid) as ProfitLastMonth

FROM MonthlyProfit

)

select * from cte

where yearid = 2010 and monthid = 8

 

Al contrario de lo que podría parecer, el rendimiento no se ve penalizado por esta acción, ya que no hay que leer dos veces, sólo una vez (comparando de nuevo con la sentencia clásica con el mismo filtro aplicado).

 

(1 row(s) affected)

Table 'MonthlyProfit'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MonthlyProfit'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

En resumidas cuentas, es seguro que nadie podrá decidir realizar una migración por este par de funciones, pero ello no quita para que, en caso de disponer de la nueva versión, las podamos disfrutar desde el primer día. Y vale para ir al registro anterior o a 15 más atrás, claro está, con lo que permitirá cubrir un buen número de casos más.

 

(Cito la fuente en la que me basé para los ejemplos de este post: http://www.sqlservercentral.com/blogs/sqlservernotesfromthefield/archive/2011/08/02/lag_1920_n_1920_lead-_1320_-new-t_2D00_sql-features-in-sql-server-denali-ctp3.aspx).

 

 

Comentarios

No hay ningún comentario

Añadir un Comentario: