LAG (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Ponto de extremidade de análise do SQLWarehouse no Microsoft Fabric
Acessa os dados de uma linha anterior no mesmo conjunto de resultados sem usar uma autojunção começando com SQL Server 2012 (11.x). LAG fornece acesso a uma linha a um determinado deslocamento físico que antecede 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 anterior.
Convenções de sintaxe de Transact-SQL
Sintaxe
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
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 atrás 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 nem uma função analítica.
default
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 e SQL do Azure no Edge
IGNORE NULLS – Ignore os valores nulos no conjunto de dados quando computar o primeiro valor em uma partição.
RESPECT NULLS – Respeite os valores nulos no conjunto de dados quando computar o primeiro valor em uma partição.
RESPECT NULLS é o comportamento padrão se uma opção NULL não está especificada.
Houve uma correção de bug no SQL Server 2022 CU4 relacionada a IGNORE NULLS em 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 cláusula FROM 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. Se partition_by_clause for especificado, ela determinará a ordem dos dados na partição. order_by_clause é obrigatória. Para obter mais informações, confira Cláusula OVER (Transact-SQL).
Tipos de retorno
O tipo de dados da scalar_expression especificada. NULL será retornado se scalar_expression não permitir valor nulo ou default for definido como NULL.
Comentários gerais
LAG é não determinística. Para obter mais informações, veja Funções determinísticas e não determinísticas.
Exemplos
a. Comparar valores entre anos
O exemplo a seguir usa a função LAG para retornar a diferença em cotas de vendas para um funcionário específico nos anos anteriores. Observe que, como não há um valor de retardo disponível para a primeira linha, o padrão de zero (0) é retornado.
USE AdventureWorks2022;
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');
Veja a seguir o conjunto de resultados.
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 partições
O exemplo a seguir usa a função LAG para comparar as vendas no ano até o momento entre funcionários. A cláusula PARTITION BY é especificada para dividir as linhas no conjunto de resultados por território de vendas. A função LAG é aplicada separadamente a cada partição e a computação é reiniciada para cada partição. A cláusula ORDER BY na cláusula OVER ordena as linhas em cada partição. A cláusula ORDER BY na instrução SELECT classifica as linhas em todo o conjunto de resultados. Observe que, como não há um valor de retardo disponível para a primeira linha de cada partição, o padrão de zero (0) é retornado.
USE AdventureWorks2022;
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;
Veja a seguir o conjunto de resultados.
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. Especificando expressões arbitrárias
O exemplo a seguir demonstra como especificar várias expressões arbitrárias e ignorar valores NULL na sintaxe da função 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) IGNORE NULLS OVER (ORDER BY a) AS i
FROM T;
Veja a seguir o conjunto de resultados.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. Use IGNORE NULLS para localizar valores diferentes de NULL
A consulta de exemplo a seguir demonstra o uso do argumento IGNORE NULLS.
O argumento IGNORE NULLS é usado com LAG e LEAD para demonstrar a substituição de valores NULL por valores anteriores ou próximos diferentes de NULL.
- Se a linha anterior contiver NULL com
LAG, a linha atual usará o valor diferente de NULL mais recente. - Se a próxima linha contiver um NULL com
LEAD, a linha atual usará o próximo valor diferente de NULL 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 valores NULL
A consulta de exemplo a seguir demonstra o uso do argumento RESPECT NULLS, que é o comportamento padrão se não for especificado, em oposição ao argumento IGNORE NULLS no exemplo anterior.
- Se a linha anterior contiver NULL com
LAG, a linha atual usará o valor mais recente. - Se a próxima linha contiver um NULL com
LEAD, 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)
a. Comparar valores entre trimestres
O exemplo a seguir demonstra a função LAG. A consulta usa a função LAG para retornar a diferença em cotas de vendas para um funcionário específico nos trimestres civis anteriores. Observe que, como não há um valor de retardo disponível para a primeira linha, o padrão de zero (0) é retornado.
-- Uses AdventureWorks
SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,
LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,
SalesAmountQuota - LAG(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;
Veja a seguir o conjunto de resultados.
Year Quarter SalesQuota PrevQuota Diff
---- ------- ---------- --------- -------------
2001 3 28000.0000 0.0000 28000.0000
2001 4 7000.0000 28000.0000 -21000.0000
2001 1 91000.0000 7000.0000 84000.0000
2002 2 140000.0000 91000.0000 49000.0000
2002 3 7000.0000 140000.0000 -70000.0000
2002 4 154000.0000 7000.0000 84000.0000