영우 글로벌 러닝 4차혁명 교육과정의 "방형욱" 강사님과 함께 공부한 내용입니다.

# Rollup

 

  - 컬럼의 개수가 n개면 n+1 가지 종류의 결과 생성

  - 컬럼의 나열 순서가 중요함

 

  select deptno, job, sum(sal)

  from emp

  group by deptno, job

 

  select deptno, job, sum(sal)

  from emp

  group by ROLLUP(deptno, job);

 

  select deptno, job, sum(sal)

  from emp

  group by GROUPING SETS((deptno, job), (deptno), ());

 

  cf. select deptno, decode(deptno, null, 'TOTAL', decode(job, null, 'DEPT_SUM', job)) as job, sum(sal)

      from emp

      group by ROLLUP(deptno, job);

 

# Cube

 

  - 컬럼의 개수가 n개면 2^n 가지 종류의 결과 생성

  - 컬럼의 나열 순서가 중요하지 않음

 

  select deptno, job, sum(sal)

  from emp

  group by CUBE(deptno, job);

 

  select deptno, job, sum(sal)

  from emp

  group by GROUPING SETS((deptno, job), (deptno), (job), ());

 

# 집계 기준 컬럼이 3개일 경우

 

  drop table emp2 purge;

 

  create table emp2

  as

  select  empno, ename, sal, job, deptno, decode(mod(empno, 2), 0, 'M', 'W') as gender

  from emp e

  union all

  select  empno, ename, sal+100, job, deptno, decode(mod(empno, 2), 0, 'W', 'M') as gender

  from emp e;

 

  select * from emp2;

 

    --

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by ROLLUP(deptno, job, gender)

  order by 1, 2, 3;

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by GROUPING SETS((deptno, job, gender), (deptno, job), (deptno), ())

  order by 1, 2, 3;

 

    --

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by CUBE(deptno, job, gender)

  order by 1, 2, 3;

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by GROUPING SETS((deptno, job, gender), (deptno, job), (deptno, gender), (job, gender), (deptno), (job), (gender), ())

  order by 1, 2, 3;

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by GROUPING SETS((deptno, job, gender), (job, gender), ())

  order by 1, 2, 3;

 

# GROUPING 함수

 

  drop table emp3 purge;

 

  create table emp3 as select * from emp;

 

  update emp3 set job = null where rownum = 1;

 

  select * from emp3;

 

  => grouping 함수를 사용하지 않을 경우 아래와 같이 질의 결과가 이상해집니다.

 

  select deptno, job, sum(sal) as sum_sal

  from emp3

  group by ROLLUP(deptno, job);

 

  select *

  from (select deptno, job, sum(sal) as sum_sal

        from emp3

        group by ROLLUP(deptno, job))

  where deptno is not null

  and job is null;

 

  => grouping 함수를 사용하면 이렇게 달라집니다.

 

  select deptno, job, sum(sal) as sum_sal,

         grouping(deptno) as g_deptno,

         grouping(job)    as g_job

  from emp3

  group by ROLLUP(deptno, job);

 

  select deptno, job, sum_sal

  from (select deptno, job, sum(sal) as sum_sal,

               grouping(deptno) as g_deptno,

               grouping(job)    as g_job

        from emp3

        group by ROLLUP(deptno, job))

  where g_deptno = 0

  and   g_job    = 1;

 

  select deptno, job, sum_sal

  from (select deptno, job, sum(sal) as sum_sal,

               grouping(deptno) as g_deptno,

               grouping(job)    as g_job

        from emp3

        group by ROLLUP(deptno, job))

  where g_deptno = 0

  and   g_job    in (0, 1);

 

# Composite column

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by deptno, job, gender;

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by rollup(deptno, job, gender);

 

    --> deptno, job, gender

    --> deptno, job

    --> deptno

    --> ()

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by rollup(deptno, (job, gender));

 

    --> deptno, job, gender

    --> deptno

    --> ()

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by rollup((deptno, job), gender);

 

    --> deptno, job, gender

    --> deptno, job

    --> ()

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by rollup((deptno, job, gender));

 

    --> deptno, job, gender

    --> ()

 

# Concatenated Groupings

  select deptno, job, gender, sum(sal)

  from emp2

  group by deptno, rollup(job), cube(gender);

 

           deptno    job         gender

                     ()          ()

 

           --> deptno, job, gender

           --> deptno, job

           --> deptno, gender

           --> deptno

 

  select deptno, job, gender, sum(sal)

  from emp2

  group by GROUPING SETS((deptno, job, gender), (deptno, job), (deptno, gender), (deptno));

 

'IT 공방 > SQL' 카테고리의 다른 글

ORA-65096 오류 해결  (0) 2019.10.07

+ Recent posts