블로그 이미지
정홍주
Azure에 대한 내용뿐만 아니라 새로운 트렌드로 빅데이터, BI, SharePoint, 앱 등의 내용을 다룹니다.

calendar

    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

Notice

2012.04.18 08:30 SQL

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 75일 데이터에 대한 내용과 위 구문의 결과입니다.

 

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

 

posted by 정홍주