[SQL 쿡북] 12 보고서 작성과 재구성하기

12.1 결과셋을 하나의 행으로 피벗하기

 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
        sum(case when deptno=30 then 1 else 0 end) as deptno_30
   from emp

select deptno,
       case when deptno=10 then 1 else 0 end as deptno_10,
       case when deptno=20 then 1 else 0 end as deptno_20,
       case when deptno=30 then 1 else 0 end as deptno_30
  from emp
 order by 1

행에서 열로 변환 되었다.

select deptno,
       sum(case when deptno=10 then 1 else 0 end) as deptno_10,
       sum(case when deptno=20 then 1 else 0 end) as deptno_20,
       sum(case when deptno=30 then 1 else 0 end) as deptno_30
  from emp
 group by deptno
--------------------------------------------------------------------
select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
       sum(case when deptno=20 then 1 else 0 end) as deptno_20,
       sum(case when deptno=30 then 1 else 0 end) as deptno_30
  from emp

그룹화하여 확인하고 결과를 확인한다.

 

select max(case when deptno=10 then empcount else null end) as deptno_10
       max(case when deptno=20 then empcount else null end) as deptno_20,
       max(case when deptno=10 then empcount else null end) as deptno_30
  from (
select deptno, count(*) as empcount
  from emp
 group by deptno
       ) x

인라인 뷰를 통한 방법도 있다. 

 

12.2 결과셋을 여러 행으로 피벗하기

순위 함수 ROW_NUMBER OVER를 사용하여 고유한 JOB/ENAME 조합을 만듭니다. 윈도우 함수에서 반환된 값을 그룹화하는 동안 CASE 식과 집계 함수 MAX를 사용하여 결과셋을 피벗합니다.

 

12.3  결과셋 역피벗하기

열을 행으로 변환하려면 데카르트 곱을 사용합니다. 데카르트 곱을 만드는 데 사용하는 테이블 표현식에는 최소한 전치하려는 열 개수만큼의 카디널리티가 있어야 하므로, 행으로 변환할 열의 수를 미리 알아야 합니다.

select dept.deptno,
      case dept.deptno 
      	   when 10 then emp_cnts.deptno_10
           when 20 then emp_cnts.deptno_20
           when 30 then emp_cnts.deptno_30
      end as counts_by_dept
  from emp_cnts cross join
       (select deptno fro dept where deptno <= 30) dept

 

12.4 결과셋을 한 열로 역피벗하기

 with four_rows (id)
  as
(select 1
From dual
  union all
  select id+1
  from four_rows
  where id < 4
  )
  ,
  x_tab (ename,job,sal,rn )
  as
  (select e.ename,e.job,e.sal,
 row_number()over(partition by e.empno
 order by e.empno)
  from emp e
  join four_rows on 1=1)

   select case rn
  when 1 then ename
  when 2 then job
  when 3 then cast(sal as char(4))
 end emps
  from x_tab

select e.ename,e.job,e.sal,
       row_number()over(partition by e.empno
                  order by e.empno) rn
from emp e
 where e.deptno=10
--------------------------------------------------------------------
with four_rows (id)
  as
(select 1
From dual
  union all
  select id+1
  from four_rows
  where id < 4
  )
 select e.ename,e.job,e.sal,
 row_number()over(partition by e.empno
 order by e.empno)
  from emp e
  join four_rows on 1=1

첫 번째 단계는 윈도우 함수 ROW_NUMBER OVER를 사용하여 DEPTNO 10에 속한 사원에 대한 순위를 만드는 것입니다.

EMPNO로 분할하므로 DEPTNO 10의 세 행 모두에 대해 순위가 1입니다. 데카르트 곱을 추가하면 다음 결과에 표시된 것처럼 순위가 형성되기 시작합니다.

12.5 결과셋에서 반복값 숨기기

Oracle 
 select to_number(
           decode(lag(deptno)over(order by deptno),
                 deptno,null,deptno)
        ) deptno, ename
   from emp

12.6 행 간 계산하는 결과셋 피벗하기

select deptno, sum(sal) as sal
  from emp
 group by deptno
--------------------------------------------------------------------
 select d20_sal - d10_sal as d20_10_diff,
        d20_sal - d30_sal as d20_30_diff
   from (
 select sum(case when deptno=10 then sal end) as d10_sal,
        sum(case when deptno=20 then sal end) as d20_sal,
        sum(case when deptno=30 then sal end) as d30_sal
   from emp
        ) totals_by_dept
