본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.
 |
|
6. 윈도우 함수
1. WINDOW FUNCTION 개요
PL/SQL, SQL/PL, T-SQL, PRO*C 같은 절차형 프로그램을 작성하거나, INLINE VIEW 를 이용해 복잡한 SQL 문을 작성해야 하던 것을 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 바로 WINDOW FUNCTION 이다.
WINDOW 함수는 다른 함수와는 달리 중첩(NEST)해서 사용하지는 못하지만, 서브쿼리에서는 사용할 수 있다.
윈도우 함수는 결과에 대해 함수 처리를 하는 것이므로 결과 건수가 줄어들지 않는다.
WINDOW FUNCTION SYNTAX
SELECT WINDOW_FUNCTION(ARGUMENTS) OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명;
- ARGUMENTS: 함수에 따라 0~N개의 인수가 지정될 수 있다.
- PARTITION BY 절: 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
- ORDER BY 절: 어떤 항목에 대해 순위를 지정할지 기술한다.
- WINDOWING 절: 함수의 대상이 되는 행 기준의 범위를 지정할 수 있다.
2. 그룹 내 순위 함수
가. RANK 함수
SELECT JOB,
ENAME,
SAL,
RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
나. DENSE_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 함수는 RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 틀린 점이다. JONES 의 경우 RANK 는 4 등으로 DENSE_RANK 는 3 등으로 표시되어 있다.
다. ROW_NUMBER 함수
SELECT JOB,
ENAME,
SAL,
RANK() OVER (ORDER BY SAL DESC) RANK,
ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;
ROW_NUMBER 의 경우 동일한 순위를 배제하기 위해 유니크한 순위를 정한다. 위 경우는 같은 SALARY 에서는 어떤 순서가 정해질지 알 수 없다. (Oracle 의 경우 rowid 가 적은 행이 먼저 나온다) 이 부분은 데이터베이스 별로 틀린 결과가 나올 수 있으므로, 만일 동일 값에 대한 순서까지 관리하고 싶으면 ROW_NUMBER( ) OVER (ORDER BY SAL DESC, ENAME) 같이 ORDER BY 절을 이용해 추가적인 정렬 기준을 정의해야 한다.
3. 일반 집계 함수
가. SUM 함수
사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구한다.
SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;
OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지의 누적값을 출력한다. (SQL Server 의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.)
SELECT MGR,
ENAME,
SAL,
SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) MGR_SUM
FROM EMP;
-- RANGE UNGOUNDED PRECEDING: 현재 행을 기준으로 파티션 내 첫 행까지의 범위 지정
4, 5 행의 WARD와 MARTIN은 SAL 값이 같으므로 같은 ORDER로 취급하여 MGR_SUM 값이 동일하게 3450으로 적용된다.
나. MAX 함수
사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값을 같이 구한다.
SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) MGR_MAX
FROM EMP;
추가로, INLINE VIEW 를 이용해 파티션별 최대값을 가진 행만 추출할 수도 있다.
SELECT MGR, ENAME, SAL
FROM (SELECT MGR,
ENAME,
SAL,
MAX(SAL) OVER (PARTITION BY MGR) IV_MAX_SAL
FROM EMP)
WHERE SAL = IV_MAX_SAL;
다. MIN 함수
사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 정렬하고, SALARY 최소값을 같이 구한다.
SELECT MGR,
ENAME,
HIREDATE,
SAL,
MIN(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) MGR_MIN
FROM EMP;
라. AVG 함수
EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY 를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.
SELECT MGR,
ENAME,
HIREDATE,
SAL,
ROUND(AVG(SAL) OVER
(PARTITION BY MGR ORDER BY HIREDATE
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) MGR_AVG
FROM EMP;
-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING:현재 행을 기준으로 파티션 내 앞뒤 행 지정
JAMES 의 경우 파티션 내에서 마지막 데이터이므로 뒤의 한 건을 제외한, 앞의 한 건과 본인의 데이터를 가지고 평균값을 구한다. (1250 + 950) / 2 = 1100 의 값을 가진다.
마. COUNT 함수
사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 출력한다.
SELECT ENAME,
SAL,
COUNT(*) OVER
(ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) SIM_CNT
FROM EMP;
-- RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING: -50 에서 +150 의 범위 내에 포함된 행들
4. 그룹 내 행 순서 함수
가. FIRST_VALUE 함수
파티션별 윈도우에서 가장 먼저 나온 값을 구한다. SQL Server 에서는 지원하지 않는 함수이다. MIN 함수를 활용하여 같은 결과를 얻을 수도 있다.
SELECT DEPTNO,
ENAME,
SAL,
FIRST_VALUE(ENAME) OVER
(PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) DEPT_RICH
FROM EMP;
-- ROWS UNBOUNDED PRECEDING: 현재 행을 기준으로 파티션 내 첫 번째 행까지 범위 지정
FIRST_VALUE 는 다른 함수와 달리 공동 등수를 인정하지 않고 처음 나온 행만을 처리한다. 위처럼 공동 등수가 있을 경우에 의도적으로 세부 항목을 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW 를 사용하거나, OVER () 내의 ORDER BY 절에 칼럼을 추가해야 한다.
SELECT DEPTNO,
ENAME,
SAL,
FIRST_VALUE(ENAME) OVER
(PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC
ROWS UNBOUNDED PRECEDING) DEPT_RICH
FROM EMP;
나. 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) DEPT_POOR
FROM EMP;
-- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING:
-- 현재 행 포함하여 파티션 마지막 행까지 범위 지정
FIRST_VALUE 와 유사하게 공동 등수를 인정하지 않고 가장 나중에 나온 행만을 처리한다. 만일 공동 등수가 있을 경우에 의도적으로 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW 를 사용하거나, OVER () 내의 ORDER BY 조건에 컬럼을 추가해야 한다.
다. LAG 함수
파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. SQL Server 에서는 지원하지 않는 함수이다.
SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';
-- LAG(SAL, 2, 0): 두 행 앞의 SALARY를 가져오고, 가져올 값이 없으면 0으로 처리
-- LAG(SAL): 바로 직전 행의 SALARY를 가져온다.
라. LEAD 함수
파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다. 참고로 SQL Server 에서는 지원하지 않는 함수이다.
SELECT ENAME, HIREDATE, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) "NEXTHIRED"
FROM EMP;
-- LEAD(HIREDATE, 1): 한 행 뒤의 HIREDATE를 가져오고, 가져올 값이 없으면 NULL 값이 들어옴
5. 그룹 내 비율 함수
가. RATIO_TO_REPORT 함수
RATIO_TO_REPORT 함수를 이용해 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. 결과 값은 > 0 & <= 1 의 범위를 가진다. 그리고 개별 RATIO 의 합을 구하면 1 이 된다. SQL Server 에서는 지원하지 않는 함수이다.
SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
FROM EMP
WHERE JOB = 'SALESMAN';
나. PERCENT_RANK 함수
PERCENT_RANK 함수를 이용해 파티션별 윈도우에서 제일 먼저 나오는 것을 0 으로, 제일 늦게 나오는 것을 1 로 하여, 값이 아닌 행의 순서별 백분율을 구한다. 결과 값은 >= 0 & <= 1 의 범위를 가진다. 참고로 SQL Server 에서는 지원하지 않는 함수이다.
SELECT DEPTNO,
ENAME,
SAL,
PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) P_R
FROM EMP;
SCOTT, FORD 와 WARD, MARTIN 의 경우 ORDER BY SAL DESC 구문에 의해 급여가 같으므로 같은 ORDER 로 취급한다.
다. CUME_DIST 함수
파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. 결과 값은 > 0 & <= 1 의 범위를 가진다. 참고로 SQL Server 에서는 지원하지 않는 함수이다.
SELECT DEPTNO,
ENAME,
SAL,
CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) CUME_DIST
FROM EMP;
FORD, SCOTT 과 WARD, MARTIN 의 경우 ORDER BY SAL에 의해 SAL 이 같으므로 같은 ORDER 로 취급한다. 다른 WINDOW 함수의 경우 동일 순서면 앞행의 함수 결과 값을 따르는데, CUME_DIST 의 경우는 동일 순서면 뒤 행의 함수 결과값을 기준으로 한다.
라. NTILE 함수
파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있다.
SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) QUAR_TILE
FROM EMP;
NTILE(4)의 의미는 14 명의 팀원을 4 개 조로 나눈다는 의미이다. 전체 14 명을 4 개의 집합으로 나누면 몫이 3 명, 나머지가 2 명이 된다. 나머지 두 명은 앞의 조부터 할당한다. 즉, 4 명 + 4 명 + 3 명 + 3 명으로 조를 나누게 된다.
↓SQL 전문가 가이드 요약 목록
1장. 데이터 모델링의 이해
Part 1. 데이터 모델링의 이해
Part 2. 데이터 모델과 성능
2장. SQL 기본 및 활용
Part 1. SQL 기본
Part 2. SQL 활용
Part 3. SQL 최적화 기본원리
따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.
'데이터 사이언스 > SQL' 카테고리의 다른 글
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.8. 절차형 SQL (0) | 2020.11.27 |
|---|---|
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.7. DCL (0) | 2020.11.27 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.5. 그룹 함수 (0) | 2020.11.26 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.4. 서브쿼리 (3) | 2020.11.26 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.3. 계층형 질의와 셀프 조인 (0) | 2020.11.25 |
