Oracle 분석 함수, 집합 함수

분석함수 : avg(salary) over(partition by ~ )

집합함수 : sum(), min(), max(), count() 하나 이상의 행을 입력받아 각각 구하는 함수들

​

분석_함수() OVER(PARTITION BY 컬럼/ ORDER BY 컬럼/ 세부 분할 기준)

하나의 행 하나의 결과 단일 행 함수

여러 행 하나의 결과 그룹함수

여러 행 여러 결과 분석함수(집계함수)

분석함수는 집합함수에 속한다.

분석함수를 이용하여 하나의 집단에 대해 여러 기준을 적용해서 여러개의 결과가 나올 수 있는데,

이때, 처리 대상이 되는 행의 집단(그룹)을 윈도우(window)라고 부른다.

​

select department_id, salary from

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

/* Sub Query */

where rn <= 3

order by department_id, salary desc, last_name;

​

row_number() 행에서 첫 줄을 보인다.

rn <= 3을 이용하여, 3명까지 보인다.

​

/* Sub Query */ // select 문장 안에 서브로 들어가 있는 문장.

select last_name, salary from employees

where salary > 8000 or (select avg(salary) from employees)

​

rank() 순위를 표시하는데, 정렬을 해야하므로,

보통 뒤에 over(order by ), within group(order by ) 와 함께 사용

over() : 정렬이나 처리가 완료되지 않은 값을 정리할 때 사용

within group() : 이미 정렬되거나 처리 완료 된 것들을 정리할 때 사용

// rank(), dense_rank() 는 order by 필수 (dense_rank() - 중복 순위가 있어도 1씩 증가)

​

 

select employee_id, salary, rank() over(order by salary desc) "PERSONAL_SAL_RANK"
from employees
where salary >= 10000
EMPLOYEE_ID SALARY PERSONAL_SAL_RANK

100 24000 1
101 17000 2
102 17000 2
145 14000 4

select employee_id, salary, rank() over(order by salary desc, employee_id) "PERSONAL_SAL_RANK"
from employees
where salary >= 10000
// 중복된 Rank() 에서 employee_id 기준으로 순위를 정리.

​

----------------------------------------------------------------------------------------------------------------------------

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

​

select rank(3000) within group (order by salary) "3000_ranked"
from employees
// rank(3000)으로 이미 3000이라는 조건을 주었으므로, within group ()을 사용해야 한다.

부서별 월급 상위자를 rank() 함수로 보이시오

select salary, department_id, first_name, rank() over(partition by department_id order by salary desc),
count(department_id) over(partition by department_id)
from employees
// count는 그냥 몇명인지 확인하려고 해석해보려고 넣은 것임
// 부서별 인원들의 salary rank()를 확인

 

----------------------------------------------------------------------------------------------------------------------------

​

fire_value() over() ~ 대해 첫번째 값

last_value() over() ~대해 마지막 값

count() over(), max() over(), min() over(), sum() over(),

avg() over(), row_number() over(), rank() over()

​

----------------------------------------------------------------------------------------------------------------------------

​

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

​

select department_id, salary, first_value(salary) over(partition by department_id order by salary desc)
from employees;
// partition by 로 department_id (부서별)로 나누고, order by 로 최고 급여를 표시.

select last_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() as FIRST_SAL,
last_value(salary) over() as LAST_SAL from employees
where job_id LIKE 'SA%'
/* 두리 같앙
select last_name, salary,
max(salary) over(),
min(salary) over() from employees
where job_id LIKE 'SA%'
*/

select last_name, job_id, salary,
first(salary) over(partition by job_id order by salary desc)
last(salary) over(partition by job_id order by salary desc)
from employees
where job_id LIKE 'SA%'
// over(partition by job_id order by salary desc) 을 사용할 수 없음. 문법 오류.

first(salary) 가 partition by job_id로 묶일 수 없음