[SQL] 윈도우 함수 (window function)

윈도우 함수란?

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

데이터를 분석하고 집계하는데 유용한 기능

전체 결과 집합에서 각 행에 다한 계산을 수행할 수 있음

 

• GROUP BY을 쓰지 않고 그룹 연산 기능
• LAG, LEAD, SUM, AVG,MIN,MAX, COUNT, RANK

문법

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

 

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

순서 중요

누적합은 ORDER BY 필수

 

 

ROWS, RANGE 차이

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

 

BETWEEN A  AND B

RANGE설정 시 반드시 범위 설정 필요

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

 

순서 관련함수

RANK WIDTHIN GROUP

• 특정값에 대한 순위 확인
• 윈도우함수는 아님

SELECT RANK(값) WITHIN GROUP(ORDER BY 컬럼)

 

RANK() OVER()

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

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

 

DENSE_RANK

• 누적순위
• 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식

 

ROW_NUMBER

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

 

예시

SELECT 
    학생ID, 
    이름, 
    점수,
    RANK() OVER (ORDER BY 점수 DESC) AS RANK,
    DENSE_RANK() OVER (ORDER BY 점수 DESC) AS DENSE_RANK,
    ROW_NUMBER() OVER (ORDER BY 점수 DESC) AS ROW_NUMBER
FROM 학생점수;

RANK, DENSE_RANK, ROW_NUMBER 비교

학생ID	이름	점수	RANK	DENSE_RANK	ROW_NUMBER
1	철수	90	1	1	1
3	민수	90	1	1	2
2	영희	80	3	2	3
5	하늘	80	3	2	4
4	지민	70	5	3	5

 

LAG, LEAD

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

 

FIRST VALUE, LAST_VALUE

• 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
• 순서와 범위 정의에 따라 최소값과 최댓값 리턴

 

NTILE

• 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
• 그룹 번호가 리턴됨
• ORDER BY절 필수
• PRTITION BY 사용하여 특정 그룹을 또 원하는 수만큼 그룹 분리 가능

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

 

비율 관련 함수

RATIO_TO_TRPORT

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

 

CUME_DIST

누적비율 

• 각 값의 누적 비율 리턴 (누적 백분율)
• ORDER BY필수 (누적 비율 구하는 순서 정할 수 있음)

 

PERCENT_PANK

분위수

• 분위수 출력
• 전체 COUNT 중 상대적 위치 출력 (0~1 출력)
• ORDER BY필수
• 파티션 별로 제일 먼저 나오는것이 0 , 제일 나중에 나오는것이 1 => 백분율

 

RATIO_TO_TRPORT, CUME_DIST, PERCENT_PANK 비교

col1	RATIO_TO_REPORT	CUME_DIST	PERCENT_RANK
10	0.1	0.6	0.0
10	0.1	0.6	0.0
10	0.1	0.6	0.0
20	0.2	0.8	0.75
50	0.5	1.0	1.0

 

SELECT 
    col1,
    RATIO_TO_REPORT(col1) OVER() AS RATIO_TO_REPORT,
    CUME_DIST() OVER(ORDER BY col1) AS CUME_DIST,
    PERCENT_RANK() OVER(ORDER BY col1) AS PERCENT_RANK
FROM 
    TB1;