SQL ServerでGROUPING演算子を使ってPostgreSQL、MySQLと比較してみた。

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

前回のSQL Serverでウィンドウ関数を使ってみた。に続いてGROUPING演算子なるものに遭遇しました。
どうやら簡単に合計と小計を算出す事ができるらしく、使えると便利かなと思い調べてみました。

構文

グループ分けしたいカラム名を SELECT句 と GROUP BY句 の ROLLUP句 に入れます。

SELECT カラム名1, カラム名2, ..., SUM(算出するカラム名)
FROM テーブル名
GROUP BY ROLLUP(カラム名1, カラム名2, ...);

用意したテーブル

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

使用例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;

使用例2

出力したい結果

今度は 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 で下記の順に繫げます。
 1、全体の合計を算出
   UNION ALL
 2、shurui'まんが'の 合計を算出
   UNION ALL
 3、shurui'まんが'の seibetsu ごとを算出
   UNION ALL
 4、shurui'情報誌'の 合計を算出
   UNION ALL
 5、shurui'情報誌'の seibetsu ごとを算出
   UNION ALL
 6、shurui'ファッション'の 合計を算出
   UNION ALL
 7、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が嫌になりますね。
実際にこんな形の表を作るかは別ですが。

2016年2月24日 追記

読者の方からPostGreSQLの最新バージョンでGROUPING:ROLLUPがサポートされていますという有益なご指摘を頂きましたので、実際にversion 9.5.1をインストールして動作確認することができましたのでその内容も合わせてアップしておきたいと思います。

ROLLUP

使用例1のSQL文

SELECT shurui, SUM(ure_su)
FROM ureyuki
GROUP BY ROLLUP(shurui)
ORDER BY shurui DESC;

実行結果

    shurui    | sum 
--------------+-----
              |  13
 情報誌        |   4
 ファッション   |   2
 まんが        |   7

使用例2の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

使用例1、使用例2のどちらも同じような結果が出せましたが、並び順が違ったので ORDER BY句 で shurui DESC としました。
PostgreSQLも進化していますね。

さいごに

最初はSQL Serverとの違いを知りたかっただけでしたが、目的によってはSQL文がだいぶ面倒になる事が分かりました。
利用しているSQLで何ができるのか、しっかり理解していると仕事が捗りそうですね。