[ORACLE] 분석함수

이번엔 분석함수에 대해 알아 볼 것 이다.

---

분석함수 
 

• 분석함수는 테이블의 행에 대해서 특정 그룹별로 집계 값을 산출할 때 주로 사용된다. GROUP BY를 사용하면 이 GROUP BY에 의한 최종 쿼리 결과는 그룹별로 행이 줄어들게 되는데 집계함수를 사용하면 행의 손실 없이 그룹별로 집계값을 산출할 수 있다. 분석함수는 집계함수에 속한다. 
    
• 분석함수는 기존의 select 만으로는 해결하기 어려운 문제를 쉽게 처리해준다. 예를 들어 다량의 데이터를 대상으로 누적 합계, 부서별 순위, 구간별 평균 등을 구할 때-하나 이상의 결과를 필요로 함-select 만 사용하면 구문이 복잡해질 뿐만 아니라 성능도 보장하기 어려울 수 있다. 이럴 때 분석함수를 사용하면 간결하게 해결할 수 있다. 
    
• 분석함수는 행의 집단에 대해서 연산이 이뤄진다는 점에서 일반 그룹함수와 유사하지만 일반 그룹함수는 하나의 집단에 대해서 하나의 결과가 나오지만 분석함수는 하나의 집단에 대해서 여러 기준을 적용해서 여러 개의 결과가 나올 수 있다. 이때 처리 대상이 되는 행의 집단(그룹)을 윈도우(window)라고 부른다. 
  
    일반적으로 분석함수는 join, where, group by, having 등이 처리한 결과에 대해서 분석함수를 적용시키고, 마지막에 order by를 적용한다. 따라서 분석함수는 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(), avg() over()
• row_number() over()
• rank() over() 등 

 

• 분석함수는 FIRST_VALUE() OVER(), COUNT() OVER(), SUM() OVER(), LAST _VALUE() OVER(), RANK() OVER(), ORDER BY 절에서 지정된 행의 순위로 각 행에 1부터 순서를 부여하는 ROW_NUMBER() OVER(), 그리고 값의 그룹에서 값의 순위를 계산할 때 RANK() 함수와 다르게 같은 순위가 둘 이상 있어도 다음 순위를 +1로 해서 순서를 주는 DENSE_RANK() OVER() 등이 있다.

---

●예제

 

●년도별 입사자들의 최대, 최소 급료를 보이시오.
select to_char(hire_date, 'yyyy'), count(employee_id), max(salary), min(salary) from employees
group by to_char(hire_date, 'yyyy')
order by to_char(hire_date, 'yyyy');

select to_char(hire_date, 'yyyy'), count(employee_id), max(salary), min(salary) from employees
group by to_char(hire_date, 'yyyy')
order by to_char(hire_date, 'yyyy');

●2000년대 이후의 입사자들에 대한 job_id 급여 합과 해당 직무의 직원 수를 보이시오.
select job_id, count(employee_id), sum(salary) from employees
where to_char(hire_date, 'yyyy') >=2000
group by job_id;

select job_id, count(employee_id), sum(salary) from employees
where to_char(hire_date, 'yyyy') >=2000
group by job_id;

●각 부서별로 급료가 가장 높은 직원 세명을 보이시오.
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 desc, last_name; // sub query

select last_name, salary from employees
where salary > (select avg(salary) from employees);//sub query

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 desc, last_name;  // sub query

select last_name, salary from employees
where salary > (select avg(salary) from employees); //sub query

row_number() 함수는 행에서 첫 줄을 보인다. 숫자를 지정한다면 rn <=3 식으로 하면 3명까지 보인다.

● rank()함수는 순위를 표시하는데 정렬을 해야 하므로  뒤에 over(order by ~),  within group(order by~) 구문이 함께 오는 경우가 많다. over는 전치사로써 '~에 대하여' 정도의 의미이다.
select employee_id, salary, rank () over(order by salary desc) "PERDONAL_SAL_RANK" from employees
where salary >=10000;

select employee_id, salary, rank () over(order by salary desc) "PERDONAL_SAL_RANK" from employees
where salary >=10000;

● 급여가 $3000인 사원의 월급 순위를 보이시오.
select rank(3000) within group(order by salary desc) "3000_ranked" from employees;

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

● 부서별로 월급 상위자를 rank()함수로 보이시오.
/* select department_id, first_name, salary, rank() over(partition by department_id order by salary desc) from employees;
order by를 밖으로 빼면 부서별로 상위자로 나온다(더 큰 단위 느낌). 안에 넣으면 부서별 안에 월급 상위자가 나온다.
/* select department_id, first_name, salary, rank() over(partition by department_id order by salary desc) from employees order by salary desc; 
//이것과 차이 를 알아둘것

select department_id, first_name, salary, 
rank() over(partition by department_id order by salary desc) from employees;

select department_id, first_name, salary, 
rank() over(partition by department_id order by salary desc) 
from employees order by salary desc; 
//이것과 차이 를 알아둘것

분석함수는 select절이나 order by 절에만 나타날 수 있다.
*하나의 행에 대해서 하나의 결과가 나오는것을 단일 행 함수
*여러행 에 대해서 하나의 결과가 나오는 것을 그룹함수,
*여러행에 대해서 여러결과가 나오는 것을 분석함수(집계함수) 라고 정리 

first_value() over()~대한 첫번째 값
last_value() over()~대한 꼴찌값
count() over() ,max() over(), min() over(), sum() over(), avg() over()
row_number() over()
tank() over() 

● 전 사원의 급여와 함께 각 부서의 최고 급여를 함께 표시하시오.*
/*select  first_name, department_id, salary, 
first_value(salary) over(partition by department_id order by salary desc) "high_sal_deprt" from employees;

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

select  first_name, department_id, salary, 
max(salary) over(partition by department_id order by salary desc) "high_sal_deprt" from employees; */

select  first_name, department_id, salary, 
max(salary) over(partition by department_id order by salary desc) "high_sal_deprt" 
from employees; */

●SA%부서 직원들의 월급과 해당 부서의 최대, 최소 급여를 함께 보이시오.
select first_name, job_id, salary, first_value(salary) over (order by salary desc) as FIRST_SAL, last_value(salary) over(order by salary desc) as LAST_SAL from employees
where job_id LIKE 'SA%';
별도로 where 에 조건을 줬기 때문에 over에 조건을 주지 않음 대신 over에 조건을 안줬대도 over안쓰면 오류

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

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%'; // min,max 와 fist_vlaue, last_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%';

● 부서별(department_id), 직급별(job_id) 급여 합계와 급여 합계 가 가장 큰 금액을 보이시오.
select department_id, sum(salary) as SAL_SUM, 
max(sum(salary)) over(partition by department_id) as DEPT_SAL_SUM from employees
group by department_id;// 쿼리가 정상으로 실행되어도 결과값이 신뢰가 안될수도 있다.

select department_id, sum(salary) as SAL_SUM, 
max(sum(salary)) over(partition by department_id) as DEPT_SAL_SUM from employees
group by department_id;