SQL 활용 - 윈도우 함수

윈도우 함수

기존 데이터베이스는 컬럼과 컬럼간의 연산, 비교, 조인 집합에 대한 집계 등은 쉬웠으나,

행과 행간의 관계를 정의하거나 비교, 연산하는 것을 하나의 SQL문으로 처리하는 건 어려웠다.

그래서 윈도우 함수가 등장해서 부분적으로 해결이 되었다.

select window_function (arguments) OVER ([partition by 컬럼] [order by 절] [windowing절])

from 테이블명 ;

* 윈도우 함수에는 'over' 가 반드시 들어감

1. 그릅 내 순위 함수 (RANK(), ROW_NUMBER())
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;
// PARTITION BY JOB 은 직업별로 순위를 매긴다는 표현.
// RANK는 공동 순위를 인정함 공동2등이 2명이면 1등 2등 2등 4등 이런식.
 
SELECT JOB, ENAME, SAL,
       RANK() OVER (ORDER BY SAL DESC) RANK
       DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;
 
// DENS_RANK는 공동 2등이 2명이면 1등 2등 2등 3등 이런식.
// 즉, 공동순위를 인정하나 그 다음 등수를 하나만 증가함.
 
SELECT JOB, ENAME, SAL,
       RANK() OVER (ORDER BY SAL DESC) RANK
       ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;
// ROW_NUMBER는 공동순위를 인정하지 않음. 즉 공동2위여도 칼럼 정렬상으로
// 1등 [2등, 3등] 4등을 매김.
 
2. 일반 집계 함수
SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;
// 사원테이블에서 같은 매니저를 두고있는 사원들의 급여의 합계
 
SELECT MGR, ENAME, HIREDATE, SAL MIN(SAL) OVER(PARTITION BY MGR BY HIREDATE) AS MGR_MIN
FROM EMP;
// 사원테이블에서 같은 매니저를 두고있는 사원들중 급여의 최소값 출력( 정렬 )
 
SELECT ENAME, SAL, COUNT(*) OVER( ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS SIM_CNT
FROM EMP;
// * RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING = 컬럼에서 -50 에서 +150 범위내의 포함된 모든 행
// 즉 본인의 급여보다 -50 에서 +150 사이에 있는 사람이 몇명인지 센 출력
 
3. 그룹 내 행 순서 함수
FIRST_VALUE 함수
: 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
       ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;
// 사원테이블에서 부서별로 급여가 높은 순으로 정렬하고 그중 가장 먼저나온값 즉 최고 연봉자 이름 출력
 
LAST_VALUE함수
: 파티션별 가장 나중에 나온 값 구함. 반대라고 생각.
 
LAG 함수
파티션별 윈도우에서 이전 몇번째 행의 값을 가져올수 있다.
SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';
// 디폴트는 1이라 이전 사람 급여를 가져오는 결과임.
// LAG(SAL, 2, 0) : 두번째인자는 몇 번째 이전 값인지 즉 2번째 이전 값, 세번째 인자는 없을때 뭐출력할건지 결정 즉 0
LEAD함수 LAG반대, 이후 몇번째 행의 값을 가져올수 있다.
 
4. 그룹 내 비율 함수
RATIO_TO_REPORT 함수 : 파티션 내 전체 컬럼값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함 0<X<=1 범위를 가진다.
각 RATIO값의 합은 1
SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (),2) AS r_r
FROM EMP
WHERE JOB = 'SALESMAN';
// 전체중에 자기 급여의 비율이 나옴 EX) 0.29 , 0.22 등
 
PERCENT_RANK 함수 : 파티션별 윈도우에서 가장 먼저나온  것을 0으로, 마지막을 1로 값이 아닌 행의 순서별 백분율을 구함.누적.
 
CUME_DIST 함수 : 누적 순서상 몇번째 위치인지... 
 
NTILE 함수 : 파티션별 ㅓㄴ체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있음

윈도우 함수로 레코드간의 처리가 가능하다는 점.

사용법과 RANK(), DENSE_RANK(), ROW_NUMBER()의 차이점 반드시 구분할 수 있어야함.