적용 대상:
SQL 서버Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric 의 SQL 분석 엔드포인트Microsoft Fabric 의 웨어하우스Microsoft Fabric의 SQL 데이터베이스
SQL Server 2012(11.x)부터 자체 조인을 사용하지 않고 동일한 결과 집합에 있는 이전 행의 데이터에 액세스합니다. LAG 함수를 사용하면 현재 행 앞에 나오는, 지정한 실제 오프셋에 있는 행에 액세스할 수 있습니다. SELECT 문에서 이 분석 함수를 사용하여 현재 행의 값을 이전 행의 값과 비교할 수 있습니다.
구문
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
인수
scalar_expression
지정한 오프셋에 따라 반환할 값입니다. 단일(스칼라) 값을 반환하는 모든 유형의 식입니다. scalar_expression은 분석 함수일 수 없습니다.
offset
현재 행 앞에 있는 행의 수로, 그 수만큼 앞에 있는 행에서 값을 가져옵니다. 이 인수를 지정하지 않으면 기본값은 1입니다.
offset은 열, 하위 쿼리 또는 양의 정수로 계산되거나 암시적으로 bigint로 변환될 수 있는 기타 식일 수 있습니다.
offset은 음수 또는 분석 함수일 수 없습니다.
default
오프셋이 파티션의 범위를 벗어날 때 반환할 값입니다. 기본값이 지정되어 있지 않으면 NULL이 반환됩니다.
default는 열, 하위 쿼리 또는 기타 식일 수 있지만 분석 함수일 수는 없습니다.
default는 scalar_expression과 호환되는 형식이어야 합니다.
[ 무시함 | 존중은 무효입니다 ]
적용: SQL Server 2022(16.x) 이상 버전, Azure SQL Database, Azure SQL Managed Instance 및 Azure SQL Edge
IGNORE NULLS - 파티션의 첫 번째 값을 컴퓨팅할 때 데이터 세트의 null 값을 무시합니다.
RESPECT NULLS - 파티션의 첫 번째 값을 컴퓨팅할 때 데이터 세트의 null 값을 고려합니다.
RESPECT NULLS 은 NULLS 옵션을 지정하지 않은 경우의 기본 동작입니다.
SQL Server 2022 CU4에서 IGNORE NULLS LAG 와 관련된 버그 수정이 있었습니다LEAD.
Azure SQL Edge에서 이 인수에 대한 자세한 내용은 누락된 값의 대체를 참조 하세요.
오버 ([ partition_by_clause ] order_by_clause )
partition_by_clause는 FROM 절이 생성한 결과 집합을 함수가 적용되는 파티션으로 나눕니다. 지정하지 않을 경우 쿼리 결과 집합의 모든 행이 단일 그룹으로 취급됩니다. order_by_clause는 함수를 적용하기 전에 데이터의 순서를 결정합니다. partition_by_clause가 지정되면 파티션의 데이터 순서가 결정됩니다. order_by_clause가 필요합니다. 자세한 내용은 OVER 절(Transact-SQL)을 참조하세요.
반환 형식
지정한 scalar_expression의 데이터 형식입니다. scalar_expression이 Null 값을 허용하거나 default가 NULL로 설정되어 있으면 NULL이 반환됩니다.
일반적인 주의 사항
LAG는 비결정적입니다. 자세한 내용은 Deterministic and Nondeterministic Functions을 참조하세요.
예제
A. 연도 간 값 비교
다음 예에서는 LAG 함수를 사용하여 특정 직원의 전년도 대비 판매 할당량 차이를 반환합니다. 첫 번째 행의 경우 앞에 나오는 값이 없으므로 기본값(0)이 반환됩니다.
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');
결과 집합은 다음과 같습니다.
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. 파티션 내의 값 비교
다음 예에서는 LAG 함수를 사용하여 직원별 연간 누계 매출을 비교합니다. 결과 집합의 행을 판매 지역별로 나누기 위해 PARTITION BY 절이 지정되었습니다. LAG 함수는 각 파티션에 별도로 적용되고 각 파티션에 대해 계산이 다시 시작됩니다. OVER 절에서 ORDER BY 절은 각 파티션의 행을 정렬합니다. SELECT 문의 ORDER BY 절은 전체 결과 집합의 행을 정렬합니다. 각 파티션에 있는 첫 번째 행의 경우 앞에 나오는 값이 없으므로 기본값(0)이 반환됩니다.
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;
결과 집합은 다음과 같습니다.
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. 임의의 식 지정
다음 예제에서는 다양한 임의 식을 지정하고 LAG 함수 구문에서 NULL 값을 무시하는 방법을 보여 줍니다.
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;
결과 집합은 다음과 같습니다.
b c i
----------- ----------- -----------
1 -3 1
2 4 -2
1 NULL 8
3 1 -6
2 NULL 8
1 5 2
D. IGNORE NULLS를 사용하여 NULL이 아닌 값 찾기
다음 샘플 쿼리는 IGNORE NULLS 인수를 사용하는 방법을 보여 줍니다.
IGNORE NULLS 인수는 LAG 및 LEAD와 함께 사용되어 이전 또는 다음 NULL이 아닌 값에 대한 NULL 값의 대체를 보여 줍니다.
- 앞의 행에 NULL
LAG이 포함된 경우 현재 행은 NULL이 아닌 가장 최근의 값을 사용합니다. - 다음 행에 NULL
LEAD이 포함된 경우 현재 행은 사용 가능한 다음 NULL이 아닌 값을 사용합니다.
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. RESPECT NULLS를 사용하여 NULL 값 유지
다음 샘플 쿼리에서는 이전 예제의 IGNORE NULLS 인수와 달리 지정하지 않은 경우 기본 동작인 RESPECT NULLS 인수를 사용하는 방법을 보여 줍니다.
- 앞의 행에 NULL
LAG이 포함된 경우 현재 행은 가장 최근 값을 사용합니다. - 다음 행에 NULL
LEAD이 포함된 경우 현재 행은 다음 값을 사용합니다.
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
예제: Azure Synapse Analytics
A. 분기 간 값 비교
다음 예에서는 LAG 함수를 보여 줍니다. 다음 쿼리에서는 LAG 함수를 사용하여 특정 직원의 이전 분기 대비 판매 할당량 차이를 반환합니다. 첫 번째 행의 경우 앞에 나오는 값이 없으므로 기본값(0)이 반환됩니다.
-- 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;
결과 집합은 다음과 같습니다.
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