[oracle] WINDOW FUNCTION

SQLP를 공부하면서 조금더 빨리 알았으면 쿼리를 짤때 더 편했을텐데 했던 것중 하나가 WINDOW FUNCTION이다.

 

WINDOW FUNCTION은 행과 행간의 관계를 쉽게 정의하기위해 DBMS가 내장하고 있는 함수이다.

 

■ 문법

WINDOW_FUNCTION_NAME( [ARGUMENTS] ) OVER ( [PARTITION BY 머시기] [ORDER BY 머시기] [WINDOWING절])

- WINDOW_FUNCTION_NAME  : WINDOW FUNCTION의 고유 이름

                                          (ex) ROW_NUMBER, RANK, MAX 등

- [ARGUMENTS] : WINDOW FUNCTION 에서 사용하는 매개변수들이 들어가는 자리

                       ** FUNCTION 별로 필요한 매개변수 수가 다르며, 필수 값일수도 아닐수도 있음

- [PARTITION BY 머시기] : (필수아님) DATA를 어떤 그룹으로 나누어 FUNCTION을 적용할 것인가에 대한 내용을 정의

                                  ** GROUP BY 역할과 같다고 생각하면 쉽다.

                                      머시기는 그룹핑의 기준이되는 칼럼들 적으면 되는거 알져~

                                  ** 머시기에 정의된 칼럼들 기준으로 DATA를 나누고 나눠진 각 영역을 파티션이라고 함

- [ORDER BY 머시기] : (필수아님) 파티션 내 정렬이 필요하다면 어떤 기준으로 정렬할 것인지를 정의

- [WINDOWING절] : (필수아님) FUNCTION을 적용할 범위를 지정하는 부분 BETWEEN 사용/미사용 타입이 있으며

                            범위의 기준이 되는 것이 ROW인지, 값인지에따라 표현방식이 다름

      ① BETWEEN 사용 TYPE 

[a.범위의기준]  BETWEEN [b.범위의 시작] AND [c.범위의 종료]

 

       a.범위의 기준(ROWS | RANGE) 

          - ROWS : 현재행 기준으로 몇개 행을 포함할지 범위 지정

            (ex) 현재 row 위로 몇개, 아래로 몇개 까지가 범위

          - RANGE : ORDER BY 대상이 되는 칼럼의 값을 기준으로 범위 지정

            (ex) order by 대상되는 칼럼의 값보다 얼만큼 작은 값에서부터 대상 칼럼의 값보다 얼만큼 큰 값까지가 범위

       b.범위의 시작(UNBOUNDED PRECENDING |CURRUNT ROW | VALUE_EXPR PRECENDING | VALUE_EXPR FOLLOWING)

          - UNBOUNDED PRECENDING :  파티션의 첫번재 row

          - CURRUNT ROW : 현재 row

          - VALUE_EXPR PRECENDING : 해당 row로부터 VALUE_EXPR 만큼 이전의 값(범위의 기준이 rows일때)

                                                또는 해당 row의 기준되는 값 - VALUE_EXPR 인 row(범위의 기준이 range일때)

          - VALUE_EXPR FOLLOWING : 해당 row로부터 VALUE_EXPR 만큼 이후의 값(범위의 기준이 rows일때)

                                                또는 해당 row의 기준되는 값 + VALUE_EXPR 인 row(범위의 기준이 range일때)

       c.범위의 종료 (UNBOUNDED PRECENDING | CURRUNT ROW | VALUE_EXPR PRECENDING | VALUE_EXPR FOLLOWING)

          - UNBOUNDED FOLLOWING :  파티션의 마지막 row

          - CURRUNT ROW : 현재 row

          - VALUE_EXPR PRECENDING : 해당 row로부터 VALUE_EXPR 만큼 이전의 값(범위의 기준이 rows일때)

                                                또는 해당 row의 기준되는 값 - VALUE_EXPR 인 row(범위의 기준이 range일때)

          - VALUE_EXPR FOLLOWING : 해당 row로부터 VALUE_EXPR 만큼 이후의 값(범위의 기준이 rows일때)

                                                또는 해당 row의 기준되는 값 + VALUE_EXPR 인 row(범위의 기준이 range일때)

      ② BETWEEN 미사용 TYPE 

[a.범위의기준] [b.범위]

       a.범위의 기준(ROWS | RANGE)  - BETWEEN 사용 TYPE과 동일

       b.범위의 시작(UNBOUNDED PRECENDING |CURRUNT ROW | VALUE_EXPR PRECENDING ) 

          - UNBOUNDED PRECENDING :  파티션의 첫번재 row 부터 현재 행까지가 범위

          - CURRUNT ROW : 현재 row 하나가 범위

          - VALUE_EXPR PRECENDING : (a값이 rows)해당 row로부터 VALUE_EXPR 만큼 이전의 값부터 현재 row 까지 

              또는 (a값이 range) 해당 row의 기준되는 값 - VALUE_EXPR 인 row 부터 현재 row의 기준되는 값까지가 범위

   

      ③ WINDOWING 절에 대한 정의가 없는 경우

        - DEFAULT로 'RANGE BETWEEN UNBOUNDED PRECENDING AND CURRUNT ROW' 가 기준이 됨

 

 

