순위, 그룹(cube, rollup), Grouping
1. rank() 함수, dense_rank() 함수
select empno, ename, rank() over(order by empno desc) from emp;
1_1. partition by : 통계함수를 적용할 대상을 지정
select deptno, empno, ename, rank() over(partition by deptno order by empno desc)from emp;
1_2. emp 에서 급여순위가 5등 이내인 사원을 출력 => '인라인뷰' 이용
select 등수, ename, sal from
(select rank() over (order by sal desc) 등수, ename, sal from emp) where 등수<=5;
1_3. emp 에서 급여가 6등에서 10등인 사원을 출력
select 등수, ename, sal FROM
(select rank() over (order by sal desc) 등수, ename, sal from emp) where 등수 between 6 and 10;
* rank(), over()를 이용할 경우 rank()함수는 where 절에 조건을 걸 수가 없다.
-> rank()를 이용해서 순위를 출력하는 인라인뷰를 대상으로 where절을 걸자.
1_4. dense_rank() 함수
select empno, sal, rank() over(order by sal desc), dense_rank() over(order by sal desc) from emp;
--rank() 를 이용해 급여순위 3등 이내를 출력하자 -> where 절에서 rank()를 사용할 수 없다.
select empno, sal, rank() over(order by sal desc) from emp where rank() over(order by sal desc) <=3;
-- 해결책 : from 절에서 rank()한 결과를 포함시킨 inline view를 만든 다음 where 절에서 조건 체크하자.
select empno, sal, 순위 from (select empno, sal, rank() over(order by sal desc) 순위 from emp) e where e.순위 <=3;
--교수 테이블에서 rank()를 이용하여 급여 순위 5등까지 출력
select 순위, name, pay from (select name, pay, rank() over (order by pay desc) 순위 from professor) p where p.순위 <=5;
-- emp 에서 급여 순위 7,8,9 등을 출력
select 순위, ename, sal from (select ename, sal, rank() over(order by sal desc) 순위 from emp) e where e.순위 in (7,8,9);
-- emp 에서 부서별 최고 급여자의 이름과 급여를 rank() 와 인라인뷰를 이용해 출력.
select deptno, ename, sal from (select deptno, ename, sal, rank() over(partition by deptno order by sal desc) rank from emp) e where e.rank = 1;
-- emp 에서 부서별 급여의 합계가 가장 많은 부서의 부서번호와 급여 합계 출력
select deptno, 합계 from (select deptno, sum(sal) 합계, rank() over(order by sum(sal) desc) rank from emp group by deptno) e where e.rank = 1;
2. 가상컬럼 rownum
elect rownum, empno, ename from emp;
select rownum, empno, ename from emp where rownum <= 5; -- 사번 순으로 5등 이내를 출력
2-1. 급여순 6~10등까지를 rownum 을 이용해 출력 : 역시 인라인뷰 이용
select 번호, empno, ename
from (select rownum 번호, empno, ename from (select empno, ename from emp order by sal desc))
where 번호 between 6 and 10; -- order by 부터 먼저 한 다음 그 데이터에 대해서 rownum을 붙이자.
2-2. 인사 테이블에서 기본급 상위 5순위까지 출력
select * from (select rownum r, name, basicpay from(select name, basicpay from insa order by basicpay desc))
where r<=5;
2-3. 인사 테이블에서 기본급 상위 10%까지 출력 (top-n 쿼리인데 오라클은 top-n이 제공되지 않는다. )
select * from (select rownum r, name, basicpay from (select name, basic from insa order by insa.BASICPAY))
where r<=count(*)*0.1;
* rownum 는 정렬조건을 order by 다음에 지정하는데 정작 rownum는 select 다음에 위치
-> 하나의 select 문으로 작성할 경우 order by 가 rownum 에 적용되지 않는다.
-> order by를 적용할 select 문을 인라인뷰로 해서 그 뷰에 rownum 을 적용
* where 절에 rownum 을 이용해 조건을 걸 경우 1번부터 순서대로 적용되는 경우만 된다.
(rownum <= 5는 적용되지만 rownum between 6 and 10은 작동안함 -> 인라인 뷰로 대처.)
* emp 에서 급여 상위 50%를 출력하시오.
select r, ename, sal from (select rank() over(order by sal desc) r, ename, sal from (select ename, sal from emp order by sal))
where r <= trunc((select count(*) from emp)/2);
3. rollup, cube : 그룹별로 출력
1) 한 컬럼에 적용될 때 -> 결과가 같다.
select deptno, avg(sal) from emp group by deptno order by 1;
select deptno, avg(sal) from emp group by rollup(deptno) order by 1;
select deptno, avg(sal) from emp group by cube(deptno) order by 1;
2) 컬럼이 2개 올 때
select deptno, job, sum(sal) from emp group by rollup(deptno, job);
select deptno, job, sum(sal) from emp group by cube(deptno, job);
4. grouping() 함수 : 해당 컬럼이 그룹핑 되었는지 안되었는지의 여부를 나타냄(그룹화 되지 않았다면 1)
select deptno, avg(sal), grouping(deptno) from emp group by rollup(deptno) ;
select decode(grouping(deptno), 1, '전체합계', 0, deptno), avg(sal) from emp group by rollup(deptno) order by 1;
4-1. emp에서 부서별 커미션을 받는 사람과 못 받는 사람의 인원수를 출력하시오.
더해서 전체 합계행을 마지막에 추가하시오.
select decode(grouping(deptno), 1,'전체 합계', 0, deptno) 부서, count(case when comm is not null then 1 end) 커미션있음, count(decode(comm, null, 2)) 커미션없음 from emp group by cube(deptno) order by 1;
select decode(grouping(deptno), 1,'직무별 합계', 0, deptno) 부서,
sum(decode(job, 'CLERK', sal)) CLERK, sum(decode(job, 'MANAGER', sal)) MANAGER,
sum(decode(job, 'PRESIDENT', sal,0)) PRESIDENT, sum(decode(job, 'ANALYST', sal,0)) ANALYST,
sum(decode(job, 'SALESMAN', sal,0)) SALESMAN from emp group by cube(deptno) order by 1;
select decode(grouping(deptno), 1,'직무별 합계', 0, (select dname from dept d where emp.deptno=d.deptno)) 부서,
sum(decode(job, 'CLERK', sal)) CLERK, sum(decode(job, 'MANAGER', sal)) MANAGER,
sum(decode(job, 'PRESIDENT', sal,0)) PRESIDENT, sum(decode(job, 'ANALYST', sal,0)) ANALYST,
sum(decode(job, 'SALESMAN', sal,0)) SALESMAN from emp group by cube(deptno) order by 1;
4-2. student, department 를 이용해서 학과별, 학년별 체중 합계를 출력하시오.
select * from department;
select * from student;
select decode(grouping(s.DEPTNO1), 1, '학년별 합계', 0, (select dname from department d where d.deptno=s.deptno1)) 학과,
sum(decode(grade, 1, weight, 0)) "1학년", sum(decode(grade, 2, weight, 0)) "2학년",
sum(decode(grade, 3, weight, 0)) "3학년", sum(decode(grade, 4, weight, 0)) "4학년",
sum(weight) 학과합계 from student s group by cube(deptno1) order by 1;