LEAD(Transact-SQL)
SQL Server 2012에서 자체 조인을 사용하지 않고 동일한 결과 집합에 있는 다음 행의 데이터에 액세스합니다. LEAD 함수를 사용하면 현재 행 뒤에 나오는, 지정한 실제 오프셋에 있는 행에 액세스할 수 있습니다. SELECT 문에서 이 분석 함수를 사용하여 현재 행의 값을 다음 행의 값과 비교할 수 있습니다.
.gif "항목 링크 아이콘")
Transact-SQL 구문 표기 규칙(Transact-SQL)
구문
LEAD ( scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] order_by_clause )
인수
scalar_expression
지정한 오프셋에 따라 반환할 값입니다. 단일(스칼라) 값을 반환하는 모든 유형의 식입니다. scalar_expression은 분석 함수일 수 없습니다.offset
현재 행 뒤에 있는 행의 수로, 그 수만큼 뒤에 있는 행에서 값을 가져옵니다. 이 인수를 지정하지 않으면 기본값은 1입니다. offset은 열, 하위 쿼리 또는 양의 정수로 계산되거나 암시적으로 bigint로 변환될 수 있는 기타 식일 수 있습니다. offset은 음수 또는 분석 함수일 수 없습니다.default
offset의 scalar_expression이 NULL일 경우 반환할 값입니다. 기본값이 지정되어 있지 않으면 NULL이 반환됩니다. default는 열, 하위 쿼리 또는 기타 식일 수 있지만 분석 함수일 수는 없습니다. default는 scalar_expression과 호환되는 형식이어야 합니다.OVER ( [ 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이 반환됩니다.
예
1.연도 간 값 비교
다음 쿼리에서는 LEAD 함수를 사용하여 특정 직원의 연도별 판매 할당량 간 차이를 반환합니다. 마지막 행의 경우 뒤에 나오는 값이 없으므로 기본값(0)이 반환됩니다.
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');
결과 집합은 다음과 같습니다.
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
2.파티션 내의 값 비교
다음 예에서는 LEAD 함수를 사용하여 직원별 연간 누계 매출을 비교합니다. 결과 집합의 행을 판매 지역별로 분할하기 위해 PARTITION BY 절이 지정되었습니다. LEAD 함수는 각 파티션에 별도로 적용되고 각 파티션에 대해 계산이 다시 시작됩니다. OVER 절에 지정된 ORDER BY 절은 함수를 적용하기 전에 각 파티션의 행을 정렬합니다. SELECT 문의 ORDER BY 절은 전체 결과 집합의 행을 정렬합니다. 각 파티션에 있는 마지막 행의 경우 뒤에 나오는 값이 없으므로 기본값(0)이 반환됩니다.
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;
결과 집합은 다음과 같습니다.
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
3.임의의 식 지정
다음 예에서는 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;
결과 집합은 다음과 같습니다.
b c i
----------- ----------- -----------
1 -3 8
2 4 2
1 NULL 2
3 1 0
2 NULL NULL
1 5 -2