■ 종류

WINDOW FUNCTION의 종류로는 순위관련함수, 집계관련함수, 행순서관련함수, 비율관련함수, 선형분석함수, 통계분석함수가 있다

구분	이름	설명
순위함수	RANK	순위를 구하는 함수 ** 2위가 2건일 경우, 3위는 없고 4위부터 다시 순위를 매김  (ex) RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC)      : JOB별로 SAL 값이 큰 순서대로 순위를 매김
	DENSE_RANK	순위를 구하는 함수 ** 동일한 순위를 하나의 건으로 취급하여 2위가 2건이어도, 3위부터 순서를 매김 (ex) DENSE_RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC)      : JOB별로 SAL 값이 큰 순서대로 순위를 매김
	ROW_NUMBER	출력하는 순서대로 순위를 매기는 함수 ** 2위가 2건이어도 출력순서에 다라 2,3위가 나뉜다 (ex) ROW_NUMBER() OVER(ORDER BY SAL DESC)      : SAL 값이 큰순서대로 정렬하여 출력 순서대로 순위를 매김 (ex) ROW_NUMBER() OVER()      : 출력 순서대로 순위를 매김
집계함수	SUM	파티션별 윈도우의 합을 구하는 함수 (ex) SUM(SAL) OVER(PARTION BY JOB)       : JOB 별 SAL 합계 (ex) SUM(SAL) OVER(PARTION BY JOB ORDER BY SAL RANGE UNBOUNDED PRECENDING)       : JOB 별, 그룹내 급여 오름차순으로 정렬하고 그룹내 누계값 출력       ** 그룹내에서 같은 순위를 갖는다면 같은 누계값을 갖는다
	MAX	파티션별 최대값 구하는 함수 (ex) MAX(SAL) OVER(PARTITION BY JOB)       : JOB별 가장 큰 값을 갖는 SAL값 출력
	MIN	파티션별 최소값 구하는 함수 (ex) MAX(SAL) OVER(PARTITION BY JOB)       : JOB별 가장 작은 값을 갖는 SAL값 출력
	AVG	파티션별 평균값 구하는 함수 (ex) AVG(SAL) OVER(PARTITION BY JOB)       : JOB별 SAL의 평균값 출력 (ex) AVG(SAL) OVER(PARTITION BY JOB ORDER BY HIREDATE ROWS BETWEEN 1 PRECENDING AND 1 FOLLOWING)       : JOB별로 입사일기준으로 정렬하고 해당 ROW 기준 앞쪽의 첫번째 ROW에서 해당 ROW 기준 뒤쪽의 첫번째 ROW 범위에 속하는 SAL 값의 평균을 출력
	COUNT	파티션별 ROW 수 COUNTING하는 함수 (ex) COUNT(*) OVER(PARTITION BY JOB)       : JOB별 ROW 갯수 출력 (ex) COUNT(*) OVER(ORDER BY SAL RANGE BETWEEN 50 PRECENDING AND 50 FOLLOWING)       : SAL 오름차순으로 정렬하고 해당 ROW의 SAL값 -50 에서 해당 ROW의 SAL값+50 범위에 속하는 ROW 수 COUNT
행순서함수	FIRST_VALUE	파티션별 첫번째 ROW 값 출력 (ex) FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORER BY SAL DESC ROWS UNBOUNDED PRECENDING)      : DEPTNO 별, SAL 값을 내림차순으로 정렬하고 파티션의 첫번째 ROW부터 해당 ROW까지의 범위에서 첫번째 ROW의 ENAME값을 출력
	LAST_VALUE	파티션별 마지막 ROW 값 출력 (ex) LAST_VALUE(ENAME IGNORE NULLS) OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECENDING AND CURRENT ROW)      : PARTITION BY 가 없으므로 전체 ROW를 SAL 오름차순으로 정렬하고, (파티션의) 첫번째 ROW부터 현재 ROW까지의 범위 내에서 마지막 ROW에 있는 ENAME값을 출력하는데, NULL값은 무시한다(마지막 ROW에 NULL 값이 있다면, 그 전 ROW의 ENAME 값 출력)
	LAG	파티션별 이전 N번째 행의 값 출력해주는 함수 (ex) LAG(ENAME, 3, '없음') OVER(ORDER BY HIREDATE)      : HIREDATE 기준 오름차순 정렬한 DATA에서 해당 ROW의 위쪽 세번째 ROW의 ENAME 값을 출력하는데, 값이 없다면 DEFAULT로 '없음'을 출력한다 (ex) LAG(ENAME) OVER(ORDER BY HIREDATE)      : HIREDATE 기준 오름차순 정렬한 DATA에서 해당 ROW의 바로 위 첫번째 ROW의 ENAME 값을 출력한다 **두번째, 세번째 매개변수가 없을 경우에는 DEFAULT로 한행 앞의 값을 출력한다
	LEAP	파티션별 이후 N번째 행의 값을 출력해 주는 함수 (ex) LEAP(HIREDATE,7) OVER(ORDER BY HIREDATE)      : HIREDATE 기준으로 오름차순으로 정렬해서 해당 ROW의 아래쪽 7번째 ROW의 HIREDATE 값을 출력
