この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
前回のSQL Serverでウィンドウ関数を使ってみた。に続いてGROUPING演算子なるものに遭遇しました。 どうやら簡単に合計と小計を算出す事ができるらしく、使えると便利かなと思い調べてみました。
構文
グループ分けしたいカラム名を SELECT句 と GROUP BY句 の ROLLUP句 に入れます。
SELECT カラム名１, カラム名２, ..., SUM（算出するカラム名）
FROM テーブル名
GROUP BY ROLLUP(カラム名１, カラム名２, ...);用意したテーブル
ureyuki
何の種類の本を、いつ、男女どちらが、何冊購入したかを示す表です。
id | shurui | ure_date | seibetsu | ure_su
----+--------------+------------+----------+--------
1 | まんが | 2016-02-01 | 男 | 4
2 | まんが | 2016-02-02 | 男 | 1
3 | まんが | 2016-02-02 | 女 | 2
4 | 情報誌 | 2016-02-05 | 男 | 2
5 | 情報誌 | 2016-02-03 | 女 | 2
6 | ファッション | 2016-02-03 | 男 | 1
7 | ファッション | 2016-02-04 | 女 | 1使用例１
出力したい結果
テーブル ureyuki の shurui ごとに ures_su の合計と全体の ure_su の合計を表示しています。 順番は「合計が上段」「それ以外が下段」と分かれていれば良しとします。
shurui | sum_su
--------------+--------
| 13 <- ure_suの合計
まんが | 7
情報誌 | 4
ファッション | 2使用するSQLごとのSQL文
・SQL Server
SELECT shurui, SUM(ure_su)
FROM ureyuki
GROUP BY ROLLUP(shurui)
ORDER BY shurui;・MySQL SQL Server の様に ORDER BY句 が使えないのでサブクエリ化してから ORDER BY句 で並び替えます。
SELECT *
FROM ( SELECT shurui, SUM(ure_su) sum_su
FROM ureyuki
GROUP BY shurui WITH ROLLUP
) SQ
ORDER BY shurui;・PostgreSQL ROLLUP が使えないので UNION ALL で合計と小計を繫げます。
SELECT '' shurui, SUM(shiire_su) sum_su
FROM ureyuki
UNION ALL
SELECT shurui, SUM(shiire_su) sum_su
FROM ureyuki
GROUP BY shurui;使用例２
出力したい結果
今度は seibetsu ごとも含めてみます。
shurui | seibetsu | sum
--------------+----------+-----
| | 13 <- 総合計
まんが | | 7 <- まんがの合計
まんが | 男 | 5
まんが | 女 | 2
情報誌 | | 4 <- 情報誌の合計
情報誌 | 男 | 2
情報誌 | 女 | 2
ファッション | | 2 <- ファッションの合計
ファッション | 男 | 1
ファッション | 女 | 1使用するSQLごとのSQL文
・SQL Server SELECT句、GROUP BY句、ORDER BY句に seibetsu を追加しただけです。
SELECT shurui, seibetsu, SUM(ure_su)
FROM ureyuki
GROUP BY ROLLUP(shurui , seibetsu)
ORDER BY shurui, seibetsu;・MySQL こちらもほぼ同じです。
SELECT *
FROM
(SELECT shurui, seibetsu, SUM(ure_su)su
FROM ureyuki
GROUP BY shurui, seibetsu WITH ROLLUP) SQ
ORDER BY shurui, seibetsu;・PostgreSQL version 9.4までは ROLLUP が使えないのでひたすら UNION ALL で下記の順に繫げます。 １、全体の合計を算出 UNION ALL ２、shurui'まんが'の 合計を算出 UNION ALL ３、shurui'まんが'の seibetsu ごとを算出 UNION ALL ４、shurui'情報誌'の 合計を算出 UNION ALL ５、shurui'情報誌'の seibetsu ごとを算出 UNION ALL ６、shurui'ファッション'の 合計を算出 UNION ALL ７、shurui'ファッション'の seibetsu ごとを算出
SELECT '' shurui, '' seibetsu, SUM(ure_su)
FROM ureyuki
UNION ALL
SELECT 'まんが' shurui, '' seibetsu, SUM(ure_su)
FROM ureyuki
WHERE shurui='まんが'
UNION ALL
SELECT shurui, seibetsu, SUM(ure_su)
FROM ureyuki
WHERE shurui='まんが'
GROUP BY shurui, seibetsu
UNION ALL
SELECT '情報誌' shurui, '' seibetsu, SUM(ure_su)
FROM ureyuki
WHERE shurui='情報誌'
UNION ALL
SELECT shurui, seibetsu, SUM(ure_su)
FROM ureyuki
WHERE shurui='情報誌'
GROUP BY shurui, seibetsu
UNION ALL
SELECT 'ファッション' shurui, '' seibetsu, SUM(ure_su)
FROM ureyuki
WHERE shurui='ファッション'
UNION ALL
SELECT shurui, seibetsu, SUM(ure_su)
FROM ureyuki
WHERE shurui='ファッション'
GROUP BY shurui, seibetsu;...PostgreSQLが嫌になりますね。 実際にこんな形の表を作るかは別ですが。
２０１６年２月２４日 追記
読者の方からPostGreSQLの最新バージョンでGROUPING:ROLLUPがサポートされていますという有益なご指摘を頂きましたので、実際にversion 9.5.1をインストールして動作確認することができましたのでその内容も合わせてアップしておきたいと思います。
ROLLUP
使用例１のSQL文
SELECT shurui, SUM(ure_su)
FROM ureyuki
GROUP BY ROLLUP(shurui)
ORDER BY shurui DESC;実行結果
shurui | sum
--------------+-----
| 13
情報誌 | 4
ファッション | 2
まんが | 7使用例２のSQL文
SELECT shurui, seibetsu, SUM(ure_su)
FROM ureyuki
GROUP BY ROLLUP(shurui , seibetsu)
ORDER BY shurui DESC, seibetsu DESC;実行結果
shurui | seibetsu | sum
--------------+----------+-----
| | 13
情報誌 | | 4
情報誌 | 男 | 2
情報誌 | 女 | 2
ファッション | | 2
ファッション | 男 | 1
ファッション | 女 | 1
まんが | | 7
まんが | 男 | 5
まんが | 女 | 2使用例１、使用例２のどちらも同じような結果が出せましたが、並び順が違ったので ORDER BY句 で shurui DESC としました。 PostgreSQLも進化していますね。
さいごに
最初はSQL Serverとの違いを知りたかっただけでしたが、目的によってはSQL文がだいぶ面倒になる事が分かりました。 利用しているSQLで何ができるのか、しっかり理解していると仕事が捗りそうですね。
3
