SQL Server 2012 T-SQL New Feature –LEAD, LAG

SQL Server 2012 T-SQL New Feature –LEAD, LAG

 

이번에는 LEAD, LAG 함수를 알아보려고 합니다.

http://technet.microsoft.com/ko-kr/library/hh213125(v=sql.110).aspx

 

LEAD ( scalar_expression [ ,offset ] , [ default ] )

    OVER ( [ partition_by_clause ] )

LEAD는 자체 조인을 사용하지 않고 동일한 결과 집합에 있는 다음 행의 데이터에 액세스합니다.

LAG (scalar_expression [,offset] [,default])

    OVER ( [ partition_by_clause ] order_by_clause )

LAG는 자체 조인을 사용하지 않고 동일한 결과 집합에 있는 이전 행의 데이터에 액세스합니다.

 

가끔 가다 이상한 요구사항(전전년도, 전년도, 현재년도의 매출과 판매율)이 나오는데 쿼리로 작성하기에는 상당히 까다로울 수 있는 내용입니다.

 

위와 같은 쿼리문의 결과에서 원하는 결과는 어떤 내용이냐 하면 해당 값의 이전 값과 다음 값을 다음과 같이 표시해달라는 겁니다.

7월 4일의 이전 판매 합은 7월 3일로 15012가 되며 다음 판매 합은 7월 5일로 15012 가 됩니다. 일별 판매 차이를 알 수 있습니다.

이전 쿼리에서는 Self 조인을 통해 이렇게 저렇게 ㅜ

 

 

 SQL Server 2012에서는 LEAD, LAG를 통해서 위와 같은 결과를 손쉽게 처리할 수 있습니다.

먼저 결과 행의 이전 행에 대한 내용을 처리하는 부분은 아래와 같이 처리됩니다.

SELECT

      oh.[OrderDate]

 

             ,LAG(SUM(od.[UnitPrice]),1) OVER (ORDER BY oh.[OrderDate] )  PrevValue

 

    , SUM(od.[UnitPrice]) AS SalesTotal             

  FROM [Sales].[SalesOrderHeader] oh INNER JOIN [Sales].[SalesOrderDetail] od

                           ON oh.[SalesOrderID] =od.[SalesOrderID]

GROUP BY oh.[OrderDate]

ORDER BY   oh.[OrderDate]

 

주문일로 정렬된 내용에 대해 이전 행의 판매량을 처리합니다.

LAG(SUM(od.[UnitPrice]),1) OVER (ORDER BY oh.[OrderDate] )  PrevValue

 

위 구문의 숫자 1은 바로 이전 행을 가리킵니다. 숫자 2를 사용한다면 전 전 행이 됩니다.

해당 값이 없을 경우 NULL을 반환하게 되는데 0 으로 표시하려면 아래와 같이 처리하면 됩니다;

LAG(SUM(od.[UnitPrice]),1,0) OVER (ORDER BY oh.[OrderDate] )  PrevValue

 

아래 쿼리의 결과를 보시면 될 것 같습니다.

SELECT

      oh.[OrderDate]

             ,LAG(SUM(od.[UnitPrice]),2,0) OVER (ORDER BY oh.[OrderDate] )  Prev2Value

             ,LAG(SUM(od.[UnitPrice]),1,0) OVER (ORDER BY oh.[OrderDate] )  PrevValue

 

    , SUM(od.[UnitPrice]) AS SalesTotal             

  FROM [Sales].[SalesOrderHeader] oh INNER JOIN [Sales].[SalesOrderDetail] od

                           ON oh.[SalesOrderID] =od.[SalesOrderID]

GROUP BY oh.[OrderDate]

ORDER BY   oh.[OrderDate]

 

 

LEAD는 결과 행의 다음 값을 나타냅니다. 아래 쿼리처럼 사용하면 되고 결과까지 캡쳐하지 않아도 될 것 같습니다.

SELECT

      oh.[OrderDate]

             ,LAG(SUM(od.[UnitPrice]),1,0) OVER (ORDER BY oh.[OrderDate] )  PrevValue

    , SUM(od.[UnitPrice]) AS SalesTotal             

 

             ,LEAD(SUM(od.[UnitPrice]),1) OVER (ORDER BY oh.[OrderDate] ) NextValue

 

  FROM [Sales].[SalesOrderHeader] oh INNER JOIN [Sales].[SalesOrderDetail] od

                           ON oh.[SalesOrderID] =od.[SalesOrderID]

GROUP BY oh.[OrderDate]

ORDER BY   oh.[OrderDate]

 

 LEAD와 LAG의 함수에 나오는 OVER 구문에서도 순위 함수와 동일하게 PARTITION BY를 사용할 수 있습니다. 아래의 경우는 TerritoryID 로 구분하여 LEAD와 LAG로 나타내야 합니다.

 

 TerritoryID 로 구분한 이전일의 판매량과 다음일의 판매량은 PARTITION BY를 사용하여 아래와 같이 가능합니다.

 

SELECT

      oh.[OrderDate] ,[TerritoryID]

             ,LAG(SUM(od.[UnitPrice]),1,0) OVER (PARTITION BY [TerritoryID] ORDER BY oh.[OrderDate] )  PrevValue

    , SUM(od.[UnitPrice]) AS SalesTotal             

 

             ,LEAD(SUM(od.[UnitPrice]),1) OVER (PARTITION BY [TerritoryID]  ORDER BY oh.[OrderDate] ) NextValue

 

  FROM [Sales].[SalesOrderHeader] oh INNER JOIN [Sales].[SalesOrderDetail] od

                           ON oh.[SalesOrderID] =od.[SalesOrderID]

GROUP BY oh.[OrderDate],[TerritoryID]

ORDER BY   oh.[OrderDate],[TerritoryID]

 

아래 결과 집합 그림은 TerritoryID 4의 7월 5일 데이터에 대한 내용과 위 구문의 결과입니다.

 

 결과 행의 이전, 다음 행의 값을 해당 행에 같이 표시해주려는 경우 LEAD, LAG를 이용하시면 아주 편한 결과를 제공할 수 있을 것 같습니다~