[SQLD] WINDOW FUNCTION

서로 다른 행의 비교나 연산을 위해 만든 함수

SELECT 윈도우함수([대상]) OVER([PARTITION BY 컬럼]
							[ORDER BY 컬럼 ASC|DESC]
							[ROWS|RANGE BETWEEN A AND B]);

• 행마다 계산한 값을 같이 보여주는 것이 필요한 경우 사용한다.
• SELECT 한 번만으로도 조회가 가능하다.
• GROUP BY 를 쓰지 않고도 그룹 연산이 가능하다.
• GROUP BY 를 함께 사용해도 오류가 나지 않는다.

순서 중요!!

1. PARTITION BY

• 그룹 연산을 수행할 GROUP BY 컬럼

2. ORDER BY

• RANK 의 경우 필수 (정렬 컬럼 및 순서에 따라 순위 변화)
• ORDER BY 절에도 집계 함수를 사용할 수 있다.
• SUM, AVG, MIN, MAX, COUNT 등은 누적합 출력 시 사용

  # 파티션이나 누적합이 필요없으면 OVER 를 비워도 됨 
  SELECT EMPNO, ENAME, SUM(대상) OVER() AS TOTAL FROM EMP;

3. ROWS/RANGE

• 연산 범위 설정
• ORDER BY 절이 필수이다.
• ORDER BY 뒤에 ROWS/RANGE 를 언급하지 않으면 RANGE 가 DEFAULT 이다.

ROWS/RANGE

RANGE (DEFAULT)

• 같은 값의 경우 하나의 RANGE 로 묶어서 동시 연산 (DEFAULT)

  SELECT R.*, SUM(SAL) OVER(ORDER BY SAL) FROM RANGE_TEST R;

  처음부터 현재 행까지

ROWS

• 값이 같더라도 각 행씩 연산

• 반드시 BETWEEN A AND B 가 와야 한다.

  SELECT R.*, 
  	  SUM(SAL) OVER(ORDER BY SAL
  				ROWS BETWEEN UNBOUNDED PRECEDING
  				AND CURRENT ROW) AS RESULT1
  	FROM RANGE_TEST R;

  처음부터 현재 행까지

BETWEEN A AND B

• 시작점 A 정의

  • CURRENT ROW : 현재행부터
  • UNBOUNDED PRECEDING : 처음부터 (DEFAULT)
  • N PRECEDING : N 이전부터

• 마지막 시점 B 정의

  • CURRENT ROW : 현재행까지 (DEFAULT)

  • UNBOUNDED FOLLOWING : 마지막까지

  • N FOLLOWING : N 이후까지

    예시 ) 기준이 3번째 행일 때

    SELECT R.*, 
    		  SUM(SAL) OVER(ORDER BY SAL
    						ROWS BETWEEN UNBOUNDED PRECEDING
    						AND 1 FOLLOWING) AS RESULT3
    	FROM RANGE_TEST R;

    

    처음부터 다음 행까지 누적합

RANK

RANK WITHIN GROUP

• 특정값에 대한 순위 확인
• 윈도우 함수는 아니지만 순위 관련 함수이다.

  SELECT RANK(대상) WITHIN GROUP(ORDER BY SAL DESC) AS RANK_VALUE FROM EMP:

RANK

SELECT RANK() OVER([PARTITION BY 컬럼]
				ORDER BY 컬럼 ASC|DESC);

• 전체 또는 특정 그룹 중 값의 순위 확인
• ORDER BY 절이 필수이고, 순위를 구할 대상을 명시한다. (여러 개 나열 가능)
• 그룹 내 순위를 구할 시 PARTITION BY 절을 사용한다.

  SELECT ENAME, DEPTNO, SAL, 
  				RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK1
  	FROM EMP;

  
• 순위가 같으면 같은 순위로 처리한다.

  SELECT ENAME, DEPTNO, SAL, 
  				RANK() OVER(ORDER BY SAL DESC) AS RANK_VALUE1
  	FROM EMP;

  

DENSE_RANK

• 누적 순위
• 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식 EX) 1등이 5명이어도 그 다음 순위가 2등
  

ROW_NUMBER

• 연속된 행 번호
• 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값 리턴 EX) 1등이 5명이어도 순서대로 1, 2, 3, 4, 5 부여
  

