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

 

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

 

 

No. Subject Author Date Views
78 SSMS(SQL Server Management Studio) Tip #3 - 정규식 사용해서 텍스트 바꾸기 [1] 쓸만한게없네(윤선식) 2013.05.15 37421
77 SSMS(SQL Server Management Studio) Tip #2 - 화면 편의기능 네 가지 [2] 쓸만한게없네(윤선식) 2013.05.08 31246
76 SSMS(SQL Server Management Studio) Tip #1 - 사용자 지정 연결 색 설정 [2] 쓸만한게없네(윤선식) 2013.05.08 32197
75 [SQL2012BI]6. 압축 [5] 김상수(바쉬*^^*) 2013.02.07 32128
74 [SQL2012BI]5. MSSQL 2012 DW 셋팅 [9] 김상수(바쉬*^^*) 2013.01.27 38297
73 [SQL2012] 결과집합의 첫 번째 값과 마지막 값을 가져오는 FIRST_VALUE, LAST_VALUE [4] 쓸만한게없네(윤선식) 2013.01.16 34507
» [SQL2012] 결과집합의 이전 행과 다음 행 데이터를 가져오는 LAG, LEAD 함수 [29] 쓸만한게없네(윤선식) 2013.01.09 36028
71 [SQL2012BI]4. 다차원 모델링(2/2) [6] 김상수(바쉬*^^*) 2012.06.28 37961
70 [SQL2012BI]3. 다차원 모델링(1/2) [5] 김상수(바쉬*^^*) 2012.06.14 45377
69 [SQL2012BI]2. DW, DM, OLAP의 이해 [4] 김상수(바쉬*^^*) 2012.06.13 60008
68 [SQL2012BI]1. BI 그리고 Microsoft BI [8] 김상수(바쉬*^^*) 2012.06.07 22562
67 [짤막 강좌] FileTable이 뭥미? 한번 훑어보자. [4] 컴포지트 2012.04.27 18098
66 [SQL2012] 첫 인수에 따라 값을 고르는 CHOOSE [1] 쓸만한게없네(윤선식) 2012.02.24 23284
65 [SQL2012] 월의 마지막을 구하는 EOMONTH [2] 쓸만한게없네(윤선식) 2012.02.17 16201
64 [SQL2012]CONVERT할 때 ERROR 대신 NULL을 반환하는 TRY_CONVERT [3] 쓸만한게없네(윤선식) 2012.02.14 15352
63 [SQL2012]EXEC WITH RESULTSET [5] 성대중(지우아빠) 2012.01.30 13647
62 [동영상] 21회 SQL서버 세미나 - 코드명 "Denali" / SQL서버의 내일을 본다. (6/6) [3] 코난(김대우) 2011.12.23 16322
61 [동영상] 21회 SQL서버 세미나 - 코드명 "Denali" / SQL서버의 내일을 본다. (5/6) 코난(김대우) 2011.12.23 13823
60 [동영상] 21회 SQL서버 세미나 - 코드명 "Denali" / SQL서버의 내일을 본다. (4/6) 코난(김대우) 2011.12.23 12443
59 [동영상] 21회 SQL서버 세미나 - 코드명 "Denali" / SQL서버의 내일을 본다. (3/6) 코난(김대우) 2011.12.23 12806





XE Login