LEAD (Transact-SQL)
Aplica-se a: 
SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System) Ponto de extremidade de análise de SQL no Microsoft Fabric Warehouse no Microsoft Fabric
Acessa os dados de uma linha seguinte no mesmo conjunto de resultados sem o uso de uma autojunção começando pelo SQL Server 2012 (11.x). LEAD fornece acesso a uma linha em um determinado deslocamento físico que segue a linha atual. Use essa função analítica em uma SELECT instrução para comparar valores na linha atual com valores em uma linha seguinte.
Convenções de sintaxe de Transact-SQL
Sintaxe
LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Observação
Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.
Argumentos
scalar_expression
O valor a ser retornado com base no deslocamento especificado. É uma expressão de qualquer tipo que retorna um único valor (escalar). scalar_expression não pode ser uma função analítica.
offset
O número de linhas à frente da linha atual da qual obter um valor. Se não for especificado, o padrão será 1. offset pode ser uma coluna, subconsulta ou outra expressão avaliada para um inteiro positivo ou pode ser convertida implicitamente em bigint. Offset não pode ser um valor negativo ou uma função analítica.
padrão
O valor a ser retornado quando offset estiver além do escopo da partição. Se um valor padrão não for especificado, NULL será retornado. default pode ser uma coluna, subconsulta ou outra expressão, mas não pode ser uma função analítica. default deve ter o tipo compatível com scalar_expression.
[ IGNORE NULLS | RESPECT NULLS ]
Aplica-se a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure, SQL do Azure no Edge
IGNORE NULLS - Ignore NULL os valores no conjunto de dados ao calcular o primeiro valor em uma partição.
RESPECT NULLS - Respeite NULL os valores no conjunto de dados ao calcular o primeiro valor em uma partição. RESPECT NULLS é o comportamento padrão se uma NULLS opção não for especificada.
Houve uma correção de bug no SQL Server 2022 CU4 relacionada a IGNORE NULLS in LAG e LEAD.
Para obter mais informações sobre esse argumento no SQL do Azure no Edge, consulte Imputando valores ausentes.
OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause divide o conjunto de resultados produzido pela
FROMcláusula em partições às quais a função é aplicada. Se não for especificado, a função tratará todas as linhas do conjunto de resultados da consulta como um único grupo.order_by_clause determina a ordem dos dados antes de a função ser aplicada.
Quando partition_by_clause é especificado, ela determina a ordem dos dados em cada partição. order_by_clause é obrigatória. Para obter mais informações, consulte Cláusula SELECT - OVER.
Tipos de retorno
O tipo de dados da scalar_expression especificada. NULL será retornado se scalar_expression for anulável ou o padrão for definido como NULL.
LEAD é não determinístico. Para obter mais informações, veja Funções determinísticas e não determinísticas.
Exemplos
Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.
R. Comparar valores entre anos
A consulta usa a LEAD função para retornar a diferença nas cotas de vendas de um funcionário específico nos anos subsequentes. Como não há nenhum valor de lead disponível para a última linha, o padrão de zero (0) é retornado.
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');
Este é o conjunto de resultados.
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 partições
O exemplo a seguir usa a função para comparar as vendas acumuladas LEAD no ano entre os funcionários. A PARTITION BY cláusula é especificada para particionar as linhas no conjunto de resultados por região de vendas. A LEAD função é aplicada a cada partição separadamente e a computação é reiniciada para cada partição. A ORDER BY cláusula especificada na OVER cláusula ordena as linhas em cada partição antes que a função seja aplicada. A ORDER BY cláusula na SELECT instrução ordena as linhas em todo o conjunto de resultados. Como não há nenhum valor inicial disponível para a última linha de cada partição, o padrão de zero (0) é retornado.
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;
Este é o conjunto de resultados.
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 expressões arbitrárias
O exemplo a seguir demonstra a especificação de várias expressões arbitrárias e a ignorância de NULL valores na sintaxe da LEAD função.
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;
Este é o conjunto de resultados.
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 localizar valores diferentes de NULL
A consulta de exemplo a seguir demonstra o uso do IGNORE NULLS argumento.
O IGNORE NULLS argumento é usado com LAG e LEAD para demonstrar a substituição de NULL valores para valores não NULL anteriores ou seguintes.
- Se a linha anterior contiver
NULLcomLAG, a linha atual usará o valor nãoNULLmais recente. - Se a próxima linha contiver um
NULLcomLEAD, a linha atual usará o próximo nãoNULLvalor disponível.
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. Use RESPECT NULLS para manter NULL valores
A consulta de exemplo a seguir demonstra o uso do RESPECT NULLS argumento, que é o comportamento padrão se não for especificado, em oposição ao IGNORE NULLS argumento no exemplo anterior.
- Se a linha anterior contiver
NULLcomLAG, a linha atual usará o valor mais recente. - Se a próxima linha contiver um
NULLwithLEAD, a linha atual usará o próximo 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
Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)
R. Comparar valores entre trimestres
O exemplo a seguir demonstra a LEAD função. A consulta obtém a diferença em valores de cota de vendas para um funcionário especificado nos trimestres do calendário seguintes. Como não há valor de lead disponível após a última linha, o padrão de zero (0) é usado.
-- 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;
Este é o conjunto de resultados.
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
Conteúdo relacionado
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de