[days07] SQL - RANK, ROLL UP

여기 두 코딩은 같은 의미이다.

select deptno, count(*)
from emp
group by deptno
order by deptno;


select distinct deptno, count(*) over(partition by deptno)
from emp
order by deptno; 

1. 여러 테이블 조인

emp, dept, salgrade 테이블에서 deptno, dname, ename, sal,  grade 컬럼 출력 

--1
select e.deptno, dname, ename, sal, grade
from emp e , dept d, salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal;

--2
select e.deptno, dname, ename, sal, grade
from emp e join dept d on e.deptno = d.deptno  --이퀄조인
        join salgrade s on e.sal between s.losal and s.hisal; --논이퀄조인

2.  emp 테이블의 각 부서별 최고급여액 받는 사원 정보 출력

   1) 상관서브쿼리 사용

   2) group by 사용

--1)
select deptno, ename, sal 
from emp o
where o.sal = (select max(sal) from emp i where i.deptno = o.deptno ) 
order by deptno;

--2)
select deptno, max(sal) --대신 group by 때문에 누군지는 확인 불가
from emp 
group by deptno
order by deptno;

 

 

RANK() OVER

- 등수를 매기는 함수로, 그룹 내에서 위치를 계산하여 반환한다.
- 해당 값에 대한 우선순위를 결정하며(중복 순위 계산함) 반환되는 데이터타입은 NUMBER이다.

select *
from(
select deptno, ename, sal
        ,rank() over(ORDER by sal desc) 급여순서
        ,rank() over(partition by deptno order by sal desc) "부서별 급여 순서"
from emp
order by deptno
)
where 급여순서<=5;

rank()over()   ,  dense_rank()over()   ,  row_number()over()  차이점

select ename, sal
        ,rank() over(order by sal desc) r --9,9,11
        ,dense_rank() over(order by sal desc) d --9,9,10
        ,row_number() over(order by sal desc) rn --9,10,11
from emp;

 

집계함수를 일반함수와 같이 쓰는 법

select deptno, ename, sal
        ,sum(sal) over(order by deptno ) 누적
        ,sum(sal) over(partition by deptno order by deptno ) 부서별
from emp;

ROLL UP()

        SELECT   컬럼명, 그룹함수(컬럼명)
        FROM     테이블명
        WHERE    조건
        GROUP BY [ROLLUP ¦ CUBE]그룹핑하고자하는 컬럼명,...
        HAVING   그룹조건
        ORDER BY 컬럼명 또는 위치번호

select deptno, count(*)
from emp
group by deptno
union select null, count(*) from emp;

--같은 의미
select deptno, count(*)
from emp
group by rollup(deptno);

select deptno, job, count(*) --부서별>직급별 사원수
from emp
group by deptno, job
union  
select deptno, null as job, count(*) --부서별 사원수
from emp
group by deptno
union all
select null, null as job , count(*) --전체 사원수
from emp
order by deptno; --오더바이는 늘 합쳐진 마지막에


--같은 의미
select deptno, job, count(*)
from emp 
group by rollup(deptno, job);

 

그럼 CUBE()는 뭐야? - 모든 그룹 조합별로 다 출력

select deptno, job, count(*)
from emp 
group by cube(deptno, job); --모든 그룹 조합별로 다 출력