SQL

[SQL][TIL] GROUP BY, ROLLUP, CUBE

breadz 2021. 7. 9. 21:48

 

GROUP BY 절

 

 

그룹 함수는 행 집합 연산을 수행하여 그룹별로 하나의 결과를 산출한다. (AVG, COUNT, MAX, MIN, SUM)

GROUP BY 절을 사용하여 테이블의 행을 더 작은 그룹으로 나눌 수 있다.

그룹 함수에 속하지 않는 SELECT list의 모든 열은 GROUP BY 절에 있어야 한다.

GROUP BY 열은 SELECT list에 없어도 된다

예) 리터럴값 100은 SELECT list에 없어도, 모든 사원의 급여의 합계인 sum(sal)은 출력됨

select sum(sal)
from emp
group by 100;

 

 

 

 

  group by rollup group by cube
group by 절 column 수 = N일때,
group by + rolllup 의 수행 결과

 

※ grouping 함수 : 컬럼의 값이 의미가 있으면 0을, 의미가 없으면 1을 출력 / '~~별' 합계를 구할거다는 말임

의미가 있다는거 => 부서번호별, 직무별.    '~~별' 합계를 구한 값이라는 의미

의미가 없다는거 => 합계를 구할 때, '~~별'에 들어가지 않는다.

※ grouping_id 함수 : grouping된 값들(2진수 : 0 아니면 1의 값)의 합을 10진수로 나타낸 값

select deptno, job, sum(sal)
    ,grouping(deptno) gp_dn
    ,grouping(job) gp_jo
    ,grouping_id(deptno, job) gid
from emp
group by rollup(deptno, job);

 

 

 

 

Q. EMP 테이블의 급여의 합과, 평균을 다음과 같이 출력하시오.

① group by 에 있는 '100'은 리터럴 값. 

상수 100은 EMP 테이블의 SAL 컬럼과 아무 상관없는 값임. 

상수 100으로 GROUP화한다는건 일단 아무 의미 없음. 그냥 SUM값 그대로 출력.

select sum(sal)
from emp
group by 100;

 

② 상수 100에 ROLLUP을 하면,

▶상수 100에 의해 집계된(상수 100이 의미가 있어짐),

▶상수 100에 의해 집계된게 아닌(상수 100과는 무관한, 의미가 없는, 그냥 SUM값 그대로를) 값을 출력하는거다.

// select절의 출력값이 한줄 더 생성된다고 생각하면 된다.

// (그치만 두 행의 의미는 완전히 다르다. 상수 100이 의미가 있냐, 없냐!)

select sum(sal)
from emp
group by rollup(100);

 

③ 이를, GROUPING 함수로 출력해보면, 아래 출력화면과 같이

두 행의 의미가 전혀 다른 것을 확인할 수 있다.

select sum(sal)
     , grouping(100)
from emp
group by rollup(100);

 

④ GROUPING으로 출력된 0과 1을 이용해서, 그룹함수 SUM과 AVG를 두개의 행으로 출력할 수 있지 않을까..?

// decode 연산자를 활용

select 
    decode(grouping(100), 0, sum(sal), round(avg(sal),5)) compute
from emp
group by rollup(100);

 

 

Q. EMP 테이블을 활용해서 30번 부서에서 근무하는 사원 정보를 검색하면서, 급여 합계와 평균을 다음과 같이 출력 하시오.!!

select deptno, empno, ename
     , decode(grouping(1), 0, sum(sal), round(avg(sal),2)) sal
from emp
where deptno = 30
group by rollup (1, (deptno, empno, ename));

 

Q. EMP 테이블의 사원 정보를 검색하면서 각 부서별 급여 합계와 평균, 전체 급여 합계와 평균을 다음과 같이 검색하시오.

select deptno, empno
    ,decode(grouping_id(100,deptno,200), 0, nvl(ename,'DEPT_SUM')
                                       , 1, 'DEPT_AVG'
                                       , 3, 'TOT_SUM'
                                       , 7, 'TOT_AVG'
            ) ename
    ,decode(grouping_id(100,deptno,200), 0, sum(sal)
                                       , 1, round(avg(sal),1)
                                       , 3, sum(sal)
                                       , 7, round(avg(sal),1)
            ) sal
    ,grouping(100)
    ,grouping(deptno)
    ,grouping(200)
    ,grouping_id(100,deptno,200)
from emp
group by rollup (100, deptno, 200, (empno, ename))
order by deptno;

 

저작자표시