Oracle에서 윈도우 함수로 간단하게 쿼리 해보세요.

요즘 Oracle SQL공부를 하고 있어요.

일하면서 SQL을 많이 다루기도 해서 SQL 관련 DB자격증을 준비중이거든요.

근데 특히, 특히 윈도우함수 쪽은 평소에 사용하지 않으니 시험공부할 때 잘 모르겠더라구요. ㅠㅠ

 

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

포스팅해놓고 틈틈히 저도 출퇴근 시간에 익혀야겠어요.

확실히 직접 쿼리 결과를 보고 데이터 비교해보니까 좀 낫더라구요. 훨씬!

 

윈도우 함수는 크게 4가지로 구분할 수 있습니다.

첫번째, 그룹내 순위 함수인 RANK, DENSE_RANK, ROW_NUMBER 가 있어요.

두번째, 그룹내 집계 함수인 SUM, MAX, MIN, AVG, COUNT 함수가 있습니다.
세번째, 그룹내 행 순서 함수인 FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수가 있고

네번쨰, 그룹내 비율 관련 함수로 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 가 있어요.

 

선형 분석을 포함한 통계 분석 관련 함수도 있다고 하는데 그건 통계에 특화된 거라 일반적으로 쓰이진 않을 것 같아요.

자세하게 윈도우 함수 설명이랑 쿼리 적어봤어요.

제가 공부 용도로 쓴거라 보기 불편하실 수도 있지만 좋은 자료가 되길 바래요 :)

 

1. 그룹내 순위함수

1) RANK 함수 : 순위함수

 

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 함수 : 파티션별 윈도우의 합

- 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 함수 : 파티션별 윈도우의 최소값

 

select mgr, ename, sal

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

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

from emp;

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

 

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 함수

 

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, 처음 나온 행만 처리.

- 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함수)

- 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 함수 : 파티션별 윈도우에서 이전 몇번째 행까지의 값 가져올 수 있다.

 

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 함수 : 파티션별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있다.

 

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(컬럼)에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있다.

 

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

from emp

where job = 'SALESMAN';

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

 

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

from emp;

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

 

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

from emp;

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

 

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

from emp;

 

생각보다 종류가 많은데 지금 실습한 쿼리는 진짜 기초라서 데이터 확인도 하고 이해도 되지만

실제 일할 때 제가 잘 적용해서 쓸 수 있을지는 의문이에요... ㅠ

 

그래도 익혀두면 유용한 함수겠죠?