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
出力結果
IsGrouping AggKey cnt SumSuuryou
---------- ------ --- ----------
1 Total 12 1674
0 AAA 5 18
0 BBB 4 440
0 CCC 3 1216grouping_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全集" インデックスに戻る
無断転載を禁ず
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。
Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。
Oracle ACE の1人。
OracleSQLパズルの運営者。