LAG (Transact-SQL)

Tiene acceso a datos de una fila anterior en el mismo conjunto de resultados sin el uso de una autocombinación en SQL Server 2012. LAG proporciona acceso a una fila en un desplazamiento físico especificado que hay antes de la fila actual. Use esta función analítica en una instrucción SELECT para comparar valores de la fila actual con valores de una fila anterior.

Convenciones de sintaxis de Transact-SQL (Transact-SQL)

Sintaxis

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Argumentos

• scalar_expression
  El valor que se va a devolver en función del desplazamiento especificado. Es una expresión de cualquier tipo que devuelve un único valor (escalar). scalar_expression no puede ser una función analítica.

• offset
  El número de filas hacia atrás de la fila actual de la que se va a obtener un valor. Si no se especifica, el valor predeterminado es 1. offset puede ser una columna, una subconsulta u otra expresión que se evalúa como un entero positivo o que se puede convertir implícitamente en un tipo bigint. offset no puede ser un valor negativo o una función analítica.

• default
  El valor que se va a devolver si scalar_expression en offset es NULL. Si no se especifica ningún valor predeterminado, se devuelve NULL. default puede ser una columna, una subconsulta u otra expresión, pero no puede ser una función analítica. default debe tener un tipo compatible con scalar_expression.

• OVER ( [ partition_by_clause ] order_by_clause**)**
  partition_by_clause divide el conjunto de resultados generado por la cláusula FROM en particiones a las que se aplica la función. Si no se especifica, la función trata todas las filas del conjunto de resultados de la consulta como un único grupo. order_by_clause determina el orden de los datos antes de que se aplique la función. Si se especifica partition_by_clause, determina el orden de los datos de la partición. Se necesita order_by_clause. Para obtener más información, vea OVER (cláusula de Transact-SQL).

Tipos de valor devuelto

El tipo de datos del parámetro scalar_expression especificado. Se devuelve NULL si scalar_expression admite valores NULL o default se establece en NULL.

Ejemplos

A.Comparar valores entre años

En el ejemplo siguiente se usa la función LAG para devolver la diferencia en cuotas de venta para un empleado concreto en años anteriores. Observe que como no hay ningún valor de intervalo disponible para la primera fila, se devuelve el valor predeterminado de cero (0).

USE AdventureWorks2012;
GO
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');

El conjunto de resultados es el siguiente.

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

B.Comparar valores dentro de particiones

En el ejemplo siguiente se usa la función LAG para comparar las ventas anuales hasta la fecha entre los empleados. La cláusula PARTITION BY se especifica para dividir las filas del conjunto de resultados por territorio de ventas. La función LAG se aplica a cada partición por separado y el cálculo se reinicia para cada partición. La cláusula ORDER BY de la cláusula OVER ordena las filas de cada partición. La cláusula ORDER BY de la instrucción SELECT ordena las filas del conjunto de resultados completo. Observe que como no hay ningún valor de intervalo disponible para la primera fila de cada partición, se devuelve el valor predeterminado de cero (0).

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD, 
       LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada') 
ORDER BY TerritoryName;

El conjunto de resultados es el siguiente.

TerritoryName            BusinessEntityID SalesYTD              PrevRepSales
-----------------------  ---------------- --------------------- ---------------------
Canada                   282              2604540.7172          0.00
Canada                   278              1453719.4653          2604540.7172
Northwest                284              1576562.1966          0.00
Northwest                283              1573012.9383          1576562.1966
Northwest                280              1352577.1325          1573012.9383

C.Especificar expresiones arbitrarias

En el ejemplo siguiente se muestra cómo especificar una serie de expresiones arbitrarias en la sintaxis de la función LAG.

CREATE TABLE T (a int, b int, c int); 
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5); 

SELECT b, c, 
    LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

El conjunto de resultados es el siguiente.

b           c           i
----------- ----------- -----------
1           -3          1
2           4           -2
1           NULL        8
3           1           -6
2           NULL        NULL
1           5           NULL

Vea también

Referencia

LEAD (Transact-SQL)