やりたいこと

下図のイメージのように、左側のデータを基に、右側の集計表を作りたい、とする。ポイントは、テーブル全体の販売個数だけでなく、種類ごとの小計、種類と名前ごとの小計も出力する。さらに加えて、明細行も一緒に出す、というのをやりたい。

環境

• DB
  • CentOS-6.4-x86_64
  • Oracle Database Express Edition 11g Release 2
• ツール
  • Oracle SQL Developer 4.0(4.0.2.15.21)
  • rlogin Version 2.16.6

サンプル用のテーブルとデータ準備

DROP TABLE food_sales;
CREATE TABLE food_sales
  (
    type_of_food   VARCHAR2(20) NOT NULL,
    food_name       VARCHAR2(20) NOT NULL,
    unit_sales INT NOT NULL
  ) ;

INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', 'にんじん', '11');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', 'にんじん', '12');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', 'にんじん', '13');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', 'にんじん', '14');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', '玉葱', '21');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', '玉葱', '22');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', '玉葱', '23');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', 'キャベツ', '31');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('野菜', 'キャベツ', '32');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('果物', 'りんご', '101');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('果物', 'りんご', '102');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('果物', '桃', '202');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('果物', '桃', '202');
INSERT INTO food_sales (type_of_food, food_name, unit_sales) VALUES ('果物', '桃', '203');
COMMIT;

集計クエリ

SELECT
  COALESCE(type_of_food, '総計') AS as_type_of_food,
  (
    CASE
      WHEN food_name IS NULL                            THEN '小計'
      WHEN food_name IS NOT NULL AND unit_sales IS NULL THEN food_name || '(小計)' 
      ELSE food_name
    END
  )                             AS as_food_name,
  COALESCE(unit_sales, 1e9)     AS as_unit_sales,
  
  SUM(unit_sales),
  
  grouping(type_of_food) AS is_grouping_type_of_food,
  grouping(food_name)    AS is_food_name,
  grouping(unit_sales)   AS is_unit_sales

FROM food_sales

GROUP BY GROUPING SETS
  (
    (type_of_food),
    (type_of_food, food_name),
    (type_of_food, food_name, rownum, unit_sales),
    ()
  )

ORDER BY
  CASE
    WHEN type_of_food IS NULL THEN '1'
    ELSE                           '0' || type_of_food
  END ,
  CASE
    WHEN food_name    IS NULL THEN '1'
    ELSE                           '0' || food_name
  END ,
  unit_sales
;

実行イメージ

SQL Developer上での実行したときの結果はこんな感じ。

詳細

正直なんも知識無しでコレみても何やってるかサッパリ分からない自信が俺にはあるが、一つ一つ要素に分けていけばそんなに難しいことをやっているわけではない。

GROUP BY GROUPING SETS

総計・小計を一回で出す

この集計クエリの根幹を成すGROUPING SETSから。

GROUP BY GROUPING SETS
  (
    (type_of_food),
    (type_of_food, food_name),
    (type_of_food, food_name, rownum, unit_sales),
    ()
  )

これはROLLUPの派生で「グループ化する列の組み合わせ」を複数指定できる、というもの。詳細な解説は他サイトをぐぐるとして、ここでの動きを見ていく。上記は「合計4つの組み合わせ」を指定しているので、一つ一つ見ていく。

まず()が分かりやすいのでこれから。これは特殊な指定で「組み合わせ無し」を意味するので、GROUP BY無しで集計関数を実行したのと同じ結果になる。よって、下記の2つのクエリは同一の結果（一行だけの「989」」を返す。これは総計を意味する。

SELECT sum(unit_sales) FROM food_sales;

SELECT sum(unit_sales) FROM food_sales
GROUP BY GROUPING SETS
  (
    ()
  );

次に(type_of_food)について。これは「type_of_food列でグループ化」して集計する。そのため、GROUP BY type_of_foodで集計関数を実行したのと同じ結果になる。よって、下記2つのクエリは同一の結果（二行果物 810, 野菜 179）を返す。これはtype_of_foodごとの小計を意味する。

SELECT type_of_food, sum(unit_sales) FROM food_sales
GROUP BY type_of_food
;

SELECT type_of_food, sum(unit_sales) FROM food_sales
GROUP BY GROUPING SETS
  (
     (type_of_food)
  );

同様に(type_of_food, food_name)はGROUP BY type_of_food, food_nameと同様で、type_of_food, food_nameごとの小計を意味する。以下のクエリは同一の結果を返す。

SELECT type_of_food, food_name, sum(unit_sales) FROM food_sales
GROUP BY type_of_food, food_name
;

SELECT type_of_food, food_name, sum(unit_sales) FROM food_sales
GROUP BY GROUPING SETS
  (
    (type_of_food, food_name)
  );

GROUPING SETSには複数の組み合わせを記述できる。よって、上記のすべての組み合わせ(), (type_of_food), (type_of_food, food_name)を書くことができる。これはそれぞれ総計と小計に対応するので、総計・小計を一発で出せることになる。