--------------------------------------------------------------------
with totals_by_dept (d10_sal, d20_sal, d30_sal)
as
(select
          sum(case when deptno=10 then sal end) as d10_sal,
          sum(case when deptno=20 then sal end) as d20_sal,
          sum(case when deptno=30 then sal end) as d30_sal

from emp)

select   d20_sal - d10_sal as d20_10_diff,
         d20_sal - d30_sal as d20_30_diff
  from   totals_by_dept
--------------------------------------------------------------------
select case when deptno=10 then sal end as d10_sal,
       case when deptno=20 then sal end as d20_sal,
       case when deptno=30 then sal end as d30_sal
  from emp
--------------------------------------------------------------------
select sum(case when deptno=10 then sal end) as d10_sal,
       sum(case when deptno=20 then sal end) as d20_sal,
       sum(case when deptno=30 then sal end) as d30_sal
  from emp

 

12.7 고정 크기의 데이터 버킷 생성하기

select ceil(row_number()over(order by empno)/5.0) grp,
        empno,
        ename
   from emp
--------------------------------------------------------------------
select row_number()over(order by empno) rn,
       empno,
       ename
  from emp
--------------------------------------------------------------------
select row_number()over(order by empno) rn,
       row_number()over(order by empno)/5.0 division,
       ceil(row_number()over(order by empno)/5.0) grp,
       empno,
       ename
  from emp

윈도우 함수 ROW_NUMBER OVER를 사용하여 EMPNO별로 각 사원의 순위를 매깁니다. 그런 다음 5로 나누어 그룹을 만듭니다.

 

12.8 사전 정의된 수의 버킷 생성하기

이것은 다양한 분석을 하기 위한 중요한 첫 단계이자 범주형 데이터를 구성하는 일반적인 방법으로, 집합을 더 작은 동일 크기의 집합으로 나눕니다. 예를 들어 급여 또는 기타 값에 대한 그룹의 평균을 구하면, 개별적으로 사례를 볼 때 변동성에 의해 숨겨진 추세를 나타낼 수 있습니다.

 

이 레시피의 목표는 각 버킷에 몇 개의 요소가 있는지 알 수는 없지만, 생성할 고정(알려진) 버킷 수를 정의하는 것입니다.

 

NTILE는 버킷 수로 정렬하고, 첫 번째 버킷에서 시작하여 사용 가능한 버킷으로 분산된 값들이 잇는 집합을 구성합니다. 버킷 1과 2에는 4개의 행이 있고 버킷 3과 4에는 3개의 행이 있습니다.

 

 select ntile(4)over(order by empno) grp,
        empno,
        ename
   from emp

 

12.9 수평 히스토그램 생성하기

<Oracle, PostgreSQL, MySQL>
 select deptno,
        lpad('*',count(*),'*') as cnt
   from emp
  group by deptno

 

12.10. 수직 히스토그램 생성하기

 select max(deptno_10) d10,
         max(deptno_20) d20,
         max(deptno_30) d30
    from (
  select row_number()over(partition by deptno order by empno) rn,
         case when deptno=10 then '*' else null end deptno_10,
         case when deptno=20 then '*' else null end deptno_20,
         case when deptno=30 then '*' else null end deptno_30
    from emp
        ) x
  group by rn
  order by 1 desc, 2 desc, 3 desc

ROW_NUMBER OVER 함수를 사용하여 각 DEPTNO에 대해 *의 각 인스턴스를 고유하게 식별합니다. 집계 함수 MAX를 사용하여 결과셋을 피벗하고, ROW_NUMBER OVER에서 반환된 값을 기준으로 그룹화합니다.

 

12.11 비 GROUP BY 열 반환하기

 select deptno,ename,job,sal,
         case when sal = max_by_dept
              then 'TOP SAL IN DEPT'
              when sal = min_by_dept
              then 'LOW SAL IN DEPT'
         end dept_status,
         case when sal = max_by_job
              then 'TOP SAL IN JOB'
              when sal = min_by_job
             then 'LOW SAL IN JOB'
        end job_status
   from (
 select deptno,ename,job,sal,
        max(sal)over(partition by deptno) max_by_dept,
        max(sal)over(partition by job)   max_by_job,
        min(sal)over(partition by deptno) min_by_dept,
        min(sal)over(partition by job)   min_by_job
   from emp
        ) emp_sals
  where sal in (max_by_dept,max_by_job,
                min_by_dept,min_by_job)

