やりたいこと
下図のイメージのように、左側のデータを基に、右側の集計表を作りたい、とする。ポイントは、テーブル全体の販売個数だけでなく、種類ごとの小計、種類と名前ごとの小計も出力する。さらに加えて、明細行も一緒に出す、というのをやりたい。
環境
- DB
- ツール
サンプル用のテーブルとデータ準備
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
も同様。
参考書籍
SQL Hacks ―データベースを自由自在に操るテクニック
- 作者: Andrew Cumming,Gordon Russell,西沢直木
- 出版社/メーカー: オライリー・ジャパン
- 発売日: 2007/07/28
- メディア: 単行本(ソフトカバー)
- 購入: 3人 クリック: 90回
- この商品を含むブログ (34件) を見る
このエントリは、上記書籍のp.117 「HACH #33 明細と小計を一度で取得する」を参考にしながら書きました。