図でイメージするOracleのSQL全集の元原稿
第6回 階層問い合わせ
第4回 集約関数など
第5回 RollUp集計など
連載のテーマ,動作確認環境
前回と同じとなります。
今回のテーマ
今回は、下記のOracleのSQL文の評価順序においての、
6番目のgroup by句と9番目のselect句で主に使用される、RollUp集計などについて
私のSQLのイメージを解説します。
1番目 from句
2番目 where句 (結合条件)
3番目 start with句
4番目 connect by句
5番目 where句 (行のフィルタ条件)
6番目 group by句
7番目 having句
8番目 model句
9番目 select句
10番目 union、minus、intersectなどの集合演算
11番目 order by句
目次
1 RollUp集計とは
通常のgroup by句による集計では、総計や小計を取得できないのですが、
RollUp集計やgrouping sets集計やcube集計を使うと、総計や小計を取得できます。
帳票作成のselect文でRollUp集計がよく使われます。
2 RollUp集計(単数列)
RollUp集計で総計も取得
-- サンプルデータ作成
create table LogTable(
HanbaiDay date, -- 販売日
SyouhinCode char(3), -- 商品コード
MiseCode char(3), -- 店コード
Suuryou Number(3), -- 数量
primary key(HanbaiDay,SyouhinCode,MiseCode));
insert into LogTable
select date '2011-06-30','AAA','111', 1 from dual union all
select date '2011-06-30','AAA','222', 3 from dual union all
select date '2011-06-30','AAA','333', 5 from dual union all
select date '2011-06-30','BBB','111', 10 from dual union all
select date '2011-06-30','BBB','222', 30 from dual union all
select date '2011-06-30','CCC','222', 16 from dual union all
select date '2011-06-30','CCC','333',700 from dual union all
select date '2011-07-01','BBB','111',100 from dual union all
select date '2011-07-01','BBB','222',300 from dual union all
select date '2011-07-01','CCC','111',500 from dual union all
select date '2011-07-02','AAA','111', 2 from dual union all
select date '2011-07-02','AAA','222', 7 from dual;
HanbaiDayごとのデータ件数とSuuryouの合計を求めてみます。
通常のgroup by句を使用したselect文では、全体行を対象とした集計結果(総計)を取得できませんが、
RollUpを使用したselect文では、総計を取得できます。
-- 通常のgroup by句を使用したselect文
select HanbaiDay,count(*) as cnt,
sum(Suuryou) as SumSuuryou
from LogTable
group by HanbaiDay
order by HanbaiDay;
出力結果
HanbaiDay cnt SumSuuryou
---------- --- ----------
2011-06-30 7 765
2011-07-01 3 900
2011-07-02 2 9
-- RollUpを使用したselect文
select HanbaiDay,count(*) as cnt,
sum(Suuryou) as SumSuuryou
from LogTable
group by RollUp(HanbaiDay)
order by HanbaiDay;
出力結果
HanbaiDay cnt SumSuuryou
---------- --- ----------
2011-06-30 7 765
2011-07-01 3 900
2011-07-02 2 9
null 12 1674
group by句でgroup by RollUp(HanbaiDay) と指定することによって、
HanbaiDayごとの集計結果と、全体行を対象とした集計結果(総計)を
1つのselect文で取得してます。
3 RollUp集計(複数列)
RollUp集計で小計と総計も取得
HanbaiDay,SyouhinCodeごとのデータ件数とSuuryouの合計を求めてみます。
小計として、HanbaiDayごとの集計結果を取得し、
総計として、全体行を対象とした集計結果も取得します。
select HanbaiDay,SyouhinCode,
count(*) as cnt,sum(Suuryou) as SumSuuryou
from LogTable
group by RollUp(HanbaiDay,SyouhinCode)
order by HanbaiDay,SyouhinCode;
出力結果
HanbaiDay SyouhinCode cnt SumSuuryou
---------- ----------- --- ----------
2011-06-30 AAA 3 9
2011-06-30 BBB 2 40
2011-06-30 CCC 2 716
2011-06-30 null 7 765
2011-07-01 BBB 2 400
2011-07-01 CCC 1 500
2011-07-01 null 3 900
2011-07-02 AAA 2 9
2011-07-02 null 2 9
null null 12 1674
group by句でgroup by RollUp(HanbaiDay,SyouhinCode) と指定することによって、
HanbaiDay,SyouhinCodeごとの集計結果と、
HanbaiDayごとの集計結果(小計)と、
全体行を対象とした集計結果(総計)を、1つのselect文で取得してます。
4 RollUp集計(複合列)
複合列でRollUp
HanbaiDay,SyouhinCodeごとのデータ件数とSuuryouの合計を求めてみます。
総計として、全体行を対象とした集計結果も取得します。
select HanbaiDay,SyouhinCode,
count(*) as cnt,sum(Suuryou) as SumSuuryou
from LogTable
group by RollUp((HanbaiDay,SyouhinCode))
order by HanbaiDay,SyouhinCode;
出力結果
HanbaiDay SyouhinCode cnt SumSuuryou
---------- ----------- --- ----------
2011-06-30 AAA 3 9
2011-06-30 BBB 2 40
2011-06-30 CCC 2 716
2011-07-01 BBB 2 400
2011-07-01 CCC 1 500
2011-07-02 AAA 2 9
null null 12 1674
group by句でgroup by RollUp((HanbaiDay,SyouhinCode)) と指定することによって、
HanbaiDay,SyouhinCodeごとの集計結果と、
全体行を対象とした集計結果(総計)を、1つのselect文で取得してます。
group by RollUp((HanbaiDay,SyouhinCode))のように、
RollUpの列指定の括弧内で、複数列をカンマ区切りで記述し、
RollUpの単位を複数列がまとまった単位にできます。(複合列と呼ばれます)
5 group by 列指定,RollUp集計
通常の列指定と、RollUp集計の組み合わせ
HanbaiDay,SyouhinCodeごとのデータ件数とSuuryouの合計を求めてみます。
小計として、HanbaiDayごとの集計結果も取得します。
select HanbaiDay,SyouhinCode,
count(*) as cnt,sum(Suuryou) as SumSuuryou
from LogTable
group by HanbaiDay,RollUp(SyouhinCode)
order by HanbaiDay,SyouhinCode;
出力結果
HanbaiDay SyouhinCode cnt SumSuuryou
---------- ----------- --- ----------
2011-06-30 AAA 3 9
2011-06-30 BBB 2 40
2011-06-30 CCC 2 716
2011-06-30 null 7 765
2011-07-01 BBB 2 400
2011-07-01 CCC 1 500
2011-07-01 null 3 900
2011-07-02 AAA 2 9
2011-07-02 null 2 9
group by HanbaiDay,RollUp(SyouhinCode) と指定することによって、
HanbaiDay,SyouhinCodeごとの集計結果と、
HanbaiDayごとの集計結果を、1つのselect文で取得してます。
6 grouping sets集計
集計したい列の組み合わせを指定
grouping sets集計を使うと、集計したい列の組み合わせを指定することができます。
データ件数とSuuryouの合計を、以下の組み合わせで集計してみます。
・MiseCodeごとの集計
・HanbaiDay,SyouhinCodeごとの集計
・全体行を対象とした集計
select HanbaiDay,SyouhinCode,MiseCode,
count(*) as cnt,sum(Suuryou) as SumSuuryou
from LogTable
group by grouping sets(MiseCode,(HanbaiDay,SyouhinCode),())
order by MiseCode,HanbaiDay,SyouhinCode;
出力結果
HanbaiDay SyouhinCode MiseCode cnt SumSuuryou
---------- ----------- -------- --- ----------
null null 111 5 613
null null 222 5 356
null null 333 2 705
2011-06-30 AAA null 3 9
2011-06-30 BBB null 2 40
2011-06-30 CCC null 2 716
2011-07-01 BBB null 2 400
2011-07-01 CCC null 1 500
2011-07-02 AAA null 2 9
null null null 12 1674
group by grouping sets(MiseCode,(HanbaiDay,SyouhinCode),()) と指定することによって、
MiseCodeごとの集計結果と、HanbaiDay,SyouhinCodeごとの集計結果と、全体行を対象とした集計を、
1つのselect文で取得してます。
(全体行を対象とした集計は、grouping sets集計での空括弧で指定できます)
7 cube集計
集計単位にしたり、しなかったりする列を指定
cube集計を使うと、集計単位にしたり、しなかったりする列を指定することができます。
Suuryouの合計と内訳を、以下の集計パターンの組み合わせで集計してみます。
・集計パターン1 HanbaiDayを集計単位にするパターンとしないパターン
・集計パターン2 SyouhinCodeを集計単位にするパターンとしないパターン
集計パターン1は2通り。集計パターン2も2通りですので、
全部で2*2=4通りの集計パターンとなります。
select HanbaiDay,SyouhinCode,
sum(Suuryou) as SumSuuryou,
ListAgg(to_char(Suuryou),',') within
group(order by HanbaiDay,SyouhinCode) as AggBase
from LogTable
group by cube(HanbaiDay,SyouhinCode)
order by HanbaiDay,SyouhinCode;
出力結果
HanbaiDay SyouhinCode SumSuuryou AggBase
---------- ----------- ---------- ----------------------------------
2011-06-30 AAA 9 1,3,5
2011-06-30 BBB 40 10,30
2011-06-30 CCC 716 16,700
2011-06-30 null 765 1,3,5,10,30,16,700
2011-07-01 BBB 400 100,300
2011-07-01 CCC 500 500
2011-07-01 null 900 100,300,500
2011-07-02 AAA 9 2,7
2011-07-02 null 9 2,7
null AAA 18 1,3,5,2,7
null BBB 440 10,30,100,300
null CCC 1216 16,700,500
null null 1674 1,3,5,10,30,16,700,100,300,500,2,7
group by cube(HanbaiDay,SyouhinCode) と指定することによって、
以下の集計パターンの組み合わせを1つのselect文で取得してます。
・HanbaiDayを集計単位にするパターンとしないパターン
・SyouhinCodeを集計単位にするパターンとしないパターン
8 grouping関数
groupingされてる状態かを判定する
grouping関数は、引数に指定した列がgroupingされてる状態なら1を返し、そうでなければ0を返す関数です。
groupという動詞の現在分詞だと考えると理解しやすいと思います。
なお、groupingされてる状態というのは、
RollUp集計やgrouping sets集計やcube集計による複数の集計の中の、該当の集計において、
その列が集約単位になってない状態(複数の値が考えられ、NULLとして表示される)です。
grouping関数は、
総計や小計の表示順序を指定するためにorder by句で使用したり、
case式と組み合わせて総計や小計であることを明示するために使用されます。
上記のgrouping関数の使用例のサンプルとして、
SyouhinCodeごとのデータ件数とSuuryouの合計を求めてみます。
総計として、全体行を対象とした集計結果も取得します。
総計のSyouhinCodeは'Total'と表示し、総計は一番最初の行に表示します。
select grouping(SyouhinCode) as IsGrouping,
case grouping(SyouhinCode)
when 1 then 'Total' else SyouhinCode end as AggKey,
count(*) as cnt,sum(Suuryou) as SumSuuryou
from LogTable
group by RollUp(SyouhinCode)
order by grouping(SyouhinCode) desc,SyouhinCode;
出力結果
IsGrouping AggKey cnt SumSuuryou
---------- ------ --- ----------
1 Total 12 1674
0 AAA 5 18
0 BBB 4 440
0 CCC 3 1216
9 grouping_ID関数
複数のgrouping関数の結果を識別
grouping_ID関数は、引数に指定した複数列のgrouping関数の結果を順に並べた2進数を10進数に変換した値を返す関数です。
例えば、grouping_ID(Col1,Col2,Col3,Col4)は下記の計算結果を返します。
grouping(Col1)*8 + grouping(Col2)*4 + grouping(Col3)*2 +grouping(Col4)*1
grouping_ID関数は、case式で、複数のgrouping関数の結果をgrouping_IDで識別する。といった使用例があります。
上記のgrouping_ID関数の使用例のサンプルとして、
SyouhinCode,MiseCodeごとのデータ件数とSuuryouの合計を求めてみます。
小計として、SyouhinCodeごとの集計結果を取得し、
総計として、全体行を対象とした集計結果も取得します。
小計のSyouhinCodeは'SubTotal'と表示し、総計のSyouhinCodeは'Total'と表示します。
select case grouping_ID(SyouhinCode,MiseCode)
when 2+1 then 'Total'
when 0+1 then 'SubTotal'
else SyouhinCode end as SyouhinCode,
MiseCode,count(*) as cnt,
sum(Suuryou) as SumSuuryou
from LogTable
group by RollUp(SyouhinCode,MiseCode);
出力結果
SyouhinCode MiseCode cnt SumSuuryou
----------- -------- --- ----------
AAA 111 2 3
AAA 222 2 10
AAA 333 1 5
SubTotal null 5 18
BBB 111 2 110
BBB 222 2 330
SubTotal null 4 440
CCC 111 1 500
CCC 222 1 16
CCC 333 1 700
SubTotal null 3 1216
Total null 12 1674
参考リソース