윈도우 함수는 기본적으로 SELECT 안에 들어간다.

기본 구조

SELECT    WINDOW함수(    )   OVER    (   <행을 분할> <행을 정렬> <대상 행 지정>  ) 
FROM TABLE;

• 행을 분할 : PARTITION BY (GROUP BY 같은 역할)
• 행을 정렬 : ORDER BY (ORDER BY 역할)
• 행을 지정 : ROWS 또는 RANGE (WHERE 의 역할)

윈도우 함수 리스트

순위 함수

동률일 때 어떻게 처리되는 지에 따라서 구분된다.

• RANK : 1,1,1,2
• DENSE_RANK : 1,1,1,4
• ROW_NUMBER : 동률과 상관 없이 순서대로 1,2,3,4

집계 함수

• SUM, MAX, MIN, AVG, COUNT

행 순서 함수

• FIRST_VALUE : 파티션에서 가장 처음에 나오는 값 반환 (MIN 결과와 동일)
• LAST_VALUE : 파티션에서 가장 마지막에 나오는 값 반환(MAX 결과와 동일)
• LAG : 현재 행에서 하나 이전 행의 값 가져오기 (ex: 이전 달 값)
• LEAD : 현재 행에서 하나 다음 행의 값 가져오기 (ex: 다음 달 값)

비율 함수

• RATIO_TO_REPORT
• PERCENT_RANK
• CUME_DIST
• NTILE

사용 예시

SELECT JOB,  
	SUM(SAL) OVER (PARTITION BY JOB 
				ORDER BY SAL DESC    
                ROWS UNBOUNDED PRECEDING) AS SUM_SAL
FROM EMP;

• 직업을 기준으로 연봉의 합을 구하고 싶은 것

WINDOWING 절 : ROWS & RANGE

ROWS는 행의 수를 선택할 때 사용하고 RANGE는 값의 범위를 선택할 때 사용한다.
둘 중 원하는 것을 선택해서 사용할 수 있다.

현재 연산 작업이 이루어지는 행(CURRENT ROW)을 기준으로 - 보통 시작은 첫 행부터!
위에 위치한 행은 PRECEDING 이라 부르고
위치에 따라서 바로 한 칸 위는 1 PRECEDING
바로 한 칸 아래는 1 FOLLOWING 이라 부른다.
맨 위의 행은 UNBOUNDED PRECEDING (무한한 상위 행 )이라 부른다.
맨 아래 행은 UNBOUNDED FOLLOWING (무한한 하위 행)이라 부른다.

SELECT JOB,  
	SUM(SAL) OVER (PARTITION BY JOB 
				ORDER BY SAL DESC    
                ROWS UNBOUNDED PRECEDING) AS SUM_SAL -- 이 부분을 안 적어줘도 똑같이 구동
FROM EMP;

• 이 예시와 같이 ROWS UNBOUNDED PRECEDING 이라고 시작점만 적은 경우 CURRENT ROW 까지 자동으로 연산이 된다.
• UNBOUNDED PRECEDING ~ CURRENT ROW
• CURRENT ROW ~ UNBOUNDED FOLLOWING

ROWS에 대한 예시

• ROWS UNBOUNDED PRECEDING
  • 윈도우 함수의 연산을 맨 위부터 현재 행(CURRENT ROW)까지
• ROWS UNBOUNDED FOLLOWING
  • 윈도우 함수의 연산을 현재 행(CURRENT ROW)에서 맨 아래 행까지 포함해서 계산
• ROWS 1 PRECEDING
  • 윈도우 함수의 연산을 한 칸 위 행부터 현재 행(CURRENT ROW)까지 포함해서 계산
• ROWS 2 FOLLOING
  • 윈도우 함수의 연산을 현재 행(CURRENT ROW)부터 두 칸 아래 행 까지 포함해서 계산

-- ROWS 1 PRECEDING : 이전달과의 합을 구해 비교해 보고 싶을 때 사용
-- 7일씩 보고 싶다 : ROWS 7 PRECEDING
SELECT JOB, ENAME, SAL, 
         SUM(SAL) OVER (ORDER BY SAL
					 	ROWS 1 PRECEDING) AS CUME_SAL 
FROM EMP;

• ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING
  • 윈도우 함수의 연산을 최고 위의 행부터 두 칸 아래 행까지 포함해서 계산
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  • 윈도우 함수의 연산을 두 칸 위의 행부터 현재 행(CURRENT ROW)까지 포함해서 계산
• ROWS 1 PRECEDING AND UNBOUNDED FOLLOWING
  • 윈도우 함수의 연산을 한 칸 위 행부터 최하단(UNBOUNDED FOLLOWING)까지 계산
• ROWS 1 FOLLOING AND 3 FOLLOWING
  • 현재 행으로부터 한 칸 아래 행부터 3칸 아래 행까지 총 3행에 대해서 계산

RANGE에 대한 예시

ROWS는 행의 위치를 기준으로 행을 선택한다면
RANGE는 칼럼의 ‘값’을 기준으로 연산에 참여할 행을 선택한다.

• 동일한 값을 지닌 행들을 더해 마친 하나의 값처럼 구동

SELECT JOB, ENAME, SAL,
	SUM(SAL) OVER (ORDER BY SAL
				RANGE 150 PRECEDING) AS CUME_SAL -- 앞 값과의 차이가 150 이하인 것만 계산
FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'SALESMAN';

• RANGE의 150 PRECEDING은 현재 칼럼의 SAL칼럼의 값을 기반으로 값을 찾는다.
• 차이가 150이하인 행들을 선택한다.
• 2행의 JAMES의 경우 SAL이 950인데 1행 SMITH의 800과 150 차이가 나므로 800과 950을 더하여 1750으로 값이 계산된다.
• 반면 TURNER 의 경우 이전행이 1500 – 150 = 1350 이상이여야 값이 합쳐 질 수 있는데 MILLER 의 SAL 이 1300 이므로 연산의 대상이 되지 않는다.
• 150 이상 차이가 날땐 자기 자신의 값을 가져온다
• 동일할 때도 자기 자신이 나오는 것인데, SUM()이므로 둘이 합한 값 2500d이 자신의 값이 된다.
  

RANGE는 ORDER BY를 통해 정렬된 컬럼에 같은 값이 존재하는 ROW가 여러 개일 경우, 동일한 컬럼값을 가지는 모든 ROW를 묶어서 연산
(ORDER BY를 해줬기 때문에 앞이 작은 값, 뒤에가 큰 값일 것)

• RANGE 150 PRECEDING
  • 현재 칼럼의 값을 기준으로 작은 값에서 150 이하로 차이가 나는 행들을 선택적으로 계산한다.
• RANGE UNBOUNDED PRECEDING
  • 현재 칼럼의 값(포함하여)을 기준으로 작은 값들을 모두 선택하여 계산한다.
• RANGE BETWEEN 150 PRECEDING AND 150 FOLLOWING
  • 현재 칼럼의 값보다 작은 값에서 150 이하로 차이가 나고 현재 칼럼 값 기준으로 큰 값에서 150이하로 차이가 나는 행들을 선택한다.
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 현재 칼럼의 값보다 작은 값을 가지는 행부터 현재 행까지 모두 선택한다.
  • RANGE UNBOUNDED PRECEDING와 동일하다.

---

다음에 다시 보기 : 윈도우 함수 종류