LEAD (Transact-SQL)
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punto de conexión de análisis SQL en Microsoft FabricAlmacenamiento en Microsoft Fabric
Tiene acceso a datos de una fila posterior en el mismo conjunto de resultados sin usar una autocombinación que empieza por SQL Server 2012 (11.x). LEAD proporciona acceso a una fila en un desplazamiento físico determinado que sigue a la fila actual. Use esta función analítica en una SELECT instrucción para comparar los valores de la fila actual con valores de una fila siguiente.
Convenciones de sintaxis de Transact-SQL
Sintaxis
LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Nota:
Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.
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 valor único (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 bigint. offset no puede ser un valor negativo ni una función analítica.
default
Valor que se devuelve cuando offset está fuera del ámbito de la partición. Si no se especifica un valor predeterminado, NULL se devuelve. el valor predeterminado 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.
[ IGNORE NULLS | RESPECT NULLS ]
Se aplica a: SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database, Azure SQL Instancia administrada, Azure SQL Edge
IGNORE NULLS : se omiten NULL los valores del conjunto de datos al calcular el primer valor en una partición.
RESPECT NULLS : respeta los NULL valores del conjunto de datos al calcular el primer valor en una partición. RESPECT NULLS es el comportamiento predeterminado si no se especifica una NULLS opción.
Se ha corregido un error en SQL Server 2022 CU4 relacionado con IGNORE NULLS en LAG y LEAD.
Para más información sobre este argumento en Azure SQL Edge, consulte Imputación de valores que faltan.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause divide el conjunto de resultados generado por la
FROMcláusula 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. order_by_clause es obligatorio. Para obtener más información, vea SELECT - OVER Clause.
Tipos de valores devueltos
El tipo de datos de la scalar_expression especificada. NULLse devuelve si scalar_expression admite valores NULL o el valor predeterminado se establece en NULL .
LEAD sea no determinista. Para obtener más información, consulte Deterministic and Nondeterministic Functions.
Ejemplos
Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022, que se puede descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.
A Comparar valores entre años
La consulta usa la LEAD función para devolver la diferencia en las cuotas de ventas de un empleado específico durante los años posteriores. Dado que no hay ningún valor de cliente potencial disponible para la última fila, se devuelve el valor predeterminado de cero (0).
USE AdventureWorks2022;
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 LEAD función para comparar las ventas de año a fecha entre los empleados. La PARTITION BY cláusula se especifica para particionar las filas del conjunto de resultados por territorio de ventas. La LEAD función se aplica a cada partición por separado y se reinicia el cálculo para cada partición. La ORDER BY cláusula especificada en la OVER cláusula ordena las filas de cada partición antes de aplicar la función. La ORDER BY cláusula de la SELECT instrucción ordena las filas del conjunto de resultados completo. Dado que no hay ningún valor de cliente potencial disponible para la última fila de cada partición, se devuelve el valor predeterminado de cero (0).
USE AdventureWorks2022;
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 varias expresiones arbitrarias e ignorar NULL valores en la sintaxis de la LEAD función.
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) IGNORE NULLS OVER (ORDER BY a) AS i
FROM T;
El conjunto de resultados es el siguiente:
b c i
----------- ----------- -----------
1 5 -2
2 NULL NULL
3 1 0
1 NULL 2
2 4 2
1 -3 8
D. Use IGNORE NULLS para buscar valores que no son NULL
La siguiente consulta de ejemplo muestra el uso del IGNORE NULLS argumento .
El IGNORE NULLS argumento se usa tanto con LAGLEAD como para demostrar la sustitución de valores para valores anteriores o distintos de NULL NULL.
- Si la fila anterior contiene
NULLLAG, la fila actual usa el valor distinto de más recienteNULL. - Si la fila siguiente contiene con
NULLLEAD, la fila actual usa el siguiente valor no disponibleNULL.
DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO
INSERT INTO #test_ignore_nulls VALUES
(1, 8),
(2, 9),
(3, NULL),
(4, 10),
(5, NULL),
(6, NULL),
(7, 11);
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) IGNORE NULLS OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) IGNORE NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--cleanup
DROP TABLE #test_ignore_nulls;
column_a column_b Previous value for column_b Next value for column_b
------------ ----------- ------------------------------ ------------------------
1 8 NULL 9
2 9 8 10
3 NULL 9 10
4 10 9 11
5 NULL 10 11
6 NULL 10 11
7 11 10 NULL
E. Uso de RESPECT NULLS para mantener NULL los valores
La siguiente consulta de ejemplo muestra el uso del RESPECT NULLS argumento , que es el comportamiento predeterminado si no se especifica, en lugar del IGNORE NULLS argumento del ejemplo anterior.
- Si la fila anterior contiene
NULLLAG, la fila actual usa el valor más reciente. - Si la fila siguiente contiene con
NULLLEAD, la fila actual usa el siguiente valor.
DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO
INSERT INTO #test_ignore_nulls VALUES
(1, 8),
(2, 9),
(3, NULL),
(4, 10),
(5, NULL),
(6, NULL),
(7, 11);
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) RESPECT NULLS OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) RESPECT NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--Identical output
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--cleanup
DROP TABLE #test_ignore_nulls;
column_a column_b Previous value for column_b Next value for column_b
1 8 NULL 9
2 9 8 NULL
3 NULL 9 10
4 10 NULL NULL
5 NULL 10 NULL
6 NULL NULL 11
7 11 NULL NULL
Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)
A Comparar valores entre trimestres
En el ejemplo siguiente se muestra la LEAD función . La consulta obtiene la diferencia en los valores de cuota de ventas para un determinado empleado durante trimestres naturales consecutivos. Dado que no hay ningún valor de cliente potencial disponible después de la última fila, se usa el valor predeterminado de cero (0).
-- Uses AdventureWorks
SELECT CalendarYear AS Year,
CalendarQuarter AS Quarter,
SalesAmountQuota AS SalesQuota,
LEAD(SalesAmountQuota, 1, 0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
SalesAmountQuota - LEAD(SalesAmountQuota, 1, 0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001, 2002)
ORDER BY CalendarYear, CalendarQuarter;
El conjunto de resultados es el siguiente:
Year Quarter SalesQuota NextQuota Diff
---- ------- ---------- --------- -------------
2001 3 28000.0000 7000.0000 21000.0000
2001 4 7000.0000 91000.0000 -84000.0000
2001 1 91000.0000 140000.0000 -49000.0000
2002 2 140000.0000 7000.0000 7000.0000
2002 3 7000.0000 154000.0000 84000.0000
2002 4 154000.0000 0.0000 154000.0000
Contenido relacionado
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de