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