<목차> |
※ 자료출처: 데이터 자격검정 협회
4. 윈도우 함수
윈도우 함수(Window Function) 개요
- 분석 함수(Analystic Function) 또는 순위 함수(Rank Function)로도 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전한 기능이다.
- 사용자 입장에서는 INLINE VIEW 이후 SQL의 중요한 기능이 추가되었다고 할 수 있으며, 많은 프로그램이나 튜닝 팁을 대체할 수 있을 것이다.
- 기존 관계형 데이터베이스는 행과 행간의 관계를 정의하거나, 행과 행간을 비교, 연산하는 것을 하나의 SQL문으로 처리하는 것은 매우 어려운 문제였는데, 윈도우 함수는 이를 쉽게 정의해준다.
- 윈도우 함수는 다른 함수와는 달리 중첩(NEST)해서 사용하지는 못하지만, 서브쿼리에서는 사용할 수 있다.
Window Function의 종류
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
5) 선형 분석을 포함한 통계 분석 관련 함수:
Oracle - CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINER REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY
요약 설명
| 분류 | 함수명 | 정의 | 비고 |
| 순위 관련 | RANK | 특정 항목(컬럼)에 대한 순위 산출 ex) RANK () OVER (ORDER BY SAL DESC) |
동일한 값에 대해서는 동일한 순위 - 1, 2, 2, 4 |
| DENSE_RANK | 특정 항목(컬럼)에 대한 순위 산출 ex) DENSE_RANK () OVER (ORDER BY SAL DESC) |
동일한 순위를 하나의 건수로 취급 - 1, 2, 2, 3 |
|
| ROW_NUMBER | 특정 항목(컬럼)에 대한 순위 산출 ex) ROW_NUMBER () OVER(ORDER BY SAL DESC) |
동일한 값이라도 고유한 순위 부여 - 1, 2, 3, 4 |
|
| 집계 관련 | SUM | 파티션별 윈도우의 합계 산출 ex) SUM(SAL) OVER(PARTITION BY MG) |
|
| MAX | 파티션별 윈도우의 최대값 산출 ex) MAX(SAL) OVER (PARTITION BY MG) |
INLINE VIEW를 이용해 파티션별 최대값을 가진 행만 추출 가능 | |
| MIN | 파티션별 윈도우의 최소값 산출 ex) MIN(SAL) OVER (PARTITION BY MGR) |
||
| AVG | 원하는 조건에 맞는 데이터에 대한 평균값 산출 ex) AVG(SAL) OVER (PARTITON BY MGR) |
||
| COUNT | 원하는 조건에 맞는 데이터에 대한 통계값 산출 ex) COUNT(*) OVER (ORDER BY SAL) |
||
| 순서 관련 | FIRST_VALUE | 파티션별 윈도우에서 가장 먼저 나온 값 산출 ex) FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) |
- SQL Server에서는 미지원 (MIN 함수를 사용하여 같은 결과 얻을 수 있음) - 공동 함수를 인정하지 않고 처음 나온 행만을 처리 |
| LAST_VALUE | 파티션별 윈도우에서 가장 나중에 나온 값 산출 ex) LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) |
- SQL Server에서는 미지원 (MAX 함수를 사용하여 같은 결과 얻을 수 있음) - 공동 함수를 인정하지 않고 가장 나중에 나온 행만을 처 |
|
| LAG | 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있음 ex) LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) |
- SQL Server에서는 미지원 - 3개의 ARGUMENT 까지 사용 - 두번째 인자는 몇 번째 행을 가져올지 결정 - 세번째 인자는 가져올 값이 없는 경우 다른 값으로 바꾸어줄 수 있음 (NVL, ISNULL 기능) |
|
| LEAD | 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있음 ex) LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) |
- SQL Server에서는 미지원 - 3개의 ARGUMENT 까지 사용 - 두번째 인자는 몇 번째 행을 가져올지 결정 - 세번째 인자는 가져올 값이 없는 경우 다른 값으로 바꾸어줄 수 있음 (NVL, ISNULL 기능) |
|
| 비율 관련 | CUME_DIST | 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적밴분율 산출 ex) CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) |
- SQL Server에서는 미지원 - 결과 값은 > 0, <=1 의 범위를 가짐 |
| PERCENT_RANK | 파티션 별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율 산출 ex) PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) |
- SQL Server에서는 미지원 - 결과 값은 > 0, <=1 의 범위를 가짐 |
|
| NTILE | 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과 산출 ex) NTILE(4) OVER (ORDER BY SAL DESC) |
* NTILE(4) : 4개의 그룹으로 분 | |
| RATIO_TO_REPORT | 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 산출 ex) RATIO_TO_REPORT(SAL) OVER (), 2) |
- SQL Server에서는 미지원 - 결과 값은 > 0, <=1 의 범위를 가짐 - 개별 RATIO의 합은 1 |
[WINDOW FUNCTION SYNTAX]
윈도우 함수에는 OVER 문구가 키워드로 필수 포함된다.
- WINDOW FUNCTION: 기존에 사용하던 함수도 있고, 새롭게 윈도우 함수용으로 추가된 함수도 있다.
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER
( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] )
FROM 테이블 명;
- ARGUMENTS (인수): 함수에 따라 0~N 개의 인수가 지정될 수 있다.
- PARTITION BY 절: 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
- ORDER BY 절: 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.
- WINDOWING 절: 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
ROWS: 물리적인 결과 행의 수 / RANGE: 논리적인 값에 의한 범위 (둘 중 하나 선택하여 사용 가능)
※ SQL Server 에서는 미지원.
BETWEEN 사용 타입
ROWS | RANGE BETWEEN
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
AND
UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
BETWEEN 미사용 타입
ROWS | RANGE
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING
그룹 내 순위 함수
1. RANK 함수
ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수
- 특정 범위 (PARTITION) 내에서 구할 수도 있고, 전체 데이터에 대한 순위도 구할 수도 있다.
(동일한 값에 대해서는 동일한 순위 부여)
[예제]
사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력
| SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK, RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP; |
업무 구분이 없는 ALL_RANK 칼럼에서 FORD와 SCOTT, WARD와 MARTIN은 동일한 SALARY 이므로 같은 순위 부여.
업무를 PARTITION으로 구분한 JOB_RANK의 경우 같은 업무 내 범위에서만 순위 부여.
하나의 SQL 문장에 ORDER BY SAL DESC 조건과 PARTITION BY JOB 조건이 충돌이 났기 때문에 JOB 별로는 정렬이 되지 않고, ORDER BY SAL DESC 조건으로 정렬.
2. 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; |
FORD와 SCOTT, WARD와 MARTIN은 동일한 SALARY 이므로 RANK와 DENSE_RANK 칼럼에서 모두 같은 순위 부여.
JONSE의 경우 RANK는 4등으로, DENSE_RANK는 3등으로 표시
ADAMS의 경우 RANK는 12등으로, DENSE_RANK는 10등으로 표시
3. ROW_NUMBER 함수
RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여.
[예제]
사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력
| SELECT JOB, ENAME, SAL RANK ( ) OVER (ORDER BY SAL DESC) RANK, ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER FROM EMP; |
FORD와 SCOTT, WARD와 MARTIN은 동일한 SALARY 이므로 RANK는 같은 순위 부여했지만,
ROW_NUMBER의 경우 동일한 순위를 배제하기 위해 유니크한 순위를 정함.
데이터베이스 별로 다른 결과가 나올 수 있으므로, 만일 동일 값에 대한 순서까지 관리하고 싶으면 ROW_NUMBER ( ) OVER (ORDER BY SAL DESC, ENAME) 같이 ORDER BY 절을 이용해 추가적인 정렬 기준을 정의해야 한다.
일반 집계 함수
1. SUM 함수
파티션별 윈도우의 합을 구할 수 있다.
[예제]
사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합 산출
| SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP; PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화 |
2. MAX 함수
파티션별 윈도우의 최대값 산출
[예제]
사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값을 같이 산출
| SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX FROM EMP; |
[실행 결과]
MGR ENAME SAL MGR_MAX 7566 FORD 3000 3000
7566 SCOTT 3000 30007698 JAMES 950 1600
7698 ALLEN 1600 1600
7698 WARD 1250 1600
7698 TURNER 1500 1600
7698 MARTIN 1250 16007782 MILLER 1300 1300
7788 ADAMS 1100 1100
7839 BLAKE 2850 2975
7839 JONES 2975 2975
7839 CLARK 2450 2975
7902 SMITH 800 800
KING 5000 5000
14개의 행이 선택되었다.
[예제]
추가로, INLINE VIEW를 이용해 파티션별 최대값을 가진 행만 추출 가능
| SELECT MGR, ENAME, SAL FROM (SELECT MGR, ENAME_SAL, MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL FROM EMP) WHERE SAL = IV_MAX_SAL; |
3. MIN 함수
파티션별 윈도우의 최소값 산출
[예제]
사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 정렬하고, SALARY 최소값 산출
| SELECT MGR, ENAME, HIREDATE, SAL MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN FROM EMP; |
4. AVG 함수
AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값 산출
[예제]
EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 두의 사번인 직원만을 대상으로 함.
| SELECT MGR, ENAME, HIREDATE, SAL, ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG FROM EMP; ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정 (ROWS는 현재 행의 앞뒤 건수) |
ALLEN의 경우 파티션 내에서 첫 번째 데이터이므로 앞의 한 건은 평균값 집계 대상이 없다.
결과적으로 평균값 집계 대상은 본인의 데이터와 뒤의 한 건으로 평균값을 산출 (1600+1250) / 2 = 1425
TURNER의 경우 앞의 한 건과, 본인의 데이터와, 뒤의 한 건으로 평균값 산출
(1250+1500+1250)/3 = 1333
JAMES의 경우 파티션 내에서 마지막 데이터이므로 뒤의 한 건을 제외한, 앞의 한 건 + 본인의 데이터를 가지고
평균값 산출 (1250+950)/2 = 1100
5. COUNT 함수
COUNT 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값 산출
[에제]
사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수 출력
| SELECT ENAME, SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 AND PRECEDING AND 150 FOLLOWING) as SIM_CNT FROM EMP; RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING: 현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상 (RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위 표시) |
** 표시된 ADAMS의 경우 자기가 가지고 있는 SALARY 1100을 기준으로 -50에서 +150까지 값을 가진
1050에서 1250까지의 값을 가진 JAMES(950), ADAMS(1100), WARD(1250) 3명의 데이터 건수 산출
그룹 내 행 순서 함수
1. FIRST_VALUE 함수
파티션별 윈도우에서 가장 먼저 나온 값 산출 (SQL Server에서는 미지원)
MIN 함수를 사용하여 같은 결과 얻을 수 있음.
[예제]
부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값 출력
| SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH FROM EMP; RANGE UNBOUNDED PRECEDING: 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위 지정 |
같은 부서 내에 최고 급여를 받는 사람이 둘 있는 경우 (*표시가 있는 사람) 어느 사람이 최고 급여자로
선택될지는 위의 SQL 문만 가지고는 판단할 수 없음.
FIRST_VALUE는 다른 함수와 달리 공동 함수를 인정하지 않고 처음 나온 행만을 처리.
▶ 의도적으로 세부 항목을 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나,
OVER ( ) 내의 ORDER BY 절에 칼럼을 추가
| SELECT DEPNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP FROM EMP; |
2. LAST_VALUE 함수
파티션별 윈도우에서 가장 나중에 나온 값 산출 (SQL Server에서는 미지원)
MAX 함수를 사용하여 같은 결과를 얻을 수 있음
[예제]
부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값 출력
| SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR FROM EMP; ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위 지정 |
LAST_VALUE는 다른 함수와 달리 공동 등수를 인정하지 않고 가장 나중에 나온 행만을 처리.
만일 공동 등수가 있을 경우 이를 의도적으로 정렬하고 싶다면 별도의 정렬 조건을 가진
INLINE VIEW를 사용하거나, OVER ( ) 내의 ORDER BY 조건에 칼럼 추가
3. LAG 함수
파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있음 (SQL Server에서는 미지원)
[예제]
직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력
| SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP WHERE JOB = 'SALESMAN'; |
LAG 함수는 3개의 ARGUMENTS 까지 사용.
두 번째 인자는 몇 번째 행을 가져올지 결정하는 것이고 (DEFAULT 1),
세 번째 인자는 가져올 값이 없는 경우 다른 값으로 바꾸어 줄 수 있음 (NVL, ISNULL 기능)
| SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP WHERE JOB = 'SALESMAN' LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리 |
4. LEAD 함수
파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있음 (SQL Server 에서는 미지원)
[예제]
직원들을 입사일자가 빠른 기준으로 정렬을 하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력
| SELECT ENAME, HIREDATE, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED" FROM EMP; |
LEAD 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,
두 번째 인자는 몇 번째 후의 행을 가져올지 결정 (DEFAULT 1)
세 번째 인자는 가져올 데이터가 없을 경우 다른 값으로 바꾸어줄 수 있음 (NVL, ISNULL 기능)
그룹 내 비율 함수
1. RATIO_TO_REPORT 함수
파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 산출(SQL Server에서는 미지원)
결과 값은 >0, <=1의 범위를 가짐.
개별 RATIO의 합은 1
[예제]
JOB이 SALESMAN 인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력
| SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as P_R FROM EMP WHERE JOB = 'SALESMAN'; |
2. PERCENT_RANK 함수
파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여,
값이 아닌 행의 순서별 백분율 산출 (SQL Server 에서는 미지원)
결과 값은 >=0, <=1의 범위를 가짐
[예제]
같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력
| SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R FROM EMP; |
3. CUME_DIST 함수
파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 산출(SQL Server에서는 미지원)
결과 값은 >0, <=1의 범위 가짐.
[예제]
같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력
| SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST FROM EMP; |
4. NTILE 함수
파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과 산출
[예제]
전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류
| SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE FROM EMP |
※ NTILE(4)의 의미는 14명의 팀원을 4개 조로 나눈다는 의미.
※ 윈도우함수 관련 예제
[SQLD] 윈도우 함수 - ROW_NUMBER, PARTITION BY 예제 및 풀
[윈도우 함수 관련 글] SQLD 2과목 SQL 활용 - 윈도우 함수 서브쿼리 집합연산자 그룹 함수 윈도우 함수 TOP N 쿼리 계층형 질의와 셀프 조인 PIVOT 절과 UNPIVOT 절 정규 표현식 ※ 자료출처: 데이터 자격
puppy-foot-it.tistory.com
이전글
다음글
'자격증 > SQLD' 카테고리의 다른 글
| SQLD 2과목 SQL 활용 - 계층형 질의와 셀프 조인 (1) (1) | 2024.01.26 |
|---|---|
| SQLD 2과목 SQL 활용 - TOP N 쿼리 (1) | 2024.01.25 |
| SQLD 2과목 SQL 활용 - 그룹 함수 (1) | 2024.01.23 |
| SQLD 2과목 SQL 활용 - 집합연산자 (1) | 2024.01.16 |
| SQLD 2과목 SQL 활용 - 서브쿼리 (1) | 2024.01.15 |