SELECT type_of_food, food_name, sum(unit_sales) FROM food_sales
GROUP BY GROUPING SETS
  (
    (type_of_food),
    (type_of_food, food_name),
    ()
  );

SQL Developer上で実行結果はこんな感じ。

明細をGROUP BYで出す

以上で総計・小計を一回で出せるようになった。次は、明細をGROUP BYで出すことを考える。

まず、下記のようなGROUP BYを使用SQLを考えてみる。

SELECT   type_of_food, food_name, sum(unit_sales) FROM food_sales
GROUP BY type_of_food, food_name, ROWNUM
ORDER BY type_of_food, food_name
;

これはGROUP BYを指定しているものの、すべての行を返す。カギはROWNUMで、これがあるため、すべての行が一件で集約される。値が一意となる列で集約すると、すべての行は異なる値なので、すべての行の分だけ組み合わせが発生する。この状態でのSUMは一件を合計するだけなので、実質的にはその行を返しているのと同じになる。ちなみに一意な列があればROWNUMでなくても良い。unit_salesが一意なら良いがそうではないのでROWNUMにしている。

下記は（フツウは）意図しない結果が出てくる誤ったGROUPING SETSの指定。

GROUP BY GROUPING SETS
  (
    (type_of_food, food_name, unit_sales)
  )

(果物, 桃, 202)が2行あるので(type_of_food, food_name, unit_sales)でグループ化すると1行にまとめられてしまう。

というわけで下記のようなGROUPING SETSのクエリは、すべての行を返す。これでGROUP BYで明細を出せるようになった。

SELECT type_of_food, food_name, sum(unit_sales) FROM food_sales
GROUP BY GROUPING SETS
  (
    (type_of_food, food_name, ROWNUM)
  )
ORDER BY type_of_food, food_name
;

総計・小計・明細を一回で出す

というわけで、総計・小計・明細を出すGROUPING SETSのパターンは確認できた。GROUPING SETSには複数のグループ化パターンを指定可能なので、パターンをゼンブ一緒に書けば良い。

SELECT type_of_food, food_name, sum(unit_sales) FROM food_sales
GROUP BY GROUPING SETS
  (
    (type_of_food),
    (type_of_food, food_name),
    (type_of_food, food_name, ROWNUM),
    ()
  )
ORDER BY type_of_food, food_name
;

SQL Developer上での実行結果はこんな感じ。

これでほぼ完成。

grouping

groupingは、その結果行がGROUP BY GROUPING SETSの集計行かどうかを判別するために使用する。

  grouping(type_of_food) AS is_grouping_type_of_food,
  grouping(food_name)    AS is_food_name,
  grouping(unit_sales)   AS is_unit_sales

見た目の問題

ここから先はオプション。つまり、SQLでどこまで集計結果の装飾をやらせるか、という話になる。

総計・小計の表示

総計を表示する行は、集計項目以外の列値はnullになる。なので、下記は安直に左端の列がnullだったらそこに総計を表示するようにしている。

COALESCE(type_of_food, '総計') AS as_type_of_food,

小計についても同様だが、こちらはキャベツ(小計)のように「名称プラス（小計）」になるように小細工している。総計・小計になっちゃってるけど、そこはまぁ別にいっかな～と。

  COALESCE(type_of_food, '総計') AS as_type_of_food,
  (
    CASE
      WHEN food_name IS NULL                            THEN '小計'
      WHEN food_name IS NOT NULL AND unit_sales IS NULL THEN food_name || '(小計)' 
      ELSE food_name
    END
  )                             AS as_food_name,

小計・総計は最後に出したい

RDBMSの挙動によると思われるが、小計は確実に最後に出すようにしておきたいのが人情というものである。

まず、小計行はunit_salesがnullになる。その場合、極端に大きい値（ここでは1e9）を指定することで、現実的に小計行が最後にソートされるようにする。

COALESCE(unit_sales, 1e9)     AS as_unit_sales,

これでunit_sales列ではソートすれば小計が最後に出るようになった。次は、food_name列でも小計がそのグループの末尾に、type_of_foodの総計は一番最後、 に出るようにする。

ORDER BY
  CASE
    WHEN type_of_food IS NULL THEN '1'
    ELSE                           '0' || type_of_food
  END ,
  CASE
    WHEN food_name    IS NULL THEN '1'
    ELSE                           '0' || food_name
  END ,
  unit_sales

type_of_foodがnullになるのは「総計」行だけである。その場合は'1'、そうでない場合は'0'プラスtype_of_foodにしている。何が起きるかというと、0果物, 0野菜, 1でソートすることになり、1の総計が最後に来る。food_nameも同様。

参考書籍

このエントリは、上記書籍のp.117 「HACH #33 明細と小計を一度で取得する」を参考にしながら書きました。