[오라클/SQL] 함수 활용해보기(2)

+ 개별적인 salary와 부서별 평균 급료 avg(salary)를 함 께 사용할 수 없기 때문에 이를 분석함수를 통해서
avg(salary) over(partition by~)식으로 처리한다. 여기서 partition by는 group by와 유사한 의미이다.

rank() 함수의 활용
rank() 함수는 순위를 표시하는데 정렬을 해야하므로 보통 뒤에 over(order by ~), within group(order by~)구문이 함께 오는 경우가 많다.

Q. 급여가 10000 이상인 직원을 월급 내림차순으로 보이시오.

select employee_id, salary, rank() over(order by salary DESC) "PERSONAL_SAL_RANK"
from employees
where salary > 10000;

Q. 급여가 $3000인 사원의 월급 순위를 보이시오

select rank(3000) within group(order by salary desc) "3000_ranked" from employees;

row_number()함수의 활용

Q. 각 부서별로 급여가 가장 높은 직원 세명을 보이시오.

select department_id, last_name, salary
from (select department_id, last_name, salary, row_number() over(partition by department_id 
order by salary DESC) rn from employees)

where rn <=3
order by department_id, salary,last_name DESC;

■ 분석 함수 (<집계함수)

테이블의 행에 대해서 특정 그룹별로 집계 값을 산출할 대 주로 사용된다.
그룹바이에 의한 최종 쿼리 결과는 그룹별로 행이 줄어들게 되는데 집계함수를 사용하면 행의 손실 없이 그룹별로 집계값을 산출할 수 있다.
분석함수는 집계함수에 속한다.
분석함수는 기존의 select만으로는 해결하기 어려운 문제를 쉽게 처리해준다.
예를 들어 다량의 데이터를 대상으로 누적 합계,부서별 순위, 구간별 평균 등을 구할 때 하나 이상의 결과를 필요로 한다.
select만 사용하면 구문이 복잡하고 성능도 보장할 수 없다.
이럴 때 분석함수를 사용하면 되는데...
분석함수는 행의 집단에 대해서 연산이 이뤄진다는 점에서 일반 그룹함수와 유사하지만 일반 그룹함수는 하나의 집단에 대해서
하나의 결과가 나오지만 분석함수는 하나의 집단에 대해서 여러 기준을 적용해서 여러 개의 결과를 도출해 낸다.
이때 처리 대상이 되는 행의 집단 (그룹)을 윈도우(window)라고 부른다.

분석함수는 select 절이나 order by~ 절에서만 나타난다.
분석_함수(인자1, 인자2, ....) over(partition by~ order by~) window-절 구문이다.
하나의 행에 대해서 하나의 결과가 나오는 것을 단일 행 함수,
여러 행(하나의 집단)에 대해서 하나의 결과가 나오는 것을 그룹함수,
여러 행(하나의 집단)에 대해서 여러 결과가 나오는 것을 분석함수(집계함수)라고 정의된다.

- 집계함수의 종류
first_value() over(), last_value() over(), count() over() , max() over(), min() over(), sum() over(),
row_number() over(), rank() over()
그리고 값의 그룹에서 값의 순위를 계산 할 때 rank() 함수와 다르게 같은 순위가 둘 이상있어도 다음 순위를 +1 해서 순서를 주 는 DENSE() OVER()함수가 있다.

Q. 전 사원의 급여와 함께 각 부서의 최고 급여를 함께 표시하시오.

select first_name, department_id, salary, 
first_value(salary) over(partition by department_id 
order by salary DESC) "high_sal_dept" from employees;

Q. SA% 부서 직원들의 월급과 해당 부서의 최대, 최소 급여를 함께 보이시오 (first_value, max_value 2가지 함수활용)
1) first_value 함수의 활용

select first_name, job_id, salary, first_value(salary) over(order by salary desc) as FIRST_SAL, 
last_value(salary) over() as LAST_SAL from employees
where job_id LIKE 'SA%' ;

* first_value(salary) over(order by salary desc) : 월급을 정렬하고 첫번째 값을 가져온다

2) max_value 함수의 활용

select first_name, job_id, salary, max(salary) over() as FIRST_SAL, min(salary) over() as LAST_SAL from employees
where job_id LIKE 'SA%' ;