2-12) 윈도우 함수

윈도우 함수 (WINDOW FUNCTION)

• 서로 다른 행의 비교나 연산을 위해 만든 함수
• 본래 다른 행의 조회는 불가능
• GROUP BY를 쓰지 않고 그룹 연산 가능
• LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK

 

** 문법

• PARTITION BY 절
  • 출력할 총 데이터 수 변화 없이 그룹 연산 수행할 GROUP BY 컬럼
  • GROUP BY는 출력할 총 데이터 수를 변화시키지만 WINDOW FUNCTION에서 PARTITION BY사용하면 그룹화된 데이터를 통한 연산을 총 데이터 수 변화 없이 출력 가능
• ORDER BY 절
  • RANK의 경우 필수 (정렬 컬럼 및 정렬 순서에 따라 순위 변화)
  • SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력 시 사용
• ROWS | RANGE BETWEEN A AND B
  • 연산 범위 설정
  • ORDER BY 절 필수

** 순서 중요 ! (ORDER BY를 PARTITION BY 전에 사용 불가)

PARTITION BY → ORDER BY → ROWS | RANGE BETWEEN A AND B

 

예) 그룹 함수 오류 (윈도우 함수가 필요한 이유)

• 전체를 출력하는 컬럼과 그룹함수 결과는 함께 출력 불가

 

그룹 함수의 형태

• SUM, COUNT, AVG, MIN, MAX 등
• OVER 절을 사용하여 윈도우 함수로 사용 가능
• 반드시 연산할 대상을 그룹함수의 입력값으로 전달

 

** 문법

 

 

1.  SUM OVER()

• 전체 총 합, 그룹별 총 합 출력 가능

 

예) 각 직원 정보와 함께 급여 총 합 출력

- 각 직원 정보와 그룹함수 결과(급여 총 합)를 동시에 출력 시도 시 에러 발생

 

해결 1) 서브쿼리 사용 (스칼라 서브쿼리)

 

해결 2) 윈도우 함수 사용

• OVER() 괄호 안에 아무 것도 안 들어가더라도 OVER 기술 필요
• OVER() 쓰지 않으면 일반 집계 함수이므로 에러

 

2. AVG OVER()

 

3. MIN / MAX OVER()

 

4. COUNT

 

** 모두 SUM과 동일하게 사용

 

윈도우 함수의 연산 범위

• 집계 연산 시 행의 범위 설정 가능

 

1. ROWS, RANGE 차이

• ROWS : 값이 같더라도 각 행씩 계산
• RANGE : 값이 같은 경우 하나의 RANGE로 묶어서 동시 연산 (DEFAULT)

 

2. BETWEEN A AND B

• A) 시작점 정의
  • CURRENT ROW : 현재 행부터
  • UNBOUNDED PRECEDING : 처음부터 (DEFAULT)
  • N PRECEDING : N 이전부터
• B) 마지막 시점 정의
  • CURRENT ROW : 현재 행까지 (DEFAULT)
  • UNBOUNDED FOLLOWING : 마지막까지
  • N FOLLOWING : N 이후까지

** A에는 FOLLOWING이 포함될 수 없고, B에는 PRECEDING이 포함될 수 없음

** CURRENT ROW는 둘 다 포함

 

예) RANGE TEST 테이블에서의 범위 설정에 따른 누적합

 

CASE 1) RANGE 범위 (DEFAULT)

• DEFAULT : RANGE 범위!
• 값이 같을 경우 같은 범위로 취급하여 동시 연산
• 값의 중복이 없었다면 SAL 1250 행에서의 SUM은 2850+1250 = 4100이었겠지만, 2850+1250+1250 5350으로 계산됨

 

CASE 2) ROWS 범위 설정 : 각 행 별로 연산 수행

• BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 처음부터 현재 행까지 누적 합

 

CASE 3) BETWEEN A AND B 수정 시

• BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING : 처음부터 다음 행까지 누적 합
• N을 변경 시 다음 N개 행까지 누적 합

 

순위 관련 함수

 

1. RANK(순위)

1-1) RANK WITHIN GROUP

• 특정 값에 대한 순위 확인
• 윈도우 함수가 아닌 일반 함수

 

** 문법

 

 

예) EMP에서 급여가 3000이면 전체 급여 순위가 얼마인지

• SAL을 내림차순 정렬한 것에서 3000의 순위 출력

 

2. RANK() OVER()

• 전체 중 / 특정 그룹 중 값의 순위 확인
• ORDER BY절 필수
• 순위를 구할 대상을 ORDER BY 절에 명시 (여러 개 나열 가능)
• 그룹 내 순위 구할 시 PARTITION BY 절 사용
• 중복된 값 처리 ≫ 예) 1,2,2,4,4,6으로 랭킹

 

** 문법

 

 

예1) 각 직원의 급여의 전체 순위 (큰 순서대로)

- 직원 정보와 함께 SAL에 대한 순위를 함께 출력

 

예2) 각 직원 이름, 부서 번호, 급여, 부서별 급여 순위 (큰 순서대로)

- 부서별 조건 추가 → PARTITION BY 필요

• 어떤 기준으로 RANKING할지는 ORDER BY에 나오기 때문에 RANK()의 괄호에 포함할 필요 X

 

3. DENSE_RANK

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

 

4. ROW_NUMBER

• 연속된 행 번호
• 동일한 순위를 인정하지 않고 단순히 순서대로 나열한 대로의 순서 값 리턴
• 예) 값이 동일하더라도 1,2,3,4,5,6,7...

 

