[SQLD] 제2장. SQL 활용의 제6절. 윈도우함수 실습

지난회차 SQLD 시험 응시할 때, 실습을 안하고 머리로만 외우려고 한 게 패인이였던 것 같아

이번엔 틈틈히 SQL Developer로 실습을 하고 있어요.

 

그중에 제가 익숙하지 않아 가장 어려워했던 윈도우함수 실습했던 걸 포스팅합니다.

나중에 제가 이동시간에 블로그 글보면서 익히려고 하는 목적도 있어요 :)

 

 

1. 그룹내 순위함수

1) RANK 함수 : 순위함수

-- ex) Rank함수 사용하여 사원 데이터에서 급여가 높은 순서와 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;

2) DENSE_RANK 함수 : 동일한 순위를 하나의 건수로 취급

-- 사원 데이터에서 급여가 높은 순서와 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력

select job, ename, sal

,rank() over (order by sal desc) rank

,dense_rank() over (order by sal desc) dense_rank

from emp;

 

3) ROW_NUMBER 함수 : RANK나 DENSE_RANK 함수가 동일한 값에 대해서 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다.

select job, ename, sal

,rank() over (order by sal desc) rank

,dense_rank() over (order by sal desc) dense_rank

,row_number() over (order by sal desc) row_number

from emp;

 

2. 일반 집계 함수

1) SUM 함수 : 파티션별 윈도우의 합

-- ex) 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합 출력

-- range unbounded preceding : 현재 행을 기준으로 파티션 내의 첫번쨰 행까지의 범위 지정

select mgr, ename, sal

,sum(sal) over (partition by mgr order by sal range unbounded preceding) mgr_sum

,sum(sal) over (partition by mgr) mgr_sum

from emp;

 

2) MAX 함수 : 파티션별 윈도우의 최대값 / MIN 함수 : 파티션별 윈도우의 최소값

-- ex) 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값, 최소값 출력

select mgr, ename, sal

,max(sal) over (partition by mgr) max

,min(sal) over (partition by mgr) min

from emp;

 

 

3) AVG 함수 : 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값 구함

-- ex) EMP 테이블에서 같은 매니저를 두고있는 사원들의 평균 SALARY를 구하는데 조건은 같은 매니저 내에서 자기 바로앞의 사번과 바로 뒤의 사번인 직원만 대상

select mgr, ename, sal

,max(sal) over (partition by mgr) max

,min(sal) over (partition by mgr) min

,round(avg(sal) over (partition by mgr order by hiredate rows between 1 preceding and 1 following)) mgr_avg

from emp;

 

4) COUNT 함수

-- ex) 본인의 급여보다 50 이하, 150 이상 많이 받는 사람 출력

select mgr, ename, sal

,count(sal) over (order by sal range between 50 preceding and 150 following) count

from emp;

 

3. 그룹 내 행순서 함수

1) FIRST_VALUE 함수 : 파티션별 윈도우에서 가장 먼저 나온 값 출력, 공동 등수 인정X, 처음 나온 행만 처리.

-- ex) 부서별 직원들을 연봉이 높은 순서대로 정렬하고, 파티션 내에서 가장 먼저 나온값 출력

-- rows unbounded preceding : 현재 행을 기준으로 파티션 내의 첫번째 행까지의 범위 지정

select deptno, ename, sal

,first_value(ename) over (partition by deptno order by sal desc rows unbounded preceding) dept_rich

from emp;

 

2) LAST_VALUE 함수 : 파티션별 윈도우에서 가장 나중에 나온 값 출력 (=MAX함수)

-- ex) 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력

-- rows unbounded preceding : 현재 행을 기준으로 파티션 내의 첫번째 행까지의 범위 지정

-- rows between current row and unbounded following : 현재 행 포함해서 파티션 내의 마지막 행까지의 범위 지정

select deptno, ename, sal

,first_value(ename) over (partition by deptno order by sal desc rows unbounded preceding) dept_rich

,LAST_VALUE(ename) over (partition by deptno order by sal desc rows between current row and unbounded following) dept_rich

from emp;

 

3) LAG 함수 : 파티션별 윈도우에서 이전 몇번째 행까지의 값 가져올 수 있다.

-- ex) 직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인 급여와 함께 출력

select ename, hiredate, sal

,lag(sal) over (order by hiredate) prev_sal

,lag(sal, 2) over (order by hiredate) prev_sal2

from emp

where job = 'SALESMAN';

 

4) LEAD 함수 : 파티션별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있다.

-- ex) 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력한다.

select ename, hiredate, sal

,lag(sal) over (order by hiredate) lag

,lead(hiredate, 1) over (order by hiredate) lead

from emp

where job = 'SALESMAN';

 

4. 그룹 내 비율 함수

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

-- ex) JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율 출력

select ename, sal, round(ratio_to_report(sal) over (), 2) ratio

from emp

where job = 'SALESMAN';

 

2) PERCENT_RANK 함수 : 값이 아닌 행의 순서별 백분율 구한다.

-- ex) 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇번째 위치쯤에 있는지 0과 1사이의 값으로 출력

select ename, sal, percent_rank() over(partition by deptno order by sal desc) percent_Rank

from emp;

 

3) CUME_DIST 함수 : 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 구한다.

-- ex) 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력

select deptno, ename, sal, round(cume_dist() over (partition by deptno order by sal desc), 2) cume_dist

from emp;

 

4) NTILE 함수 : 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다

-- ex) 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다.

select ename, sal, ntile(4) over (order by sal desc) ntile

from emp;