윈도우 함수 : 순위, 오프셋, 집계

윈도우 함수 (Window function)

쿼리를 기반으로 하는 각 행들의 윈도우 (집합)에 대해 연산을 하거나,

이 행들을 이용해서 계산 후 스칼라 (단일) 결과값을 출력하는 함수.

윈도우 행들의 서브집합. 현재 행과 관련된 윈도우 설정을 기반으로 한다.

 

서브쿼리처럼 집합에 대해 연산을 수행할 수 있지만,

서브쿼리는 데이터에 대한 뷰를 대상으로 연산한다는 점에서 차이.

 

필요에 따라 결과집합의 관계형 측면을 훼손시키지 않으면서도 계산 작업의 일부로 순서를 정의할 수 있게 해 준다.

SELECT a.SalesOrderID
     , CONVERT(varchar, a.ModifiedDate, 112) as date
     , a.LineTotal
	 , SUM(linetotal) OVER (
     		PARTITION BY salesorderid 
	   		ORDER BY modifieddate
       		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as runval
  FROM SalesLT.SalesOrderDetail a

 

 

OVER절에서 윈도우 설정하는 부분 3가지

• 파티셔닝
• 정렬
• 프레임

비어있는 OVER() 절은 기반이 되는 쿼리의 결과 집합에서 모든 행을 대상으로 윈도우를 만든다.

- 순위 윈도우 함수

SELECT SalesOrderID, CONVERT(VARCHAR, ModifiedDate, 112), LineTotal
	 , ROW_NUMBER() OVER (ORDER BY LineTotal DESC) AS rownum
	 , RANK() OVER (ORDER BY LineTotal DESC) AS 'rank'
	 , DENSE_RANK() OVER (ORDER BY LineTotal DESC) AS 'dense_rank'
	 , NTILE(10) OVER (ORDER BY LineTotal DESC) AS 'ntile'
  FROM SalesLT.SalesOrderDetail
 ORDER BY LineTotal DESC

 

ROW_NUMBER : 쿼리 결과집합에 대해 ORDER BY서브절에 지정된 논리적 순서를 기반으로 순차적인 정수값 할당

RANK, DENSE_RANK : ROW_NUMBER와 비슷하지만, RANK는 얼마나 많은 행들이 정렬 값 아래에 존재하는지를 나타내느데 비해, DENSE_RANK는 얼마나 많은 고유 정렬 값 들이 아래에 존재하는지를 나타낸다

NTILE : 결과 행들을 동일한 크기의 행을 가지는 단위인 TILE로 나눈 후, 각 행이 속한 타일번호를 출력하는 기능

- 오프셋 윈도우 함수

현재 행 또는 윈도우 프레임의 처음이나 끝에서부터 특정 오프셋 위치에 있는 행의 요소들을 반환하는 기능.

• LAG
• LEAD
• FIRST_VALUE
• LAST_VALUE

LAG, LEAD는 윈도우 파티션과 윈도우 정렬 절에서 사용가능하며 윈도우 프레임과는 상관없다

정렬을 기반으로 하는 파티션 내에서 현재 행으로부터 특정 오프셋 위치에 있는 행의 요소를 구하는 용도로 사용

LAG는 현재 행 이전에 있는 값을 찾을 때, LEAD는 이후에 있는 값을 찾을 때 사용된다

첫번째 매개변수(필수)는 반환하고자 하는 요소

두번째 매개변수벝 선택적이며 오프셋 값 (defaut : 1) 지정하고

세번째 매개변수에는 지정된 오프셋에 행이 없는 경우에 대신 반환할 값 (default : NULL)

SELECT custid, orderid, val
	 , LAG(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS prevVal
	 , LEAD(val)OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS nextVal
FROM Sales.OrderValues;

FIRST / LAST_VALUE : 윈도우 프레임 내에서 첫번째 행 및 마지막 행으로부터의 요소를 반환하고자 할 때 사용

현재 파티션의 첫번째 행에 있는 요소를 찾고자 한다면

FIRST_VALUE + ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 라는 프레임 구문 사용

윈도우 파티션의 마지막 행에 있는 요소를 찾고자 한다면 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 이란 프레임 구문 사용

SELECT custid, orderid, val
	 , FIRST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS firstVal
	 , LAST_VALUE(val)  OVER(PARTITION BY custid ORDER BY orderdate, orderid
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastVal
FROM Sales.OrderValues
ORDER BY custid, orderdate, orderid

- 윈도우 집계

SELECT orderid, custid, val
	 , SUM(val) OVER() AS TotalValue -- 모든 총합
	 , SUM(val) OVER(PARTITION BY custid) AS CustTotalValue -- custid별 합계
FROM Sales.OrderValues;
--select 814.5+878+330+845.8+471.2+933.5 -- 4273.0