예시 ) RANK / DENSE_RANK / ROW_NUMBER 비교하기

SELECT ENAME, DEPTNO, SAL, 
				RANK() OVER(ORDER BY SAL DESC) AS RANK_VALUE1,
				DENSE_RANK() OVER(ORDER BY SAL DESC) AS RANK_VALUE2,
				ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RANK_VALUE3,
	FROM EMP;

LAG / LEAD

• 행 순서대로 각각 이전 값(LAG), 이후 값(LEAD) 가져오기
• ORDER BY 절이 필수이다.

SELECT LAG(컬럼, N) -- 가져올 값을 갖는 컬럼, 몇 번째 값을 가져올지(DEFAULT = 1) 
						OVER([PARTITION BY 컬럼]
									ORDER BY 컬럼 ASC|DESC);
									
LAG(칼럼명,1,2) -- 칼럼명에서 1 앞의 행을 가져올 지 결정하며, 2는 가져올 값이 NULL일 경우 해당 2로 처리하란 뜻
LEAD(칼럼명,1,2) -- 칼럼명에서 1 이후의 행을 가져올 지 결정하며, 2는 가져올 값이 NULL일 경우 해당 2로 처리하란 뜻

예시 ) 바로 이전 입사자와 급여 비교 (현업에서 많이 사용)

SELECT ENAME, HIREDATE, SAL, 
			LAG(SAL) OVER(ORDER BY HIREDATE) AS 바로직전상사급여 FROM EMP;

FIRST_VALUE / LAST_VALUE

• 정렬 순서대로 정해진 범위에서의 처음 값(FIRST_VALUE), 마지막 값(LAST_VALUE) 출력
• 순서와 범위에 따라 최소값, 최대값 리턴 가능

FIRST_VALUE 로 최대값, 최소값 구하기

• MIN 을 활용해도 값은 결과가 나온다.

SELECT ENAME, DEPTNO, SAL, 
			FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS MIN_VALUE,
			FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS MAX_VALUE
	FROM EMP; 
	
# 기본적으로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 로 정의되어 있음 

LAST_VALUE 로 최대값 구하기

• 그냥 오름차순을 하면 안되고, RANGE BETWEEN 으로 처음부터 끝까지 범위를 설정하여 비교해줘야 한다.

SELECT ENAME, DEPTNO, SAL, 
			LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS MAX_VALUE1,
			LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL
		RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_VALUE2
	FROM EMP;

# 기본적으로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 로 정의되어 있음 

NTILE

• 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나눈다.
• 그룹 번호를 리턴한다.
• ORDER BY 가 필수이다.

  SELECT NTILE(N) OVER([PARTITION BY 컬럼]
  					ORDER BY 컬럼 ASC|DESC);

예시 ) 14명을 2개의 그룹으로 나누기 → 7명씩 나눈다.

SELECT ENAME, SAL, DEPTNO, NTILE(2) OVER(ORDER BY SAL) AS GROUP_NUMBER
		FROM EMP;

비율 관련 함수

RATIO_TO_REPORT

• 각 값의 비율을 리턴한다. 전체 중에 몇 차지하는지?
• ORDER BY 사용 불가!

RATIO_TO_REPORT(대상) OVER([PARTITION BY 컬럼]);

CUME_DIST

• 각 값의 누적 비율을 리턴한다.
• ORDER BY 가 필수이다.

CUME_DIST(대상) OVER([PARTITION BY 컬럼]
										ORDER BY 컬럼);

예시 ) RATIO_TO_REPORT / CUME_DIST 비교하기

• RATE3 는 SAL, ENAME 으로 정렬해서 범위가 달라지게 되므로 각각 연산된다.

PERCENT_RANK

• PERCENTILE(분위수) 출력 == 전체 중에 몇 번째 있는지를 백분율로 출력
• 전체 COUNT 중 상대적 위치 출력 (0~1범위 내)
• 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것으로 1로해서 값이 아닌 행의 순서별 백분율을 구한다.
• ORDER BY 필수

PERCENT_RANK(대상) OVER([PARTITION BY 컬럼]
												ORDER BY 컬럼);

예시 ) PERCENT_RANK

SELECT ENAME, DEPTNO, SAL, PERCENT_RANK() OVER(ORDER BY SAL) 
	FROM EMP;