5. 데이터 분석 함수
※ EMP 테이블 구조
※ 데이터 분석 함수
1. rank 함수
|
# 문제 82. 1981년도에 입사한 사원들의 이름과 입사일과 순위를 출력하는데 먼저 입사한 사원 순으로 순위를 부여하시오.
- 튜닝 전 select ename, hiredate, rank() over (order by hiredate asc) 순위 from emp where to_char(hiredate,'RRRR') = '1981';
- 튜닝 후 select ename, hiredate, rank() over (order by hiredate asc) 순위 from emp where hiredate between to_date('81/01/01','RR/MM/DD') and to_date('81/12/31','RR/MM/DD'); |
2. dense_rank 함수
|
# 문제 83. 이름과 월급과 순위를 출력하는데 순위가 월급이 높은 사원부터 출력하고 같은 순위가 있는 겨우에도 다음 순위가 바로 이어지게 하시오.
select ename, sal, dense_rank() over (order by sal desc) 순위 from emp;
|
|
 |
 |
3. 단일값의 순위
|
# 문제 84. 월급이 2975인 사원은 전체 사원들 중에서 월급의 순위가 어떻게 되는지 알아보시오.
select rank(2975) within group (order by sal desc) 순위 from emp; |
4. 그룹별 순위 ~ partition by
|
# 문제 85. 부서번호, 이름, 월급의 순위를 출력하는데, 순위를 출력할 때 부서번호별로 각각 월급이 높은 순으로 출력하시오.
select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) 순위 from emp; |
 |
5. listagg 함수
|
# 문제 86. listagg 함수를 사용하여 다음과 같이 출력하시오. 
select deptno, listagg(ename,',') within group (order by ename) from emp group by deptno; |
|
# 문제 87. listagg 함수를 사용하여 다음과 같이 출력하시오. 
select to_char(hiredate,'RRRR') 입사년도, listagg(ename,',') within group (order by ename asc) 명단 from emp group by to_char(hiredate,'RRRR'); |
6. ntile 함수
|
# 문제 88. 월급을 4등급으로 나누어 출력하는 컬럼과 이름, 월급을 출력하시오.
select ename, sal, ntile(4) over (order by sal desc) 등급 from emp; |
 |
|
# 문제 89. 위의 결과를 다시 출력하는데 등급이 1등급인 사원만 출력하시오. (서브쿼리 문제~~)
select * from ( select ename, sal, ntile(4) over (order by sal desc) 등급 from emp ) where 등급 = 1; |
7. 누적치를 구해주는 함수 ★★★★★
|
# 문제 90. 사원번소, 이름, 월급, 월급의 누적치를 출력하시오.
select empno, ename, sal, sum(sal) over (order by empno rows between unbounded preceding and current row) 누적치 from emp; |
 - unbounded preceding : 제일 첫번째 행 - current row : 현재 행 - unbounded following : 맨 마지막 행 |
|
# 문제 91. 위의 결과를 다시 출력하는데, 부서번호를 추가하고 월급의 누적치가 부서번호별 월급의 누적치가 되게 하시오.
select deptno, empno, ename, sal, sum(sal) over (partition by deptno order by empno rows between unbounded preceding and current row) 누적치 |
8. lead, lag 함수
|
# 문제 92. 사원번호, 이름, 월급, 월급에 lead함수와 lag함수를 사용하고 결과를 출력해보시오.
select empno, ename, sal, lead(sal,1) over (order by empno) as lead, lag(sal,1) over (order by empno) as lag from emp; |
 - 전과의 차이를 알아보기 편하다. // 시계열 데이터 분석할 떄 쓰면 좋을지도? |
|
# 문제 93. 위의 결과를 다시 출력하는데, 부서번호, 이름, 월급, 월급의 그 전행, 월급의 그 다음행을 출력하는데 부서번호 별로 각각 나오게 하시오. 
select deptno, empno, ename, sal, lead(sal,1) over (partition by deptno order by empno) as lead, lag(sal,1) over (partition by deptno order by empno) as lag from emp; |