[DB] GROUP BY

<그룹 함수>

AVG(column)	평균 select AVG(salary) from employees;
SUM(column)	합 select SUM(salary) from employees;
MIN(column)	최소값 select MIN(first_name) from employees;
MAX(column)	최대값 select MAX(first_name) from employees;
COUNT	COUNT(*) = null값 포함하여 체이블 행 수 반환 COUNT(name) = null값 제외 행 수 반환
STDDEV	표준편차 STDDEV_SAMP = 표본 표준편차 출력 STDDEV_POP = 모표준편차 출력
VARIANCE	분산 VAR_SAMP = 표본 분산 출력 VAR_POP = 모분산 출력

 

<GROUP BY>

=데이터를 그룹 지어주는 쿼리

 

group by절에 없는 절은 select절에 직접 올 수 없다

ex) select first_name, sum(salary) from employees group by department_id;  라고 하면

first_name 에서 에러난다.

 

ex) 부서별 이름 개수, salary합을 출력하라

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

 

ex) 하나이상의 열로 그룹화가 가능하다

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

 

< HAVING>

group by  절 조건을 설정하고 싶으면 where말고 having으로 설정해야한다,

 

ex) 부서 급여 평균이 8000 초과인 부서의 번호와 급여 평균 출력

select department_id, round(avg(salary),2)
from employees group by department_id 
having avg(salary)>8000;

 

ex) 부서 급여 평균이 8000을 초과하는 각 직무에 대해 직무와 급여 평균을 출력. 단 Sales 직무 사원 제외

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);

 

<GROUPING SETS>

union all과  group by의 결합된 형태.

union all 보다 간결해지고 성능이 좋다

 

구조

select ...
from table
where ...
group by grouping sets();

 

예제

1. union all로 작성

select to_char(department_id), round(avg(salary),2) 
from employees 
group by department_id
union all
select job_id, round(avg(salary),2)
from employees 
group by job_id;

2. grouping sets로 작성

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

 

<ROLLUP, CUBE>

= group by 결과를 집계

 

ROLLUP

= group by로 묶은 그룹 별 합계 계

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>

=하나의 컬럼에 대해 원래 null인지 집계된 후 null인지 확인

집계 되어 null이면 1(즉 원래 값은 null이 아닌 것), 원래값이 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 average
from employees
group by cube(department_id,job_id);

<GROUPING ID>

= 여러 컬럼에 대해 집계된 후 null인지 원래 값이 null인지 확인

grouping_id(column1, column2 ...)

select 
    decode(grouping_id(department_id, job_id),2,'소계',3,'합계',to_char(department_id))as 부서번호,
    decode(grouping_id(drpartment_id, job_id),1,'소계',3,'합계',job_id) as 직무,
    grouping_id(department_id, job_id) as GID,
    round(avg(salary),2)as average
from employees
group by CUBE(department_id, job_id);

grouping_id값은 여러개 지정할 수 있다.

순서대로 null이면 1, null이 아니면 0을 return한다.

 

그러므로 나올 수 있는 모든 경우의 값은 아래와 같다.

department_id	job_id	return
1	0	10 => 2
0	1	01 => 1
0	0	00 => 0
1	1	11 =>3