トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

7-32 キーブレイク時に、指定行を出力その1

SQLパズル

empテーブル
dname       empno
----------  -----
accounting  9999
accounting  1111
research    5555
research    4444
research    3333
sales       8888
sales       7777
sales       6666

empテーブルを以下のように出力する。

order by dname,empno descでソートし、
dnameが変更されたら、**の行を出力

出力結果
dname       empno
----------  -----
accounting  9999
accounting  1111
**          **
research    5555
research    4444
research    3333
**          **
sales       8888
sales       7777
sales       6666
**          **

データ作成スクリプト

create table emp(
dname char(10),
empno char(4));

insert into emp values('accounting','9999');
insert into emp values('accounting','1111');
insert into emp values('research'  ,'5555');
insert into emp values('research'  ,'4444');
insert into emp values('research'  ,'3333');
insert into emp values('sales'     ,'8888');
insert into emp values('sales'     ,'7777');
insert into emp values('sales'     ,'6666');
commit;

SQL

--■■■union allを使う方法■■■
select nvl2(empno,dname,'**') as dname,
nvl(empno,'**') as empno
from (select dname,empno from emp
      union all select distinct dname,null from emp) a
order by a.dname,empno desc nulls Last;

--■■■rollupを使う方法1■■■
select dname,empno
from (select case grouping(empno) when 1 then '**' else dname end as dname,
             case grouping(empno) when 1 then '**' else empno end as empno,
      Row_Number() over(order by dname,grouping(empno),empno desc) as sortKey
        from emp
      group by rollup(dname,empno)
      having grouping_ID(dname,empno) != 3)
order by sortKey;

--■■■rollupを使う方法2■■■
select dname,empno
from (select
      case grouping(empno) when 1 then '**' else dname end as dname,
      case grouping(empno) when 1 then '**' else empno end as empno,
      Row_Number() over(order by dname,grouping(empno),empno desc) as sortKey
        from emp
      group by dname,rollup(empno))
order by sortKey;

--■■■grouping setsを使う方法■■■
select dname,empno
from (select case grouping(empno) when 1 then '**' else dname end as dname,
             case grouping(empno) when 1 then '**' else empno end as empno,
      Row_Number() over(order by dname,grouping(empno),empno desc) as sortKey
        from emp
      group by grouping sets((dname,empno),dname))
order by sortKey;

--■■■model句を使う方法(10g以降)■■■
select dname,empno
  from emp
 model
 partition by (dname as PID)
 dimension by (dname,empno)
 measures(0 as SortKey)
 rules(SortKey['**','**'] = 1)
order by PID,SortKey,dname,empno;

解説

union allを使う方法では、
nvl2関数と、nvl関数と、インラインビューを組み合わせてます。

3-29 grouping setsで総合計を取得

Printing a blank line??

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
初めてのSQLの152ページに書いてありますが、
Oracleでは、group by ColA,rollup(ColB) という指定ができます。

group_by_clause::=










まず、rollupを無視した組み合わせをイメージし、
rollupによる小計となる組み合わせをイメージすると分かりやすいようです。

小計の作成イメージ
・rollup(ColA)は、order by ColAのソート順でブレークするごとに超集合を作成する。
・rollup(ColA,ColB)は、order by ColA,ColBのソート順でブレークするごとに超集合を作成する。
・rollup(ColB,ColA)は、order by ColB,ColAのソート順でブレークするごとに超集合を作成する。

create table rollupTest(ColA,ColB) as
select 1,2 from dual union all
select 1,3 from dual union all
select 1,4 from dual union all
select 2,1 from dual union all
select 2,2 from dual;

--■■■実験1■■■
select ColA,ColB,sum(ColA) as SumA,sum(ColB) as SumB
  from rollupTest
group by ColA,rollup(ColB);

ColA  ColB  SumA  SumB
----  ----  ----  ----
   1     2     1     2
   1     3     1     3
   1     4     1     4
   1  null     3     9
   2     1     2     1
   2     2     2     2
   2  null     4     3

7行が選択されました。

