[SQL] 4. 그룹 함수를 이용한 데이터 집계

그룹함수

1. AVG, SUM, MIN, MAX(평균, 합, 최소, 최대값)

select avg(salary),sum(salary),min(salary),max(salary)
from employees
where job_id like 'SA%';

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

select min(first_name), max(last_name)
from employees;

select max(salary)
from employees;

2. COUNT

count(*)
count(expr)

select count(*) from employees;
select count(commission_pct) from employees;

 

3. STDDEV, VARIANCE

select sum(salary) as 합계,
round(avg(salary),2) as 평균,
round(stddev(salary),2) as 표준편차,
round(variance(salary),2) as 분산 
from employees;

4. 그룹 함수와 NULL 값

- count(*)를 제외한 모든 그룹 함수는 열에 있는 NULL값을 연산에서 제외

select
round(sum(salary*commission_pct),2) as sum_bonus,
count(*) count,
round(avg(salary*commission_pct),2) as avg_bonus1,
round(sum(salary*commission_pct)/count(*),2) as avg_bonus2
from employees;

select avg(nvl(salary*commission_pct,0))
from employees;

 

GROUP BY

SELECT column, group_function(column)
FROM table
[WHERE condition(S)]
[GROUP BY group_by_expression]
[ORDER BY {column | expr[[ASC]|DESC],...};

 

1. GROUP BY 사용

select department_id, avg(salary)
from employees
group by department_id;

2. 하나 이상의 열로 그룹화

select department_id, job_id, sum(salary)
from employees
group by department_id, job_id;

3. 그룹 함수를 잘못 사용한 질의

- select 절의 그룹 함수가 아닌 모든 열이나 표현식은 group by 절에 있어야 한다.

- where 절을 사용해 그룹을 제한할 수 없다. having 사용

 

HAVING

- 그룹 제한하기 위해 사용

SELECT coulmn, group_function(column)
FROM table
[WHERE condition(s)]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY {column|expr[[ASC]|DESC]]}];

1. HAVING 사용

select department_id, avg(salary)
from employees
group by department_id
having avg(salary) > 2000;

select job_id, avg(salary) payroll
from employees
where job_id not like 'SA%'
group by job_id
having avg(salary) > 8000
order by avg(salary) desc;

 

GROUPING SETS

SELECT coulmn, group_function(column)
FROM table
[WHERE condition(s)]
[GROUP BY [GROUPING SETS] (group_by_expression)]
[HAVING group_condition]
[ORDER BY {column|expr[[ASC]|DESC]]}];

select department_id, job_id, round(avg(salary),2)
from employees
group by grouping sets(department_id, job_id)
order by department_id, job_id;

select department_id, job_id, manager_id, round(avg(salary),2) as avg_Sal
from employees
group by grouping sets((department_id, job_id),
(job_id, manager_id))
order by department_id, job_id, manager_id;

 

ROLLUP, CUBE

1. ROLLUP, CUBE사용

select department_id,job_id, round(Avg(salary),2), count(*)
from employees
group by department_id, job_id
order by department_id, job_id;
--각 부서의 직업별 평균만 알 수 있다.
--각 부서의 전체 평균을 알고싶다면 rollup사용

select department_id,job_id, round(Avg(salary),2), count(*)
from employees
group by rollup(department_id, job_id)
order by department_id, job_id;

- 여기에 직무별 평균과 사원의 수도 출력하고 싶으면 cube 사용

select department_id,job_id, round(Avg(salary),2), count(*)
from employees
group by cube(department_id, job_id)
order by department_id, job_id;

 

GROUPING

- ROLLUP, CUBE, GROUPING SETS 등 GROUP BY 확장 연산으로 소계의 일부로 생성된 null값일 경우 1을 반환

- 저장된 null값이거나 다른 값일 경우 0 반환

select
decode(grouping(department_id), 1, '소계',
to_Char(department_id)) as 부서,
decode(grouping(job_id),1,'소계',job_id) as 직무,
round(avg(salary),2) as 평균,
count(*) as 사원의수
from employees
group by cube(department_id, job_id)
order by 부서, 직무;

 

GROUPING_ID

- GROUPING_ID는 행과 연관된 GROUPING 비트 벡터에 해당하는 숫자를 반환

- GROUPING_ID를 사용하면 그룹화 수준 식별 가능

GROUPING_ID(column, column2,...)

select
decode(grouping_id(department_id, job_id),
2, '소계', 3, '합계',
to_char(department_id)) as 부서번호,
decode(grouping_id(department_id, job_id),
1,'소계',3,'합계',
job_id) as 직무,
grouping_id(department_id, job_id) as gid,
round(avg(salary),2) as 평균,
count(*) as 사원수
from employees
group by cube(department_id, job_id)
order by 부서번호, 직무;