LEAD (Transact-SQL)
Tiene acceso a datos de una fila posterior en el mismo conjunto de resultados sin el uso de una autocombinación en SQL Server 2012. LEAD proporciona acceso a una fila en un desplazamiento físico especificado que hay después 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 posterior.
.gif "Icono de vínculo a temas")
Convenciones de sintaxis de Transact-SQL (Transact-SQL)
Sintaxis
LEAD ( 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 delante 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. Cuando se especifica partition_by_clause, determina el orden de los datos en cada 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
La consulta usa la función LEAD para devolver la diferencia en cuotas de venta para un empleado específico durante años posteriores. Observe que como no hay ningún valor inicial disponible para la última fila, se devuelve el valor predeterminado de cero (0).
USE AdventureWorks2012;
GO
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');
El conjunto de resultados es el siguiente.
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
B.Comparar valores dentro de particiones
En el ejemplo siguiente se usa la función LEAD para comparar las ventas anuales hasta la fecha entre los empleados. La cláusula PARTITION BY se especifica para crear particiones de las filas del conjunto de resultados por territorio de ventas. La función LEAD se aplica a cada partición por separado y el cálculo se reinicia para cada partición. La cláusula ORDER BY especificada en la cláusula OVER ordena las filas de cada partición antes de que se aplique la funció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 inicial disponible para la última fila de cada partición, se devuelve el valor predeterminado de cero (0).
USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;
El conjunto de resultados es el siguiente.
TerritoryName BusinessEntityID SalesYTD NextRepSales
----------------------- ---------------- --------------------- ---------------------
Canada 282 2604540.7172 1453719.4653
Canada 278 1453719.4653 0.00
Northwest 284 1576562.1966 1573012.9383
Northwest 283 1573012.9383 1352577.1325
Northwest 280 1352577.1325 0.00
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 LEAD.
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,
LEAD(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 8
2 4 2
1 NULL 2
3 1 0
2 NULL NULL
1 5 -2