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

#初心者向け

#データベース

takahara

2016.02.22

この記事は公開されてから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も進化していますね。