GAL (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)ponto de extremidade de análise SQL no Microsoft FabricWarehouse no Microsoft Fabric
Acessa dados de uma linha anterior no mesmo conjunto de resultados sem o uso de uma associação automática a partir do SQL Server 2012 (11.x). O LAG fornece acesso a uma linha em um determinado deslocamento físico que vem antes da 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.
Transact-SQL convenções de sintaxe
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 de volta da linha atual a partir da qual obter um valor. Se não for especificado, o padrão será 1.
de deslocamento pode ser uma coluna, subconsulta ou outra expressão avaliada como um inteiro positivo ou pode ser implicitamente convertida em bigint.
de deslocamento não pode ser um valor negativo ou uma função analítica.
padrão
O valor a ser retornado quando de deslocamento está além do escopo da partição. Se um valor padrão não for especificado, NULL será retornado.
padrão pode ser uma coluna, subconsulta ou outra expressão, mas não pode ser uma função analítica.
padrão deve ser compatível com scalar_expression.
[ IGNORAR NULOS | RESPEITO NULADOS ]
Aplica-se a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure, Instância Gerenciada do SQL do Azure e SQL Edge do Azure
IGNORE NULLS - Ignore valores nulos no conjunto de dados ao calcular o primeiro valor em uma partição.
RESPECT NULLS - Respeita valores nulos no conjunto de dados ao calcular o primeiro valor em uma partição.
RESPECT NULLS é o comportamento padrão se uma opção NULLS não for especificada.
Houve uma correção de bug de no SQL Server 2022 CU4 relacionado a IGNORE NULLS no LAG e LEAD.
Para obter mais informações sobre esse argumento no Azure SQL Edge, consulte Imputando valores ausentes.
MAIS ( [ 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 especificada, a função trata todas as linhas do conjunto de resultados da consulta como um único grupo. order_by_clause determina a ordem dos dados antes que a função seja aplicada. Se partition_by_clause for especificado, ele determina a ordem dos dados na partição. O order_by_clause é obrigatório. Para obter mais informações, consulte Cláusula OVER (Transact-SQL).
Tipos de devolução
O tipo de dados do scalar_expressionespecificado . NULL será retornado se scalar_expression for anulável ou padrão estiver definido como NULL.
Observações gerais
O GAL não é determinístico. Para obter mais informações, consulte Deterministic and Nondeterministic Functions.
Exemplos
Um. Comparar valores entre anos
O exemplo a seguir usa a função LAG para retornar a diferença nas cotas de vendas de um funcionário específico em relação aos anos anteriores. Observe que, como não há nenhum valor de atraso 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');
Aqui está 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 GAL para comparar as vendas do ano até o momento entre funcionários. A cláusula PARTITION BY é especificada para dividir as linhas no resultado definido por território de vendas. A função LAG é aplicada a cada partição separadamente e o cálculo é reiniciado 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á nenhum valor de atraso 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;
Aqui está 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 a especificação de várias expressões arbitrárias e ignorando 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;
Aqui está 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 não NULL
A consulta de exemplo a seguir demonstra o uso do argumento IGNORE NULLS.
O argumento IGNORE NULLS é usado com LAG e
- Se a linha anterior continha NULL com
LAG, a linha atual usa o valor não-NULL mais recente. - Se a próxima linha contiver um NULL com
LEAD, a linha atual usará o próximo valor não-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 continha NULL com
LAG, a linha atual usa 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 and Analytics Platform System (PDW)
Um. 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 nas cotas de vendas de um funcionário específico em relação aos trimestres civis anteriores. Observe que, como não há nenhum valor de atraso 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;
Aqui está 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