과목II SQL 기본 및 활용 2장 - 6절 WINDOW FUNCTION

WINDOW FUNCTION

- 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수. 

- 윈도우 함수를 활용하면 복잡한 프로그램을 하나의 SQL문장으로 쉽게 해결할 수 있다. 

- WINDOW 함수는 다름 함수와 달리 중첩해서 사용하지는 못하지만, 서브쿼리에서는 사용할 수 있다.

 

1.그룹 내 순위(RANK) 관련 함수

 - RANK, DENSE_RANK, ROW_NUMBER 함수

2.그룹 내 집계(AGGREGATE) 관련 함수

 - SUM, MAX, MIN, AVG, COUNT 함수 

3.그룹 내 행 순서 관련 함수

 - FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수 

4.그룹 내 비율 관련 함수

 - CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수

 

WINDOW FUNCTION SYNTAX

SELECT  WINDOW_FUNCTION (ARGUMENTS) OVER 

 ( [PARTITION BY 칼럼] [ORDER BY절] [WINDOWING 절] ) 

FROM 테이블명; 

 

RANK 함수

- ORDER BY를 포함한 QUERY 문에서 특정 칼럼에 대한 순위를 구하는 함수이다.

- 특정 범위(PARTITION) 내에서 순위를 구할 수도 있다.

-또한 동일한 값에 대해서는 동일한 순위를 부여하게 된다. 

 

예제) SELECT JOB, ENAME, SAL,

  RANK() OVER (ORDER BY SAL DESC) ALL_RANK, -> SAL을 내림차순으로 순위 매김

  RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK -> 같은 JOB내에서 SAL별로 순위 매김 

FROM EMP;

* 동일한 SAL에 대해서는 같은 순위 부여 

* ORDER BY SAL DESC와 PARTITION BY JOB 조건의 충돌      로 인해 JOB별로는 저열ㄹ되지 않고 SAL DESC로 정렬됨. 

 

 

 

 

 

 

 

 

 

 

 

 

DENSE_RANK 함수

- RANK함수와의 차이점은 동일한 순위를 하나의 건수로 취급하는 것이다. 

 

예제) SELECT JOB, ENAME, SAL,

  RANK() OVER (ORDER BY SAL DESC) RANK, 

  DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK

FROM EMP;   

아까와 다르게 DENSE_RANK의 경우 동일순위에 대해 하나의 건수로 취급하여 2등 다음이 3등이 되었다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ROW_NUMBER 함수

- ROW_NUMBER 함수는 RANK나 DENSE_RANK 함수가 동일한 값에 대해 동일한 순위를 부여하는데 반해, 

 동일한 값이라도 유니크한 순위를 부여한다. 

- 동일한 값의 순서 기준은 정해주지 않았다면 랜덤이다. (ORACLE의 경우 rowid가 적은 행이 먼저 나온다.) 

 

예제) SELECT JOB, ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) RANK,

     ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER

FROM EMP; 

* 동일한 순위를 배제하기 위해 유니크한 순위가 정해진 것을 볼 수 있다.

*  만약 동일 값에 순서까지 관리하고 싶다면 

ROW_NUMBER() OVER (ORDER BY SAL DESC,ENAME)과 같이 추가적인 정렬 기준 정의한다. 

 

 

 

 

 

 

 

 

 

 

MIN 함수

- MIN 함수를 이용해 파티션별 윈도우의 최소값을 구할 수 있다. 

 

예제) SELECT MGR, ENAME, HIREDATE, SAL, 

MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE RANGE UNBOUNDED PRECEDING ) AS MGR_MIN

FROM EMP; 

+) RANGE UNBOUNDED PRECEDING :  현재 행을 기준으로 파티션 내의 첫번째 행까지의 범위를 지정한다. 

 

SUM 함수

- 파티션별 윈도우의 합을 구할 수 있다. 

 

예제)

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) 

FROM EMP; 

 * MGR 별로 SAL 값을 계속해서 누적한다. 

* 단, 값이 같을 경우에는 동일한 순위로 취급하여 한번에 더해준다. 

 

 

 

 

 

 

 

 

 

 

 

 

 

AVG 함수

- AVG 함수를 통해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있음. 

 

예제)

SELECT MGR, ENAME, HIREDATE, SAL, 

     ROUND(AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

FROM EMP;

* ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 

에 의해 위의 행과 아래행과 함께 평균값을 집계한다.

(앞이나 뒤의 데이터가 없다면 해당하는 것만) 

 

 

 

 

 

 

 

 

 

 

 

 

COUNT 함수

예제)

SELECT ENAME,SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS MOV_COUNT 

FROM EMP;

* RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING 

에 의해 SAL가 현재 데이터 기준 -50 ~+150 기준에 맞는지 검사한 후 연산한다. (PARTITION BY 사용 안했으므로 모든 건수에 대해 탐색한다.) 

*  ** 표시에 있는 값을 예시로 들었을 때 1100-50인 1050부터 1100+150인 1250 안에 포함되는 SAL를 전부 세준다. 즉 3이다. (1100,1250,1250) 

 

 

 

 

 

 

 

 

 

FIRST_VALUE 함수

- 가장 먼저 나온 값을 구하는 함수이다. 

 

예제) 

SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING ) AS RICH_EMP

FROM EMP; 

 * DEPTNO 별로 가장 먼저 나온 값을 출력한다. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

LAG 함수

- LAG 함수는 3개의 ARGUMENTS 까지 사용할 수  있는데, 두번째 인자는 몇번째 앞의 행을 가져올지 결정하고(DEFAULT라면 1) , 세번째 인자는 예를 들어 가져올 데이터가 없을 경우 NULL이 들어오므로 이때 다른값으로 바꿔줄 수 있다. 결과적으로  NVI 또는 ISNULL과 같다. 

 

예제)

SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN' ;

 

LEAD 함수

- 몇번째 아래의 행을 가져올 지 결정한다. (LAG와 반대) 

 

RATIO_TO_REPORT 함수

- 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다.

결과값은 0< X <=1 의 범위를 가진다. 그리고 개별 RATIO의 합을 구하면 1이 된다. 

 

예제) 

SELECT ENAME, SAL,
ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) AS R_R
FROM EMP
WHERE JOB = 'SALESMAN';

PERCENT_RANK 함수

- 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1으로 하여, 값이 아닌 행의 순서별 백분율을 구한다. 

결과값은 0<X<=1의 범위를 가진다. 

 

예제) SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER
(PARTITION BY DEPTNO ORDER BY SAL DESC) AS P_R
 FROM EMP;

  

* ORDER BY SAL에 의해 만약 SAL의 값이 같다면 같은 순위로 취급한다. (SAL이후에 다른 정렬기준이 없으므로) 

 

 

 

 

 

 

 

 

 

 

 

 

 

CUME_DIST 함수

- 파티션별 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다.

결과값은 0< X <=1 의 범위를 가진다. 

 

예제) SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)
AS CUME_DIST
FROM EMP;

* DEPTNO 20인 경우 전체 총 5건이므로 하나당 0.2 단위의 간격을 가진다. 

* CUME_DIST의 경우 다른 WINDOW 함수와 달리 동일순서면 뒤 행의 함수 결과값을 기준으로 한다. 

(그래서 중복에 대해 한건으로 치지 않는건가?) 

 

 

 

 

 

 

 

 

 

 

 

NTILE 함수

- 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다.

 

예제)

SELECT ENAEM, SAL, NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE 

FROM EMP; 

 총 14명의 팀원을 4개의 조로 나누어야 하므로 

4+4+3+3으로 나누는 것이 가장 합리적이다. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

출처 : SQL 전문가 가이드