예) RANK, DENSE_RANK, ROW_NUMBER 비교

 

 

LAG, LEAD

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

 

** 문법

 

 

예) EMP에서 바로 이전 입사자와 급여 비교

• LAG(SAL, N) → N 생략하고 컬럼만 전달 시 DEFAULT는 1, 이전 행의 SAL 출력

 

** 이전 / 이후 값을 가져올 때 이전 값이 같더라도 항상 행의 순서대로 이전, 이후 하나를 가져옴

→ 사용자가 이전/이후 값을 가져올 원하는 행 배치를 ORDER BY를 통해 충분히 전달하기

• DEPTNO를 통해 1차 정렬, 이후 SAL을 통해 2차 정렬하고 이전 값 가져오기
• DEPTNO를 통해서만 정렬했다면 원하는 값을 얻지 못했을 수 있음

 

FIRST_VALUE, LAST_VALUE

• 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
• 순서와 범위의 정의에 따라 최솟값과 최댓값 리턴 가능
• PARTITION BY, ORDER BY 절 생략 가능

 

** 문법

 

 

예1) FIRST_VALUE를 사용한 최소, 최대 출력

• 어떻게 정렬하는지에 따라 최소, 최대를 모두 출력 가능

 

예2) LAST_VALUE를 사용한 최소, 최대 출력

• LAST_VALUE로 최대, 최소를 구할 때 정렬 후 연산 범위 설정 없이 DEFAULT로 수행하면 : 처음부터 현재 행까지 중에 최대, 최소를 구하는 것이므로 항상 현재 행이 반환됨
• 같은 DEPTNO 중 최대값을 LAST_VALUE를 통해 매 행에 출력하고 싶다면 연산 범위를 적절히 조정해야 함
• OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_VALUE
• OVER(PARTITION BY DEPTNO ORDER BY SAL DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_VALUE

 

NTILE

• 랭을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
• 그룹 번호가 리턴됨
• ORDER BY 필수
• PARTITION BY를 사용하여 특정 그룹에 대해 또 원하는 수 만큼 그룹 분리 가능
• 총 행의 수가 명확히 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리됨
  • 예) 14명 3개 그룹 분리 시 → 5 / 5 / 4

 

** 문법

• NTILE(N) → N 자리에 몇 개의 그룹으로 나눌 건지 기술

 

예) NTILE을 사용한 그룹 분리

NTILE(2) OVER(ORDER BY SAL)

→ 행이 총 14개라면 순서대로 오름차순 정렬 후 1~7번째 행은 1, 8~14번째 행은 2 반환

 

 

비율 관련 함수

 

1. RATIO_TO_REPORT

• 각 값의 비율 리턴 (전체 비율 또는 특정 그룹 내 비율 가능)
• ORDER BY 사용 불가 (순서에 대한 결과가 아님)
• '값'에 대한 비율

 

** 문법

 

 

2. CUME_DIST

• 각 행의 수에 대한 누적 비율
• 특정 값이 전체 데이터 집합에서 차지하는 위치를 백분위 수로 계산하여 출력
• ORDER BY를 사용하여 누적 비율을 구하는 순서 정함
• ORDER BY 필수 (위치에 대한 결과)
• 값이 3개이면 1/3 = 0.33부터 시작 (0부터 시작 X)
• '순서, 위치'에 대한 비율

 

** 문법

• PARTITION BY를 통해 그룹 내에서의 위치 비율 출력 가능

 

3. PERCENT_RANK

• PERCENTILE (분위수) 출력
• 전체 COUNT 중 상대적 위치 출력 (0~1 범위 내)
• ORDER BY 필수 (위치에 대한 결과)
• '순서, 위치'에 대한 비율

 

** 문법

• PARTITION BY를 통해 그룹 내에서의 위치 비율 출력 가능

 

예1) CUME_DIST와 PERCENT_RANK 비교

• 첫 번째 행에 대해
  CUME_DIST ≫ 1/N (N은 행의 개수)
  PERCENT_RANK ≫ 0
• CUME_DIST : 1/N, 2/N, 3/N ... N/N(1)
• PERCENT_RANK : 0, 1/N-1, 2/N-1, ..., 1

 

예2) 누적 비율 비교

• RATIO_TO_REPORT(SAL) : DEPTNO가 10인 (1300+2450+5000)에 대한 SAL의 비율 반환
  • 예) 첫 번째 행 DEPTNO = 10, SAL = 1300 → 1300/(1300+2450+5000)의 반올림은 0.15
  • ROUND(값, 2) → 소수점 둘째자리까지 반올림 (셋째자리에서)
• CUME_DIST() : ORDER BY를 SAL로만 한 경우 최대값인 같은 값에 대해 둘 다 1로 반환
• ORDER BY를 적절히 조절하여 중복 없이 반환되도록 조절 가능
  • ORDER BY에 두 컬럼 전달 → 두 값이 모두 같을 때만 RANGE 처리, 그렇지 않으면 각각 연산
• CUME_DIST는 각 행이 전체 혹은 PARTITION 내에서 차지하고 있는 비율을 의미함
  • 예) MILLER는 10번 부서원 총 3명 중 1명의 비율

 

예3) PERCENT_RANK 예제

• 사진에 잘렸지만 마지막 행은 TOTAL_PERCENTILE이 1
• 0부터 1 범위로 표현