[SQLD] 윈도우 함수

개념

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

 

문법

 

 

• PARTITION BY절
  • 출력할 총 데이터 수 변화 없이 그룹연산 수행할 GROUP BY 컬럼
• ORDER BY절
  • RANK의 경우 필수
  • SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력 시 사용
• ROWS[RANGE BETWEEN A AND B]
  • 연산 범위 지정
  • ORDER BY 절 필수

💥 PARTITION BY, ORDER BY, ROWS...절 전달 순서 중요(ORDER BY를 PARTITION BY 전에 사용 불가)

 

 

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

👉 전체를 출력하는 컬럼과 그룹함수 결과는 함께 출력할 수 없음

 

 

그룹 함수의 형태

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

 

문법

 

 

1) SUM OVER()

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

 

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

 

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

 

 

해결2) 윈도우 함수 사용

 

 

2) AVG OVER()

• SUM과 동일하게 사용

 

👉 각 직원 정보와 해당 직원이 속한 부서의 평균 급여 출력

 

 

3) MIN/MAX OVER()

• SUM과 동일하게 사용

 

👉 각 직원 정보와 해당 직원이 속한 부서의 최대급여를 함께 출력

 

4) COUNT

• SUM과 동일하게 사용

 

 

💥 윈도우 함수의 연산 범위 : 집계 연산 시 행의 범위 설정 가능

1. ROWS, RANGE 차이

1) ROWS

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

2) RANGE

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

 

2. BETWEEN A AND B

A) 시작점 정의

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

 

B) 마지막 시점 정의

• CURRENT ROW
  • 현재행까지(DEFAULT)
• UNBOUNDED PRECEDING
  • 마지막까지
• N FOLLOWING
  • N 이후까지

 

👉 RANGE_TEST 테이블에서의 범위 설정에 따른 누적합

CASE1) RANGE 범위 전달(DEFAULT)

• 값이 같은 범위로 취급하여 동시 연산

 

 

CASE2) ROWS 범위 설정 시

• 각 행 별로 연산 수행

 

 

CASE3) BETWEEN A AND B 수정 시

순위 관련 함수

1. RANK(순위)

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

 

문법

 

 

👉 EMP에서 급여가 3000이면 전체 급여 순위가 얼마?

 

 

• RANK() OVER()
  • 전체 중/특정 그룹 중 값의 순위 확인
  • ORDER BY 절 필수
  • 순위를 구할 대상을 ORDER BY절에 명시(여러 개 나열 가능)
  • 그룹 내 순위 구할 시 PARTITION BY 절 사용

 

문법

 

 

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

 

 

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

 

 

• ROW NUMBER
  • 연속된 행 번호
  • 동일한 순위를 인정하지 않고 단순히 순서대로 나열한 순으로 값 리턴

 

👉 RANK, DENSE_RANK, ROW_NUBER 비교

 

 

LAG, LEAD

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

 

문법

 

 

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

 

참고) 이전/이후 값 가져올 때 이전 값이 같더라도 항상 행의 순서대로 이전, 이후 하나를 가져옴 따라서 사용자가 이전/이후 값을 가져올 원하는 행 배치를 ORDER BY를 통해 충분히 전달 한 후 이전/이후 값을 가져오면 됨

 

 

 

FIRST_VALUE, LAST_VALUE

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

 

문법

 

 

👉 FIRST_VALUE를 사용한 최소, 최대 출력

 

👉 LAST_VALUE를 사용한 최소, 최대 출력

 

 

NTILE

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

 

문법

 

 

👉 NTILE을 사용한 그룹 분리

 

 

비율 관련 함수

1) RATIO_TO_REPORT

• 각 값의 비율 리턴
• ORDER BY 사용 불가

 

문법

 

 

2) CUME_DIST

• 각 행의 수에 대한 누적비율
• ORDER BY를 사용하여 누적비율을 구하는 순서 정할 수 있음
• ORDER BY 필수
• 값이 3개이면 1/3 = 0.33 부터 시작

 

문법

 

 

3) PERCENT_RANK

• PERCENTILE(분위수) 출력
• 전체 COUNT 중 상대적 위치 출력(0~1 범위 내)
• ORDER BY 필수

 

문법

 

 

👉 누적 비율 비교

 

 

👉 CUME_DIST와 PERCENT_RANK 비교

 

👉 PERCENT_RANK 예제

 

 

 

 

 

출처: https://youtu.be/hLvv0GN0rT8?si=bXqUIrUjCkirzJ0R