[MySQL] WINDOW 함수

WINDOW 함수

---

기본구조

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

 

· 행 분할 : PARTITOIN BY                < GROUP BY 역할

· 행 정렬 : ORDER BY                        < ORDER BY 역할

· 행 지정 : ROWS 또는 RANGE       < WHERE의 역할

 

함수구분	종류
순위함수	RANK, DENSE_RANK, ROW_NUMBER
집계함수	SUM, MAX, MIN, AVG, COUNT
행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율 함수	RATIO_TO_REPORT,  PERCENT_RANK, CUME_DIST, NTILE

---

WINDOWIG절

ROWS & RANGE

ROWS 는 행의 수를 선택할 때 사용

RANGE 는 값의 범위를 선택할 때 사용

UNBOUNDED PRECEDING PRECEDING (이전의 행) ▲ CURRENT ROW (현재 행인 경우) ▼ FOLLOWING : 다음에 나오는 행 UNBOUNDED FOLLOWING

 

> 현재 연산 작업이 이루어지는 행 (CURRENT ROW) 기준

위로 위치한 행은 PRECEDING

아래로 위치한 행은 FOLLOWING

 

위치에 따라서 바로 한 칸 위는 1 PRECEDING

바로 한 칸 아래는 1 FOLLOWING 이라 부른다.

맨 위의 행은 UNBOUNDED PRECEDING (무한한 상위 행 )이라 부른다.

맨 아래 행은 UNBOUNDED FOLLOWING (무한한 하위 행) 이라 부른다.

 

예시)

SELECT JOB,
        SUB(SAL) OVER (PARITTION 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	윈도우 함수 연산을 현재 행부터 맨 아래 행까지
ROWS 1 PRECEDING	윈도우 함수 연산을 현재 행 기준 한칸 위부터 현재행까지 계산
ROWS 2 FOLLOWING	윈도우 함수 연산을 형재 행 기준 한칸 아래까지 계산

 

예시)

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	현재 행 기준 두칸 위 행부터 현재행까지 포함해서 계산
ROWS 1 PRECEDING AND UNBOUNDED FOLLOWING	현재 행 기준 한칸 위부터 최하단 행까지 계산
ROWS 1 FOLLOWING AND 3 FOLLOWING	현재 행 기준 한칸 아래부터 세칸 아래 행까지 총 3행 계산

 

---

RANGE에 대한 예시

 

ROWS는 '행의 위치'를 기준으로 행을 선택한다면

RANGE는 '칼럼의 값' 기준으로 연산을 적용시킬 행을 선택.

SELECT JOB, ENAME, SAL,
       SUM(SAL) OVER (ORDER BY SAL
                      RANGE 150 PRECEDING) AS CUME_SAL
 FROM emp
 WHERE JOB = 'CLERK' OR JOB= 'SALESMAN';

> job이 CLERK 또는 SALESMAN인 사람들을

   SAL 오름차순으로 정렬해서 현재 SAL 컬럼 기준으로 차이가 150 이하인 행의

   job, ename, sal을 출력한다.

 

①

2행의 JAMES 의 경우 SAL 이 950 

1행 SMITH의 800과 150 차이가 나므로                                      > ROWS 150 PRECEDING 만족

800과 950 을 더하여                                                                        > SUM(sal)

1750으로 값이 계산                                                               

 

②

반면 TURNER 의 경우 이전행이 1500 – 150 = 1350 이상이여야 값이 합쳐질 수 있는데

MILLER 의 SAL 이 1300

→  연산의 대상이 되지 못함.

 

RANGE는 ORDER BY 를 통해 정렬된 컬럼에 같은 값이 존재하는 ROW가 여러 개일 경우, 

동일한 컬럼값을 가지는 모든 ROW를 묶어서 연산

코드	의미
RANGE 수치 PRECEDING 예) 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

 

RANGE 요약

> 컬럼의 값들 중 선택적으로 집계함수로 연산을 해야 한다거나 같은 값들이 필요하지 않을 경우 사용