[MSSQL] 결과집합의 이전 행과 다음 행 데이터를 가져오는 LAG, LEAD 함수

출처: https://windtrap.tistory.com/95 [곰탱이의 개인블로그]

 

[SQL2012] 결과집합의 이전 행과 다음 행 데이터를 가져오는 LAG, LEAD 함수

-------------------------------------------------------------------------------

 

 

 

SQL Server 2012의 신규 함수로 LAG, LEAD 가 있습니다.

이미 오라클에서는 지원하고 있었지만, SQL Server 는 2012에 포함되었네요.

 

LAG 함수는 현재 결과집합에서 이전 행의 데이터를 가져오는 함수이고,

LEAD 함수는 LAG와 반대로 현재 결과집합에서 다음 행의 데이터를 가져오는 함수입니다.

 

이 함수가 나오기 이전에는 SELF JOIN을 사용하거나 CTE(Common Table Expression)을 사용해야 했습니다.

 

각설하고... SQL Server 2012의 샘플DB인 AdventureWoks2012 데이터베이스에서 예제를 만들어 보았습니다.

 

1. 원본 데이터 SELECT 

USE AdventureWorks2012 GO SELECT         ProductID, ModifiedDate, StandardCost FROM         Production.ProductCostHistory WHERE         ProductID IN (711, 712, 713) ORDER BY         ProductID, ModifiedDate

 

2. CTE 로 구현.

WITH LAG_LEAD_CTE AS (         SELECT                ROW_NUMBER() OVER (ORDER BY ProductID, ModifiedDate) AS RN, ProductID, ModifiedDate, StandardCost         FROM                Production.ProductCostHistory         WHERE                ProductID IN (711, 712, 713) ) SELECT         A.ProductID, A.StandardCost, A.ModifiedDate, B.StandardCost AS PrevStandardCost, C.StandardCost AS NextStandardCost FROM         LAG_LEAD_CTE A LEFT JOIN LAG_LEAD_CTE B ON A.ProductID = B.ProductID AND A.RN = B.RN + 1         LEFT JOIN LAG_LEAD_CTE C ON A.ProductID = C.ProductID AND A.RN = C.RN - 1 ORDER BY         A.ProductID, A.ModifiedDate ;

 ROW_NUMBER  를 주고 재귀쿼리를 이용,  뒤의 ROW, 앞의 ROW 와 조인하여  결과를 보여줍니다.

 

 

3. LAG / LEAD 함수를 사용하여 구현

SELECT         ProductID, StandardCost, ModifiedDate,         LAG(StandardCost, 1, NULL) OVER (PARTITION BY ProductID ORDER BY ModifiedDate) PrevStandardCost,         LEAD(StandardCost, 1, NULL) OVER (PARTITION BY ProductID ORDER BY ModifiedDate) NextStandardCost FROM         Production.ProductCostHistory WHERE         ProductID IN (711, 712, 713) ORDER BY ProductID, ModifiedDate ;

결과는 CTE와 LAG, LEAD 함수 사용 시 모두 같습니다만, 훨씬 구문이 가벼워진 것을 알 수 있습니다.

 

 

 

그럼 CTE로 구현했을 때와 LAG / LEAD 함수 로 구현했을 때의 성능 차이는 어느 정도일까요?

 

1) CTE 로 구현 시 IO 및 TIME 수치

SQL Server 구문 분석 및 컴파일 시간:    CPU 시간 = 16ms, 경과 시간 = 25ms.   (9개 행이 영향을 받음) 테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0. 테이블 'ProductCostHistory'. 검색 수 9, 논리적 읽기 수 18, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.    SQL Server 실행 시간:  CPU 시간 = 16밀리초, 경과 시간 = 4밀리초

 

2) LAG, LEAD 함수를 사용하여 구현 시 IO 및 TIME 수치

SQL Server 구문 분석 및 컴파일 시간:    CPU 시간 = 11ms, 경과 시간 = 11ms.   (9개 행이 영향을 받음) 테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0. 테이블 'ProductCostHistory'. 검색 수 3, 논리적 읽기 수 6, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.    SQL Server 실행 시간:  CPU 시간 = 0밀리초, 경과 시간 = 1밀리초

 

CTE보다 더 좋은 성능이 나오네요.

 

참고 URL

LAG : http://msdn.microsoft.com/ko-kr/library/hh231256.aspx

LEAD : http://msdn.microsoft.com/ko-kr/library/hh213125.aspx