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