--■■■実験2(この場合は、実験1と同じ結果になります)■■■
select ColA,ColB,sum(ColA) as SumA,sum(ColB) as SumB
  from rollupTest
group by rollup(ColB),ColA;

ColA  ColB  SumA  SumB
----  ----  ----  ----
   1     2     1     2
   1     3     1     3
   1     4     1     4
   1  null     3     9
   2     1     2     1
   2     2     2     2
   2  null     4     3

7行が選択されました。

--■■■実験3■■■
select ColA,ColB,sum(ColA) as SumA,sum(ColB) as SumB
  from rollupTest
group by rollup(ColA,ColB);

ColA  ColB  SumA  SumB
----  ----  ----  ----
   1     2     1     2
   1     3     1     3
   1     4     1     4
   1  null     3     9
   2     1     2     1
   2     2     2     2
   2  null     4     3
null  null     7    12

8行が選択されました。

--■■■実験4■■■
select ColA,ColB,sum(ColA) as SumA,sum(ColB) as SumB
  from rollupTest
group by rollup(ColB,ColA);

ColA  ColB  SumA  SumB
----  ----  ----  ----
   2     1     2     1
null     1     2     1
   1     2     1     2
   2     2     2     2
null     2     3     4
   1     3     1     3
null     3     1     3
   1     4     1     4
null     4     1     4
null  null     7    12

10行が選択されました。

--■■■実験5■■■
select ColA,ColB,
grouping(Cola) as GA,
grouping(Colb) as GB
  from rollupTest
group by rollup(ColA),rollup(ColB);

COLA  COLB  GA  GB
----  ----  --  --
   1  null   0   1
   2  null   0   1
null  null   1   1
   2     1   0   0
null     1   1   0
   1     2   0   0
   2     2   0   0
null     2   1   0
   1     3   0   0
null     3   1   0
   1     4   0   0
null     4   1   0

12行が選択されました。



iAnywhere.com - ROLLUP の使用
GROUP BY ROLLUP (A,B,C) は、4つの組み合わせを定義 1+3=4
(A, B, C)
(A, B)
(A)
( )

iAnywhere.com - CUBE の使用
GROUP BY CUBE (A,B,C) は、8つの組み合わせを定義 2*2*2=8

(A, B, C)
(A, B)
(A, C)
(A)
(B, C)
(B)
(C)
( )



DB2 V9.7 --- SQL Reference, Volume 1の762ページからの例
GROUP BY ROLLUP (A,B) は、3つの組み合わせを定義 1+2=3
(A, B)
(A)
( )

GROUP BY ROLLUP (A, (B,C)) は、3つの組み合わせを定義 1+2=3
(A, B, C)
(A)
( )

GROUP BY A,ROLLUP (B,C) は、3つの組み合わせを定義 1+2=3
(A, B, C)
(A, B)
(A)

GROUP BY A,B ROLLUP (C,D) は、3つの組み合わせを定義 1+2=3
(A, B, C, D)
(A, B, C)
(A, B)

GROUP BY ROLLUP (A),ROLLUP (B,C) は、6つの組み合わせを定義 (1+1)*(1+2)=6
(A, B, C)
(A, B)
(A)
(B, C)
(B)
( )

GROUP BY ROLLUP (A),CUBE (B,C) は、8つの組み合わせを定義 (1+1)*(2*2)=8
(A, B, C)
(A, B)
(A, C)
(A)
(B, C)
(B)
(C)
( )

GROUP BY CUBE (A,B),ROLLUP (C,D) は、12の組み合わせを定義 (2*2)*(1+2)=12
(A, B, C, D)
(A, B, C)
(A, B)
(A, C, D)
(A, C)
(A)
(B, C, D)
(B, C)
(B)
(C, D)
(C)
( )



RollUpは、(1+式の数)の組み合わせを作ります
RollUp(a,b,c)   なら1+3=4
RollUp(a,(b,c)) なら1+2=3

Cubeは、2の(式の数)乗の組み合わせを作ります。
cube(a,b,c)   なら2*2*2=8
cube(a,(b,c)) なら2*2=4
10-301 rollupで複合列(Composite Columns)指定
10-312 複合列指定を使ったクロス集計