SQLで小計と合計を取得する (SQL: Subtotal and Grand Total)

帳票を1枚ずつカスタマイズして作成する場合には問題になりませんが、帳票ツールや部品によっては、出力内容を1つのデータセット（またはSQL）として受け渡す必要がある場合があります。
これにさらに「小計と合計を出力」という要件が加わり、非効率なSQLの記述を余儀なくされているケースがあります。

非効率なSQLの例と結果、および実行計画を以下に示します。この例では、集計値を表示するために同一表への検索が３回実行されます。

List 1:

select * from ( select deptno, job, sum(sal) sum_sal from emp group by deptno, job union select deptno, null as job, sum(sal) sum_sal from emp group by deptno union select null as deptno, null as job, sum(sal) sum_sal from emp ) order by deptno, job; Rows Row Source Operation ------- --------------------------------------------------- 13 SORT ORDER BY (cr=21 pr=0 pw=0 time=0 us cost=15 size=480 card=15) 13 VIEW (cr=21 pr=0 pw=0 time=12 us cost=14 size=480 card=15) 13 SORT UNIQUE (cr=21 pr=0 pw=0 time=12 us cost=14 size=190 card=15) 13 UNION-ALL (cr=21 pr=0 pw=0 time=24 us) 9 HASH GROUP BY (cr=7 pr=0 pw=0 time=8 us cost=5 size=165 card=11) 12 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=11 us cost=3 size=180 card=12) 3 HASH GROUP BY (cr=7 pr=0 pw=0 time=8 us cost=5 size=21 card=3) 12 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=11 us cost=3 size=84 card=12) 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us cost=4 size=4 card=1) 12 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=3 size=48 card=12) 

出力例は以下の通りです。

Figure 1: Subtotal and Grand Total

今回はOracleのSQL構文で小計と合計をより少ない負荷で出力する手法を確認します。


【データ】
データはscott.emp表を使用します。

Figure 2: scott.emp

【例】
基本となる構文は非常にシンプルで、group by句にrollupを追加するだけです。
rollupに列を指定することで、指定した列を単位とする集計を得ることができます。
集計の出力される行は、集計の単位となる列の値がNULLで出力されます。

例と出力結果は以下の通りです。この例では、deptno列の集計、つまり合計が出力されます。合計はdeptno列がNULLで出力されています。

List 2:

select deptno, sum(sal) sum_sal from emp group by rollup (deptno) order by deptno; 

Figure 3

以下の例ではjob列の集計（小計）と、deptno列の集計（合計）が出力されます。冒頭の例と同じ結果を得ることができます。
job列毎の小計はjob列がNULLで出力されています。また、合計はdeptno, job列ともにNULLで出力されています。

List 3:

select deptno, job, sum(sal) sum_sal

  from emp

 group by rollup (deptno, job)

 order by

       deptno, job; 

Figure 4

【集計行の識別】
rollupの指定で生成された行を識別するために、grouping関数が提供されています。
grouping関数は、集計行の場合に数値型の１、それ以外の行ではゼロを戻します。
上述のSQLを以下のように修正します。集計行の該当する列に１が出力されていることが確認できます。

List 4:

select deptno, job, sum(sal) sum_sal, grouping(deptno), grouping(job)

  from emp
 group by rollup (deptno, job)
 order by
       deptno, job;

Figure 5: Usage of GROUPING function

grouping関数とdecodeを組み合わせた例を以下に示します。
この例では、集計行に「Subtotal」および「Grand Total」の文字列を表示します。

List 5:

select deptno,

       case
         when grouping(deptno) = 1 then
              '[Grand Total]'
         when grouping(job) = 1 then
              '[Sub Total]'
         else job
       end job,

       sum(sal) sum_sal

  from emp

 group by rollup (deptno, job)

 order by

       deptno, job;

Figure 6: DECODE with GROUPING

【性能】
rollupの使用による性能への影響を確認します。
120万件のデータに対して、通常のgroup byとgroup by rollup()の問い合わせをかけた結果のトレースは以下の通りです。

List 6:

******************************************************************************** select deptno, job, sum(sal) sum_sal from emp2 group by deptno, job order by deptno, job call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.54 0.53 15 8580 0 9 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.54 0.53 15 8580 0 9 ... Rows Row Source Operation ------- --------------------------------------------------- 9 SORT GROUP BY (cr=8580 pr=15 pw=0 time=0 us cost=284 size=135 card=9) 1200000 TABLE ACCESS FULL EMP2 (cr=8580 pr=15 pw=0 time=2297470 us cost=245 size=18000000 card=1200000) ******************************************************************************** select deptno, job, sum(sal) sum_sal from emp2 group by rollup (deptno, job) order by deptno, job call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.54 0.56 17 8580 0 13 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.54 0.56 17 8580 0 13 ... Rows Row Source Operation ------- --------------------------------------------------- 13 SORT GROUP BY ROLLUP (cr=8580 pr=17 pw=0 time=0 us cost=284 size=195 card=13) 1200000 TABLE ACCESS FULL EMP2 (cr=8580 pr=17 pw=0 time=2089726 us cost=245 size=18000000 card=1200000)

集計処理による負荷の増大はトレースには明確には表れていません。通常の使用においては、性能への影響はおおむね無視できると考えられます。

なお、サンプルの120万件のデータは以下のスクリプトで生成しました。

List 7:

create table emp2 ( empno number(4), ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2), pageno number(10,0), -- page number 1..100000 dummycol number(10,0) -- always 1 );



declare
  cursor c is select * from emp order by deptno, empno;
  i pls_integer;
begin
  for i in 1..100000 loop
    for r in c loop
      insert into emp2 values (r.empno, r.ename, r.job, r.mgr, r.hiredate, r.sal, r.comm, r.deptno, i, 1);
    end loop;
  end loop;
  commit;
end;
/

【編集後記】
当ブログには「Oracle、小計」の検索でたどり着く方が多いのですが、SQLではなくBI Publisherの出力の説明（「小計と合計 (Subtotal and Grand total)」）でガッカリした、という声を複数いただきました。今回の記事が参考になれば幸いです。
また、group byには、この他、クロス集計値を求めるcubeも指定可能です。必要に応じて下記マニュアルを参照してください。
Oracle Database SQL言語リファレンス 11gリリース2（11.2）B56299-02：
http://download.oracle.com/docs/cd/E16338_01/server.112/b56299/statements_10002.htm#i2182483

---

[Summary]
Some times requirement like below forces you to write heavy SQL.

• Requirement: Get the data, Subtotals and the Grand Total in one SQL (or dataset).

Please see List 1 for the bad example.  The SQL executes same query three times.

You can achieve this requirement with group by rollup().

[Example]
Figure 2 shows the original data.
The syntax is very simple and easy to use.  All you need to do is just append rollup after group by.
List 2 and Figure 3 show how to get the summary row as Grand Total.
List 3 and Figure 4 show Subtotal sample with group by rollup (deptno, job).


[grouping function]
Oracle provides grouping function to identify the summary row.
grouping returns 1 on the summary row, and returns zero on the other rows.  Please see List 4 and Figure 5.
List 5 and Figure 6 shows the usage with decode function.


[Performance]
List 6 shows the trace of group by and group by rollup().
There seems few performance overhead.


[Reference]
Oracle Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-03
http://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_10002.htm#i2182483