안녕하세요? 쓸만한게없네 윤선식입니다.

 

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

 

1.png

 

 

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

;

 

2-1.png

 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

;

 

3-1.png

결과는 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

 

 





profile

윤선식 / SQL프런티어 3기 / http://www.sqler.com , http://windtrap.tistory.com

즐거운 인생을 살고 싶어요. ^^.

OnDemand SQL Server  Consultant

UPDATE dbo.MyWarehouse

SET MyDream = 'SQL Server Expert' 

WHERE 닉네임 = '쓸만한게없네'

GO