PostgreSQL 9.5の新機能CUBEを使ってみた。

はじめに

PostgreSQLが今年に入り9.5になり追加された機能の１つ、CUBEを使ってみました。

動作確認環境
Mac OSX 10.9.5
PostgreSQL 9.5.1

用意したテーブル　ureyuki

 id  |    shurui    |  ure_date  | seibetsu | ure_su 
----+--------------+------------+----------+--------
  1 | まんが        | 2016-02-01 | 男       |      1
  2 | まんが        | 2016-02-02 | 男       |      1
  3 | まんが        | 2016-02-02 | 女       |      1
  4 | 情報誌        | 2016-02-02 | 男       |      1
  5 | ファッション   |            | 女       |      1

使用例１

SQL文

SELECT shurui, SUM(ure_su) AS su
FROM ureyuki
GROUP BY CUBE(shurui);

GROUP BY句に書きます。

実行結果

    shurui    | su 
--------------+----
 まんが        |  3
 ファッション   |  1
 情報誌        |  1
              |  5

”shurui”ごとの”ure_su”の合計と、全体の合計が表示されました。
これだけだとROLLUPを使用した場合と同じ結果ですね。

使用例２

SQL文

SELECT shurui, ure_date, SUM(ure_su) AS su 
FROM ureyuki
GROUP BY CUBE(shurui, ure_date);

SQL文１のSELECT句・GROUP BY句に”ure_date”を追加しました。

実行結果

    shurui    |  ure_date  | su 
--------------+------------+----
 まんが        | 2016-02-01 |  1
 まんが        | 2016-02-02 |  2
 まんが        |            |  3
 ファッション   |            |  1
 ファッション   |            |  1
 情報誌        | 2016-02-02 |  1
 情報誌        |            |  1
              |            |  5
              | 2016-02-01 |  1
              | 2016-02-02 |  3
              |            |  1

行数かなり増えました。
”shurui”の各項目の合計と、”ure_date”の日付ごとの合計が追加されています。
１１行目〜１３行目は”まんが””ファッション””情報誌”を合わせて”ure_date”の日付ごとに分かれています。
総合計は１０行目です。

だんだん空欄が増えてきてぱっと見でどれがどれか分からなくなってきました。
空欄（この場合はNULL）は実は意味が違います。

GROUPING

NULLごとの違いが知りたい時に使います。

SQL文

SELECT GROUPING(shurui), GROUPING(ure_date), SUM(ure_su) AS su 
FROM ureyuki
GROUP BY CUBE(shurui, ure_date);

SELECT句でGROUPING()を付けただけです。

実行結果

 grouping  | grouping | su 
----------+----------+----
        0 |        0 |  1
        0 |        0 |  2
        0 |        1 |  3
        0 |        0 |  1
        0 |        1 |  1
        0 |        0 |  1
        0 |        1 |  1
        1 |        1 |  5
        1 |        0 |  1
        1 |        0 |  3
        1 |        0 |  1

比較対象：使用例２の実行結果

    shurui    |  ure_date  | su 
--------------+------------+----
 まんが        | 2016-02-01 |  1
 まんが        | 2016-02-02 |  2
 まんが        |            |  3
 ファッション   |            |  1
 ファッション   |            |  1
 情報誌        | 2016-02-02 |  1
 情報誌        |            |  1
              |            |  5
              | 2016-02-01 |  1
              | 2016-02-02 |  3
              |            |  1

SELECT句で GROUPING に設定した shurui と ure_date の表示が使用例と違いますね。
"１＝グループ化された結果（表示する値を決めていないのでNULLになる）"、"０＝ただのNULL"という意味になっています。
これで”ure_date”の６行目と７行目の違いが分かりました。 元テーブルの”ファッション”の”ure_date”はNULLだったので、６行目と１３行目は”０”となっているのです。

もっと分かりやすく表示させるためにCASE文を使ってみます。

SELECT CASE WHEN GROUPING(shurui)=1 THEN '合計' 
            ELSE shurui 
            END AS shurui
       , 
       CASE WHEN GROUPING(ure_date)=1 THEN '計' 
            ELSE CAST(ure_date AS VARCHAR(20)) 
            END AS ure_date
       , 
       SUM(ure_su) AS su 
FROM ureyuki
GROUP BY CUBE(shurui, ure_date);

    shurui    |  ure_date  | su 
--------------+------------+----
 まんが        | 2016-02-01 |  1
 まんが        | 2016-02-02 |  2
 まんが        | 計         |  3
 ファッション   |            |  1
 ファッション   | 計         |  1
 情報誌        | 2016-02-02 |  1
 情報誌        | 計         |  1
 合計          | 計         |  5
 合計          | 2016-02-01 |  1
 合計          | 2016-02-02 |  3
 合計          |            |  1

かなり分かりやすくなったのではないでしょうか。

GROUPING SETS

もし集計結果だけ欲しい場合はこれを使います。

SQL文

SELECT shurui, ure_date, SUM(ure_su) AS su 
FROM ureyuki
GROUP BY GROUPING SETS(shurui, ure_date);

    shurui    |  ure_date  | su 
--------------+------------+----
 まんが        |            |  3
 ファッション   |            |  1
 情報誌        |            |  1
              | 2016-02-01 |  1
              | 2016-02-02 |  3
              |            |  1

さいごに

CUBEにはもっと対象カラムを記載できますが、見辛くなるだけな気がするのであまり使い勝手が無い様な気もします。
上手い使用例を見てみたいですね。