day29-DB: sql

1. 그룹 함수 - group by, having, rollup, cube, grouping

-- 그룹 함수 AVG, MAX, MIN, SUM, COUNT
select AVG(salary), MAX(salary), MIN(salary), SUM(salary), count(*) from employees;

select max(hire_date), min(hire_date) from employees; -- 날짜에도 가능

-- count(*), count(컬럼)
select count(*) from employees; -- 총 행 데이터의 수
select count(commission_pct), count(manager_id) from employees; -- null이 아닌 행의 수

-- 주의할 점: 그룹 함수가 일반 컬럼과 동시에 출력 불가
select employee_id, sum(salary) from employees;
select sum(employee_id), sum(salary) from employees;

-- 부서별 급여 평균(여기)
select department_id, avg(salary) from employees group by department_id;

-- group 절에 묶이지 않은 컬럼은 select 절에 사용 불가(데이터 베이스 별로 다름)
select job_id, department_id from employees group by department_id; -- 오류

-- 2개 이상의 그룹핑
select job_id, department_id, sum(salary) 
from employees 
group by department_id, job_id 
order by department_id desc;

select department_id, 
        job_id, 
        sum(salary), 
        count(*) as 그룹별수, --그룹별 수
        count(*) over() as 전체행수 -- 전체 행수를 같이 출력 
from employees
group by department_id, job_id
order by department_id desc;

select department_id, 
        job_id,
        count(*) over() as 전체행수 -- 전체 행 수 ( 그룹 없이 사용 가능)
from employees;

-- where절에 그룹 함수 사용 불가
select department_id
from employess
where sum(salary) >= 5000
group by department_id;

-- having절(그룹의 조건)
select department_id, sum(salary)
from employees
group by department_id
having sum(salary) > 100000;

select job_id, count(*)
from employees
group by job_id
having count(*) >= 20;

-- 부서 아이디가 50이상인 것들을 그룹화 시키고, 그룹 평균 중 5000이상만 조회, 정렬 평균 내림 차순
select department_id, trunc(avg(salary)) as 평균
from employees
where department_id >= 50
group by department_id 
having avg(salary) >= 5000 -- and department_id >= 50로 연결해도 됨 
order by avg(salary) desc;

-- 직무 sa가 포함된 데이터의 그룹별 사원수, 그룹별 급여의 합

select job_id, count(*) as 사원수, sum(salary) as 급여의합
from employees
where job_id like 'SA%'
group by job_id;

-- 롤업 - 주 그룹의 토탈
-- 그룹핑 1개 > 총계 출력
select department_id, 
sum(salary)
from employees
group by rollup(department_id)
order by department_id;

-- 그룹핑 2개 > 총계와 주그룹의 토탈
select department_id, job_id, avg(salary), count(salary)
from employees
group by rollup(department_id, job_id)
order by department_id, job_id;

-- 큐브 - 주, 서브 그룹의 토탈
select department_id, job_id, avg(salary), count(salary) -- 주 그룹이 department_id 서브 그룹이 job_id
from employees
group by cube(department_id, job_id)
order by department_id, job_id;

-- grouping 
-- GROUPING 함수는 ROLLUP, CUBE에 모두 사용할 수 있다. GROUPING 함수는 해당 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 
-- 0을 반환하고, ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 된다.
-- 따라서 해당 Row가 결과집합에 의해 산출된 Data 인지, ROLLUP이나 CUBE에 의해서 산출된 Data 인지를 알 수 있도록 지원하는 함수이다.

select department_id, 
        decode( grouping(job_id), 1, '소계', job_id ), 
        avg(salary), 
        count(salary), -- 주 그룹이 department_id 서브 그룹이 job_id
        grouping(department_id),
        grouping(job_id)
from employees
group by rollup (department_id, job_id)
order by department_id, job_id;

-- 예제
-- 문제 1. 사원 테이블에서 JOB_ID별 사원 수를 구하세요.
-- 사원 테이블에서 JOB_ID별 월급의 평균을 구하세요. 월급의 평균 순으로 내림차순 정렬하세요
select job_id, count(*) as 사원수
from employees
group by job_id;

select job_id, AVG(salary) as 평균월급
from employees
group by job_id
order by 평균월급 desc;

-- 문제 2. 사원 테이블에서 입사 년도 별 사원 수를 구하세요.
select substr(hire_date, 1, 2), count(hire_date) as 입사년도별사원수
from employees
group by hire_date
order by hire_date;

-- 선생님 정답
select to_char(hire_date, 'yy') as 입사년도, count(*) as 사원수
from employees
group by to_char(hire_date, 'yy');

-- 문제 3.급여가 1000 이상인 사원들의 부서별 평균 급여를 출력하세요.
-- 단 부서 평균 급여가 2000이상인 부서만 출력
select department_id, avg(salary) as 부서별평균급여
from employees
where salary >= 1000
group by (department_id)
having avg(salary) >= 2000; -- 그룹 함수는 having에 씀

-- 선생님 정답
select department_id, avg(salary)
from employees
where salary >= 1000
group by department_id
having avg(salary) >= 2000;

-- 문제 4. 사원 테이블에서 commission_pct(커미션) 컬럼이 null이 아닌 사람들의 
-- department_id(부서별) salary(월급)의 평균, 합계, count를 구합니다.
-- 조건 1) 월급의 평균은 커미션을 적용시킨 월급입니다.
-- 조건 2) 평균은 소수 2째 자리에서 절삭 하세요.
select department_id, trunc(avg(salary), 2) + comission_pct, sum(salary), count(salary)
from employees
where commission is not null;


-- 선생님 정답
select department_id,
        trunc(avg(salary + salary * commission_pct), 2),
        sum(salary + salary * commission_pct),
        count(*)
from employees
where commission_pct is not null
group by department_id;

-- 문제 5. 직업별 월급합, 총합계를 출력하세요
select job_id, sum(salary)
from employees
group by(job_id)
order by job_id;

-- 선생님 정답
select decode(grouping(job_id), 1, '합계', job_id) as job_id,
        sum(salary)
from employees
group by rollup(job_id);

-- 문제 6. 부서별, JOB_ID를 그룹핑 하여 토탈, 합계를 출력하세요.
-- GROUPING() 을 이용하여 소계 합계를 표현하세요
select 
department_id, 
        decode( grouping(job_id), 1, '소계', job_id) as job_id,
        count(department_id),
        sum(salary)
from employees
group by rollup (department_id, job_id)
order by sum(salary);

-- 선생님 정답
select
decode(grouping(department_id), 1, '합계', department_id) as department_id,
decode(grouping(job_id), 1, '소계', job_id) as job_id,
count(*) as total,
sum(salary) as sum
from employees
group by rollup (department_id, job_id)
order by sum;

 

테이블 - 새 테이블 - + 키 눌러서 추가 -  info 지우고 보내주신 파일 오라클 hr에 넣기 - 새로 고침