비율함수	RATIO_TO_PERCENT	파티션내 기준 칼럼의 전체 합계값에 대한 해당 ROW의 기준 칼럼값의 백분율 ** 해당 값은 0~1의 범위를 같는다. (ex) RATIO_TO_PERCENT(SAL) OVER()       : 전체 SAL값 합계 대비 현재 ROW의 SAL값이 갖는 백분율 값 출력
	PERCENT_RANK	파티션내 제일 첫번째 행을 0, 제일 마지막 행을 1로 놓고 행의 순서별로 백분율을 구하는 함수 (ex) PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)      : DEPNO 별, SAL 높은 순으로 정렬하여 파티션 내 순위별 백분율을 산출
	CUME_DIST	파티션별 전체 건수에서 현재 ROW의 기준 값보다 작거나 같은 값을 같는 ROW 건수에 대한 누적 백분율 산출 (ex) CUME_DIST() OVER(PARTITION BY DEPTNO ORDER BY SAL )      : DEPTNO별 SAL 높은순으로 정력하여 파티션에 속한 전체 ROW 수 대비 해당 ROW의 기준값 보다 작거나 같은 건의 ROW 수를 백분율로 산출 (ex) CUME_DIST() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)      : DEPTNO별 SAL 높은순으로 정력하여 파티션에 속한 전체 ROW 수 대비 해당 ROW의 기준값 보다 크거나 같은 건의 ROW 수를 백분율로 산출
	NTILE	파티션별 전체 건수를 N 등분한 결과 (ex) NTILE(10) OVER(ORDER BY SAL DESC)     : 전체 DATA를 SAL 높은 순으로 정렬하여 1~10번째 ROW는 1 출력, 11~20번째 ROW는 2 출력... ** 만약 14개의 ROW를 4등분 할 경우, [4,4,3,3] 으로 1이 출력되는 ROW는 4개, 2가 출력되는 ROW는 4개, 3이 출력되는 ROW는 3개, 4가 출력되는 ROW는 3개가 된다. 14/4 해서 그 몫이 3이니까 [3,3,3,3]. 여기에다가 14-(4*3) = 2 남은 2를 앞에서부터 1씩 분배하면 [3+1,3+1,3,3] 해서 [4,4,3,3]이 된다.

 

 

참고로 WINDOW FUCNTION은 다른 함수들과 달리 중첩해서 사용이 불가능하고, WHERE절에서 사용이 불가능하며, SUBQUERY에서는 사용 가능하다.

 

아래와 같이 사용할 수 있다. 아래의 예는 국영수 각 과목별로 가장 점수가 높은 점수를 출력하는 쿼리이다.

WITH TEMP_TB AS

(

    SELECT '국어' SUBJECT, 80 SCORE FROM DUAL

    UNION ALL SELECT '국어' SUBJECT, 100 SCORE FROM DUAL

    UNION ALL SELECT '국어' SUBJECT, 45 SCORE FROM DUAL

    UNION ALL SELECT '영어' SUBJECT, 80 SCORE FROM DUAL

    UNION ALL SELECT '영어' SUBJECT, 50 SCORE FROM DUAL

    UNION ALL SELECT '영어' SUBJECT, 94 SCORE FROM DUAL

    UNION ALL SELECT '수학' SUBJECT, 76 SCORE FROM DUAL

    UNION ALL SELECT '수학' SUBJECT, 98 SCORE FROM DUAL

    UNION ALL SELECT '수학' SUBJECT, 23 SCORE FROM DUAL

)

SELECT

   INNER.SUBJECT

   ,INNER.SCORE

FROM 

   (

   SELECT

       ROW_NUMBER() OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC ) RNUM

       ,SUBJECT

       ,SCORE

   FROM TEMP_TB

    ) INNER

WHERE INNER.RNUM=1