인라인 뷰를 사용하여 DEPTNO 및 JOB별 최고 급여와 최저 급여를 찾습니다. 그런 다음 그 급여를 받는 사원만 남겨둡니다.

윈도우 함수 MAX OVER 및 MIN OVER를 사용하여 DEPTNO 및 JOB별로 최고 및 최저 급여를 찾습니다. 그런 다음 급여가 DEPTNO 또는 JOB별로 가장 높거나 가장 낮은 행을 남깁니다.

 

(SELECT 절에 여러 열을 포함하는) 그룹화는 MIN OVER 및 MAX OVER에서 반환되는 값에 영향을 주지 않습니다. 이것이 윈도우 함수의 장점입니다.

 

12.12 단순 소계 계산하기

<DB2와 Oracle>
 select case grouping(job)
             when 0 then job
             else 'TOTAL'
        end job,
        sum(sal) sal
   from emp
  group by rollup(job)

<SQL Server와 MySQL>
 select coalesce(job,'TOTAL') job,
        sum(sal) sal
   from emp
  group by job with rollup

<PostgreSQL>
select coalesce(job,'TOTAL') job,
        sum(sal) sal
   from emp
  group by rollup(job)

GROUP BY 절에 대한 ROLLUP 확장으로 이 문제를 완벽하게 해결합니다. RDBMS에서 ROLLUP을 사용할 수 없을 때는 스칼라 서브쿼리 또는 UNION 쿼리를 사용하여 문제를 조금 더 어렵게 해결할 수 있습니다.

 

12.13 가능한 모든 식 조합의 소계 계산하기

최근 몇 년 동안 GROUP BY에 추가된 확장 기능은 이 문제를 해결하기 매우 쉽게 만듭니다. 다양한 수준의 소계를 계산할 확장 기능을 플랫폼이 제공하지 않는 경우, (셀프 조인 또는 스칼라 서브쿼리를 통해) 수동으로 계산해야 합니다.

<MySQL>
select deptno, job,
       'TOTAL BY DEPT AND JOB' as category,
       sum(sal) as sal
  from emp
 group by deptno, job
--------------------------------------------------------------------
select deptno, job,
       'TOTAL BY DEPT AND JOB' as category,
       sum(sal) as sal
  from emp
 group by deptno, job
 union all
select null, job, 'TOTAL BY JOB', sum(sal)
  from emp
 group by job
--------------------------------------------------------------------
select deptno, job,
       'TOTAL BY DEPT AND JOB' as category,
       sum(sal) as sal
  from emp
 group by deptno, job
 union all
select null, job, 'TOTAL BY JOB', sum(sal)
  from emp
 group by job
 union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
  from emp
 group by deptno
--------------------------------------------------------------------
select deptno, job,
       'TOTAL BY DEPT AND JOB' as category,
       sum(sal) as sal
  from emp
 group by deptno, job
 union all
select null, job, 'TOTAL BY JOB', sum(sal)
  from emp
 group by job
 union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
  from emp
 group by deptno
 union all
select null,null, 'GRAND TOTAL FOR TABLE', sum(sal)
  from emp

1. 집계함수 SUM을 사용하고, DEPTNO 및 JOB별로 그룹화하는 것입니다.

2. UNION ALL을 사용하여 TOTAL BY JOB 합계를 추가합니다.

3. DEPTNO의 모든 급여 합계를 UNION ALL

4. UNION ALL을 사용하여 전체 급여의 합계를 추가합니다.

 

12.14 소계가 아닌 행 식별하기

일반 GROUP BY 절로 생성된 행과 CUBE 또는 ROLLUP을 사용하여 생성된 행을 구분할 방법이 필요합니다.

<PostgreSQL, DB2, Oracle>

select deptno, job, sal,
         grouping(deptno) deptno_subtotals,
         grouping(job) job_subtotals
    from emp
   group by cube(deptno,job)
--------------------------------------------------------------------
  select deptno, job, sum(sal) sal,
         grouping(deptno) deptno_subtotals,
         grouping(job) job_subtotals
    from emp
   group by deptno,job with cube

SQL server와 DB2 및 Oracle의 유일한 차이점은 CUBE/ROLLUP 절의 작성방식/ 책 지필 시점 MySQL은 CUBE 및 GROUPING을 지원하지 않습니다.

 

12.15 Case 표현식으로 행 플래그 지정하기

EMP 테이블의 JOB 열과 같은 열의 값을 일련의 불리언 플래그에 매핑하려고 합니다(mysql 적용으로 다소 다름 행이 몇개 들어가지 않음)

 

