[Oracle] 분석함수

분석함수

select employee_id, department_id, salary, min(salary) over()
from employees;

누적합

select employee_id, department_id, salary, sum(salary) over(order by employee_id) "누적합"
from employees;

OO별

select employee_id, department_id, salary, sum(salary) over(partition by department_id) "부서별 총합"
from employees order by 2;

OO별 누적합

select employee_id, department_id, salary, sum(salary) over(partition by department_id 
                                                            order by employee_id) "부서별 누적합"
from employees order by 2;

 

TOP-N 분석

rank() : 동일한 순위가 있을 경우에 다음 순위는 갭이 생길 수 있다. 1-2-2-4-5 ...

dense_rank() : 동일한 순위가 있더라도 연이은 순위를 구하는 함수. 1-2-2-3-4 ...

select rank() over(order by salary desc) "rank",
	dense_rank() over(order by salary desc) "dense_rank", employee_id, salary
from employees;

부서별 급여 순위

select rank() over(partition by department_id order by salary desc) "부서별 rank", 
	dense_rank() over(partition by department_id order by salary desc) "부서별 dense_rank", 
	department_id, employee_id, salary 
from employees;

주의!!

rownum : fetch번호를 리턴하는 가상컬럼(numbering)

select rownum, employee_id, salary
from employees
where rownum <= 10 --where절 1순위 : 랜덤으로 10개 뽑아서
order by salary desc; --랜덤으로 10개 뽑아낸 결과를 가지고 정렬

우리회사에 똑같은 급여자가 없을때만 1-2-3-4...

select rownum, employee_id, salary
from (select employee_id, salary
      from employees order by salary desc)
where rownum <= 10;