[데이터베이스] 19일차 - 집합함수, 분석함수, 집합

오라클 7일차

 

참고 >> 집합함수 (Aggregate Functions)

 

참고 >> 분석함수 (Analytic Functions)

 

2012/08/22 - [Dev. 데이터베이스/Oracle 관련] - [ORACLE 강좌] 오라클 함수 - 집합함수 ( Aggregate functions )

 

2012/08/22 - [Dev. 데이터베이스/Oracle 관련] - [ORACLE 강좌] 오라클 함수 - ROLLUP, CUBE, GROUPING

 

2012/08/22 - [Dev. 데이터베이스/Oracle 관련] - [ORACLE 강좌] 오라클 함수 - 분석함수 ( Analytic Functions )

 

2012/08/22 - [Dev. 데이터베이스/SQL 예제] - [SQL 문제] 함수(FUNCTION) 통합 테스트

 

2012/08/22 - [Dev. 데이터베이스/SQL 예제] - [SQL 문제] 집합함수, group by 이용 간단 테스트 예제

 

** 대표적인 집합함수 **

AVG
COUNT
MAX
MIN
RANK
SUM

** 대표적인 분석함수 (over + 분석절이 들어가면 분석함수, 집합함수와 같은것이 많다) **

AVG *
FIRST_VALUE *
LAST_VALUE *
MAX *
MIN *
RANK
ROW_NUMBER
SUM *

 

 

-- 복수행 함수 ( 집합함수, 분석함수 )
-- 집합함수 ( Aggregate functions )
-- 집합함수와 일반 컬럼은 같이 쓸 수 없다.

-- avg
-- 직원들의 평균 월급
select * from employees;

select avg(salary)
from employees;

-- count
-- 월급을 5000이상 받는 직원수
select count(employee_id), max(salary), min(salary)
from employees
where salary >= 5000;

-- group by
-- 그룹으로 잡힌 컬럼명은 사용가능하다
select department_id, count(employee_id) "부서별 인원수", avg(salary) "부서별 월급평균", sum(salary) "부서별 월급합계"
from employees
group by department_id;

-- having
-- group by 에 대한 조건을 설정할 경우 사용


-- 각 직업별 인원과 월급의 평균을 구해서 월급 평균의 내림차순으로 정렬하시오
-- (단, 인원이 3명 이상인 직업만 가져올 것)
select job_id "직업", count(employee_id) "인원", avg(salary) "월급 평균"
from employees
group by job_id
having count(employee_id) >= 3
order by 3 desc;

-- 분석함수
-- 분석함수는 일반 함수, 일반 컬럼과 같이 쓸수 있다!
-- 순위 구하기 : rank, dense_rank, row_number, count
-- rank
select first_name, salary, rank() over (order by salary desc) ranking
from employees;

-- dense_rank
select first_name, salary, dense_rank() over (order by salary desc) ranking
from employees;

-- row_number
select first_name, salary, row_number() over (order by salary desc) ranking
from employees;

-- count
select first_name, salary, count(*) over (order by salary desc) ranking
from employees;

-- partition by : 어떤 기준으로 묶음을 나누는 것
select first_name, department_id, salary,
rank() over (partition by department_id
order by salary desc) "부서별 월급 랭킹"
from employees;

select first_name, salary, sum(salary) over (order by salary desc)
from employees;

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

-- 부서별 월급 랭킹 TOP 3 를 출력하라
select *
from (
  select first_name, department_id, salary, 
  rank() over (partition by department_id order by salary desc) rank
  from employees
)
where rank<4;

-- 1. 직업별 이름(first_name), 월급, 월급 순위를 출력하세요.
-- 2. 입사 년도별 이름, 월급, 월급 순위를 출력하세요.
-- 3. 80번 부서 직원들의 직업별 이름, 연봉(월급*12 + 월금*커미션), 연봉순위를 출력
-- 4. 관리자별(manager_id), 직업별 부하사원들의 이름과 월급, 월급 순위 출력
-- 5. 월급이 5000이상인 사람들 중에 부서별 입사일 순위가 5위 이상만 출력
select * from employees;

--1
select job_id 직업, first_name 이름, salary 월급,
rank() over (partition by job_id order by salary desc) 월급순위
from employees;

--2
select extract(year from hire_date) 입사년도, first_name 이름, salary 월급, 
rank() over (partition by extract(year from hire_date) order by salary desc) 월급순위
from employees;

--3
select 이름, 직업이름, 연봉,
rank() over(order by 연봉 desc)
from (
  select department_id 부서번호, first_name 이름, job_id 직업이름,
  salary*12+salary*nvl(commission_pct,0) 연봉
  from employees
)
where 부서번호 = 80;

--4 
select manager_id 관리자, job_id 직업, first_name 이름, salary 월급,
rank() over(partition by manager_id, job_id order by salary desc) 월급순위
from employees
order by manager_id;

--5
select *
from (
  select first_name 이름,
  rank() over(partition by department_id order by hire_date) 입사일순위
  from employees
  where salary >= 5000
)
where 입사일순위>5;



-- windowing clause : 값이 선택되는 기준(범위), order by와 항상 같이 쓰임
-- first_value는 윈도잉절이 반드시 필요한것은 아니지만 last_value는 반드시 필요
select first_name, salary,
--first_value(salary) over (order by salary desc rows 3 preceding)
first_value(salary) over (order by salary desc range 2000 preceding)
from employees;

-- last_value (windwing절과 반드시 같이 사용!)
select first_name, salary,
last_value(salary) over (order by salary desc rows between 1 preceding and 1 following) value
from employees;

-- rollup, cube ( group by 와 같이 사용 )
-- pseudo column (의사컬럼 : 실제로는 존재하지 않으나 특수목적으로 사용)
select department_id, job_id, sum(salary), grouping(department_id), grouping(job_id)
from employees
group by rollup(department_id, job_id);     -- 전체 합계 표시

-- cube
select department_id, job_id, sum(salary), grouping(department_id), grouping(job_id)
from employees
where department_id is not null
group by cube(department_id, job_id);     -- 전체 합계 표시


-- 집합 : 합집합(UNION), 교집합(INTERSECT), 차집합(MINUS)
-- 결합시 데이터 타입과 갯수를 맞추어야 한다
 select first_name, salary||''
 from employees
 where salary > 5000
 --union all
 union
 --intersect
 --minus
 select first_name, last_name
 from employees
 where hire_date < '2003/01/01';