12.16 희소행렬 만들기

12.17 시간 단위로 행 그룹화하기

12.18 여러 그룹/파티션 집계를 동시 수행하기

select ename,
       deptno,
       count(*)over(partition by deptno) deptno_cnt,
       job,
       count(*)over(partition by job) job_cnt,
       count(*)over() total
  from emp
--------------------------------------------------------------------
count(*)over(partition by deptno)
--------------------------------------------------------------------
count(*)over(partition by job)
--------------------------------------------------------------------
count(*)over()

윈도우 함수는 WERE 절 다음에 적용된다. 윈도우 함수가 평가된 후 결과를 필터링하려면, 윈도 쿼리를 인라인 뷰로 만든 다음 해당 뷰에서의 결과를 필터링해야 한다.

 

12.19 값의 이동 범위에 대한 집계 수행하기

<Oracle>
    select hiredate,
           sal,
           sum(sal)over(order by hiredate
                           range between 90 preceding
                             and current row) spending_pattern
      from emp e

<MySQL>
  select hiredate,
          sal,
          sum(sal)over(order by hiredate
              range interval 90 day preceding ) spending_pattern
  from emp e

EMP 테이블의 급여에 대한 이동 합계와 같은 이동 집계를 계산.

윈도우 함수의 프레임 또는 윈도우 함수 절에서 이동 윈도를 지정할 수 있으면 이 문제를 쉽게 해결할 수 있습니다.

1. 처음 고용된 사원의 급여를 평가합니다. 첫 번째 사원보다 먼저 고용된 사원이 없으므로 이 시점의 합계는 단순히 첫 번째 사원의 급여입니다.

2. 다음 사원(HIREDATE 기준)의 급여가 평가됩니다. 이 사원의 급여는 최대 90일 이전에 고용된 다른 사원과 함께 이동 합계에 포함됩니다.

 

12.20 소계를 사용한 결과셋 피벗하기

<MySQL>
    select mgr,
          sum(case deptno when 10 then sal else 0 end) dept10,
          sum(case deptno when 20 then sal else 0 end) dept20,
          sum(case deptno when 30 then sal else 0 end) dept30,
          sum(case flag when '11' then sal else null end) total
     from (
    select  deptno,mgr,sum(sal) sal,
            concat( cast(grouping(deptno) as char(1)) ,
            cast(grouping(mgr) as char(1))) flag
   from emp
  where mgr is not null
   group by deptno,mgr with rollup
         ) x
   group by mgr;
--------------------------------------------------------------------
<SQL Server>
select deptno,mgr,sum(sal) sal
  from emp
 where mgr is not null
 group by mgr,deptno
 order by 1,2
--------------------------------------------------------------------
select deptno,mgr,sum(sal) sal
  from emp
 where mgr is not null
 group by deptno,mgr with rollup
--------------------------------------------------------------------
select deptno,mgr,sum(sal) sal,
       cast(grouping(deptno) as char(1))+
       cast(grouping(mgr) as char(1)) flag
  from emp
 where mgr is not null
 group by deptno,mgr with rollup
--------------------------------------------------------------------
select mgr,
       sum(case deptno when 10 then sal else 0 end) dept10,
       sum(case deptno when 20 then sal else 0 end) dept20,
       sum(case deptno when 30 then sal else 0 end) dept30,
       sum(case flag when '11' then sal else null end) total
  from (
select deptno,mgr,sum(sal) sal,
       cast(grouping(deptno) as char(1))+
       cast(grouping(mgr) as char(1)) flag
  from emp
 where mgr is not null
 group by deptno,mgr with rollup
       ) x
 group by mgr
 order by coalesce(mgr,9999)

1. GROUP BY에 대한 ROLLUP 확장을 사용하여 소계를 생성

2. 전통적인 피벗(집계 및 CASE 표현식)을 수행하여 보고서에 필요한 열을 만드는 것

3. RDBMS가 null값을 정렬하는 방법에 따라 이전 대상 결과셋처럼 보이도록 해법에 ORDER BY를 추가할 수도 있습니다.

 

12.21 마치며

데이터베이스는 데이터를 저장하기 위한 것이지만 결국 누군가 데이터를 검색하여 어딘가에 제시할 수 있어야 합니다. 사용자에게 필요한 형식으로 데이터를 제공하는 일반적인 목적 외에도, 이러한 기술은 데이터베이스 소유자에게 데이터 웨어하우스를 생성하는 데 중요한 역할을 합니다.