図でイメージするOracle DatabaseのSQL全集 indexページ▶▶
Oracle ACE
山岸 賢治(やまぎし けんじ)
SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。
今回は、下記の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句 |
Oracle Database 11g Release 11.2.0.1.0 (windows 32ビット版)
通常のgroup by句による集計では、総計や小計を取得できないのですが、RollUp集計やgrouping sets集計やcube集計を使うと、総計や小計を取得できます。
帳票作成のselect文で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文で取得してます。
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文で取得しています。
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の単位を複数列がまとまった単位にできます。(複合列と呼ばれます)
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文で取得しています。
grouping sets集計を使うと、集計したい列の組み合わせを指定することができます。
データ件数とSuuryouの合計を、以下の組み合わせで集計してみます。
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集計での空括弧で指定できます)
cube集計を使うと、集計単位にしたり、しなかったりする列を指定することができます。
Suuryouの合計と内訳を、以下の集計パターンの組み合わせで集計してみます。
集計パターン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文で取得しています。
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
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
マニュアル --- データ・ウェアハウスにおける集計のためのSQL
【セミナー動画/資料】効果的な集計処理ことはじめ (オラクルエンジニア通信)
"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る
Copyright © 2011, Oracle Corporation Japan. All rights reserved. 無断転載を禁ず |
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。 Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。 |
山岸 賢治(やまぎし けんじ)
Oracle ACEの1人。
OracleSQLパズルの運営者。