Window 함수란 ?
SQL의 윈도우 함수란 행과 행(레코드와 레코드) 간의 관계를 비교하고 연산,정의 하기위한 함수이다. ( 순위, 합계, 평균, 행 위치 등등 .. )
윈도우 함수를 사용할 때 유의할 점은 GROUP BY 구문과 병행해서는 사용이 불가능하므로 사용에 유의해야한다.
윈도우 함수에선 GROUP BY 대신 PARTITION BY 구문을 사용하여 무언가를 기준으로 나눠 레코드 간의 관계를 파악한다.
Window 함수의 기본문법
SELECT WINDOW_FUNC ( args ) OVER ( [PARTION BY col] [ORDER BY] [ROWS BETWEEN ~ AND] )
FROM TABLE1; 쿼리는 다음과 같은 문법을 따르며 OVER 문구가 필수로 들어간다 다양한 키워드들이 사용될 수 있고 대괄호([]) 안에 사용한 키워드들은 생략이 가능하다.
윈도우 함수는 행을 하나씩 선택하여 연산을 수행한다 그래서 경우에 따라 BETWEEN 을 사용해 연산에 사용할 행을 지정할 경우가 있을 수 있다.
| 키워드 | 설명 |
|---|---|
| ARGS | 윈도우 함수에 따라 0~N개의 인자를 설정할 수 있다. |
| PARTITION BY | GROUP BY 처럼 윈도우 함수를 적용할 데이터를 나누는 기준이 된다. 예를들어 PARTITION BY 팀 이라면 팀을 기준으로 윈도우를 나눠 계산한다. |
| ORDER BY | 윈도우 안에서 함수 적용 전 데이터를 정렬한다. PARTITION BY 를 사용했다면 그 안에서 정렬이 이뤄진다. |
| ROWS BETWEEN~AND | 윈도우 프레임을 지정한다. |
WINDOW_FUNC 에는 무엇이 올 수 있는데?
| 구분 | 함수 | 비고 |
|---|---|---|
| 순위함수 | RANK,DENSE_RANK,ROW_NUMBER | - |
| 집계함수 | SUM,MAX,MIN,AVG,COUNT | SQL서버에선 OVER 절 내 ORDER BY를 지원하지 않음. |
| 그룹 내 행 순서 함수 | FIRST_VALUE,LAST_VALUE,LAG,LEAD | SQL서버에선 지원하지 않음 |
| 그룹 내 비율함수 | RATIO_TO_REPROT,PERCENT_RANK,CUME_DIST,NTILE | NTILE 제외 SQL 서버에서 지원하지 않음. |
순위함수
- RANK
ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수이다.
PARTITION BY 내에서 순위를 구할 수도 있고, 전체 데이터에 대해 순위를 구할 수도 있다.
동일한 순위에 대해서 같은 순위를 부여하며 다음 값은 건너뛴다. ( 1,1,3,4,5 와같이 )
SELECT JOB, ENAME, SAL,
RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
CLERK MILLER 1300 1
CLERK ADAMS 1300 1
CLERK JAMES 950 3
CLERK SMITH 800 4- DENSE_RANK
RANK와 동일한 동작을 하지만 중간순위를 제외하지 않고 이어서 작성한다.( 1,1,2,3,4 와 같음)
SELECT JOB, ENAME, SAL,
DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
CLERK MILLER 1300 1
CLERK ADAMS 1300 1
CLERK JAMES 950 2
CLERK SMITH 800 3- ROW_NUMBER
RANK,DENSE_RANK 와 마찬가지로 순위를 구하는 함수이지만, 동일한 값이어도 고유한 순위를 부여한다.
SELECT JOB, ENAME, SAL,
ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 2
CLERK MILLER 1300 1
CLERK ADAMS 1300 2
CLERK JAMES 950 3
CLERK SMITH 800 4일반집계 함수
말 그대로 각 그룹별 특정값을 계산할 때 사용한다 사용할 수 있는 함수는 다음과 같이 5개
SUM : 합
MAX : 최대값
MIN : 최소값
AVG : 평균값
COUNT : 조건에 해당하는 컬럼 숫자 출력
그룹 내 행 순서 함수
-
FIRST_VALUE
PARTITION BY 별 윈도우에서 가장 먼저 나온 값을 구할 때 사용한다. 공동 등수는 인정하지 않고 처음 나온 행만 가져온다. -
LAST_VALUE
FIRST_VALUE 가 가장 처음값을 가져왔다면 이는 가장 마지막 나온 값을 가져온다. 마찬가지로 공동등수는 인정하지 않는다 -
LAG
이전 몇번째 행의 값을 가져오는 함수로 인자를 최대 3개까지 사용가능하며
첫번째 인자는 어떤 컬럼의 값을 확인할지
두번째 인자는 몇번째 앞(아래)의 행을 가져올지, Default = 1
세번째 인자는 가져올 행이 없을 경우 어떤 값을 넣어줄지 지정.
SELECT ENAME, HIREDATE, SAL
, LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME HIREDATE SAL PREV_SAL
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600
WARD 22-FEB-81 1250 1600
TURNER 08-SEP-81 1500 1250
MARTIN 28-SEP-81 1250 1500
--HIREDATE를 기준으로 정렬하고 본인보다 입사일자가 하나 더 앞선 사원의 급여를 출력
SELECT ENAME, HIREDATE, SAL
, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN' ;
ENAME HIREDATE SAL PREV_SAL
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600 0
WARD 22-FEB-81 1250 0
TURNER 08-SEP-81 1500 1600
MARTIN 28-SEP-81 1250 1250
--HIREDATE를 기준으로 정렬하고 본인보다 입사일자가 두 개 더 앞선 사원의 급여를 출력
--두 개 더 앞선 사원이 없을 경우 0을 출력- LEAD
RAG 와는 다르게 이 전 n번째 행의 값을 가져오는 함수이며 동작방식은 LAG 와 마찬가지로 동작한다.
SELECT ENAME, HIREDATE
, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED"
FROM EMP;
ENAME HIREDATE NEXTHIRED
---------- --------- ---------
SMITH 17-DEC-80 20-FEB-81
ALLEN 20-FEB-81 22-FEB-81
WARD 22-FEB-81 02-APR-81
JONES 02-APR-81 01-MAY-81
BLAKE 01-MAY-81 09-JUN-81
CLARK 09-JUN-81
--HIREDATE를 기준으로 정렬하고 본인보다 HIREDATE가 하나 더 뒤인 날짜를 출력
--없는 경우 NULL
그룹 내 비율 함수
- RATIO_TO_REPORT
파티션 내 전체 SUM 값에 대해 행별 컬럼 값의 백분율을 소수점으로 구한다.
결과값은 0~1 사이이며 구간의 합을 구하면 1이다.
SELECT ENAME, SAL
, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME SAL R_R
---------- ---------- ----------
ALLEN 1600 .29
WARD 1250 .22
MARTIN 1250 .22
TURNER 1500 .27
--전체 급여에서 각각이 차지하는 비율 출력- PERCENT_RANK
파티션별 가장 먼저 나오는 것을 0, 가장 마지막에 나오는 것을 1로 지정하여 행 별 백분율을 구해서 출력한다.
SELECT DEPTNO, ENAME, SAL
, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R
FROM EMP;
DEPTNO ENAME SAL P_R
---------- ---------- ---------- ----------
10 KING 5000 0
10 CLARK 2450 .5
10 MILLER 1300 1
20 SCOTT 3000 0
20 FORD 3000 0
20 JONES 2975 .5
20 ADAMS 1100 .75
20 SMITH 800 1
30 BLAKE 2850 0
30 ALLEN 1600 .2
30 TURNER 1500 .4
30 MARTIN 1250 .6
30 WARD 1250 .6
30 JAMES 950 1
--DEPTNO를 기준으로 파티셔닝해서 순서별 백분율 출력
- CUME_DIST
파티션별 전체건수에서 현재 행보다 작거나 같은 건수에 대해 누적 백분율을 구한다.
SELECT DEPTNO, ENAME, SAL
, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST
FROM EMP;
DEPTNO ENAME SAL CUME_DIST
---------- ---------- ---------- ----------
10 KING 5000 .333333333
10 CLARK 2450 .666666667
10 MILLER 1300 1
20 SCOTT 3000 .4
20 FORD 3000 .4
20 JONES 2975 .6
20 ADAMS 1100 .8
20 SMITH 800 1
30 BLAKE 2850 .166666667
30 ALLEN 1600 .333333333
30 TURNER 1500 .5
30 MARTIN 1250 .833333333
30 WARD 1250 .833333333
30 JAMES 950 1
--DEPTNO를 기준으로 파티셔닝해서 누적 백분율 출력4.NTILE
파티션별 전체 건수를 arg 값으로 등분한 결과를 출력한다.
SELECT ENAME, SAL
, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE
FROM EMP ;
ENAME SAL QUAR_TILE
---------- ---------- ----------
KING 5000 1
FORD 3000 1
SCOTT 3000 1
JONES 2975 1
BLAKE 2850 2
CLARK 2450 2
ALLEN 1600 2
TURNER 1500 2
MILLER 1300 3
WARD 1250 3
MARTIN 1250 3
ADAMS 1100 4
JAMES 950 4
SMITH 800 4
--전체건수를 ARGUMENT값(여기서는 4)으로 N등분한 결과 출력
--14를 4로 나누면 몫이 3 나머지가 2, 나머지2는 앞의 조부터 할당되기 때문에 4,4,3,3으로 나눔여기까지 Window 함수에 대해서 알아보았다. Window 함수는 처음에 익히기에 조금 어려울 수 있지만 한번 적응하고 난다면 아주 유용하게 사용할 수 있다.
가장 많이 사용되는 윈도우 함수로는
ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG 들이 주로 사용되고
집계 함수로 자주 사용되는 함수는
AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE 들이 주로 사용되므로
해당 함수들을 사용한 예시를 자주 사용해보고 익혀 실상황에 아주 유용하게 사용하면 좋을 것 같다.
살아남기 위해 끄적이는 블로그 : 생존법