LEAD (Transact-SQL)

Acessa os dados de uma linha subsequente no mesmo conjunto de resultados sem o uso de uma autojunção no SQL Server 2014. LEAD fornece acesso a uma linha a um determinado deslocamento físico que segue a linha atual. Use essa função analítica em uma instrução SELECT para comparar valores na linha atual com valores em uma linha seguinte.

Aplica-se a: SQL Server (do SQL Server 2012 à versão atual).

convenções de sintaxe Transact-SQL (Transact-SQL)

Sintaxe

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

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 que avalia 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.

• default
  O valor a ser retornado quando scalar_expression a offset é NULL. 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 ser de tipo compatível com scalar_expression.

• OVER ( [ partition_by_clause ] order_by_clause**)**
  partition_by_clause divide o conjunto de resultados produzido pela cláusula FROM em partições nas 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, determina a ordem dos dados em cada partição. A order_by_clause é obrigatória. Para obter mais informações, consulte Cláusula OVER (Transact-SQL).

Tipos de retorno

O tipo de dados de scalar_expression especificado. NULL será retornado se scalar_expression permitir valor nulo ou default for definido como NULL.

LEAD é não determinística. Para obter mais informações, consulte Funções determinísticas e não determinísticas.

Exemplos

A.Comparar valores entre anos

A consulta usa a função LEAD para retornar a diferença em cotas de vendas para um funcionário específico nos anos subsequentes. Observe que, como não há um valor inicial disponível para a última linha, o padrão de zero (0) é retornado.

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');

Aqui está 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 LEAD para comparar as vendas no ano até o momento entre funcionários. A cláusula PARTITION BY é especificada para particionar as linhas no conjunto de resultados por território de vendas. A função LEAD é aplicada separadamente a cada partição e a computação é reiniciada para cada partição. A cláusula ORDER BY especificada na cláusula OVER ordena as linhas em cada partição antes de a função ser aplicada. A cláusula ORDER BY na instrução SELECT ordena as linhas em todo o conjunto de resultados. Observe que, como não há um valor inicial disponível para a última linha de cada partição, o padrão de zero (0) é retornado.

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;

Aqui está 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.Especificando expressões arbitrárias

O exemplo a seguir demonstra como especificar uma variedade de expressões arbitrárias na sintaxe da função 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;

Aqui está o conjunto de resultados.

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

Consulte também

Referência

LAG (Transact-SQL)