X

A blog about Oracle Technology Network Japan

Try Oracle Cloud Platform For Free
  • July 27, 2011

図でイメージするOracle DatabaseのSQL全集　第5回 RollUp集計など

Guest Author

図でイメージするOracle DatabaseのSQL全集　indexページ▶▶

 

Oracle SQLの各機能をイメージ図を交えて解説

Oracle ACE
山岸 賢治（やまぎし けんじ）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ビット版)

 

RollUp集計とは


通常のgroup by句による集計では、総計や小計を取得できないのですが、RollUp集計やgrouping sets集計やcube集計を使うと、総計や小計を取得できます。
帳票作成のselect文でRollUp集計がよく使われます。

 

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文で取得してます。

 

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文で取得しています。

 

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の単位を複数列がまとまった単位にできます。(複合列と呼ばれます)

 

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文で取得しています。

 

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集計での空括弧で指定できます)

 

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を集計単位にするパターンとしないパターン

 

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

 

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

 

参考リソース

マニュアル --- データ・ウェアハウスにおける集計のためのSQL
【セミナー動画/資料】効果的な集計処理ことはじめ (オラクルエンジニア通信)

"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る

Copyright © 2011, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

山岸 賢治山岸 賢治（やまぎし けんじ）
Oracle ACEの1人。
OracleSQLパズルの運営者。

ページトップへ戻る▲

 

図でイメージするOracle DatabaseのSQL全集　indexページ▶▶

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content