はじめに

いままで使ったことが無かったけど便利そうな GROUPING というのを見つけたので紹介します。

GROUP BY CUBE

こういう SQL があったとして

WITH t AS (
  SELECT *
    FROM (VALUES('太郎', 'リンゴ', 10),
                ('太郎', 'バナナ', 2),
                ('太郎', 'バナナ', 8),
                ('二郎', 'バナナ', 9),
                ('二郎', 'バナナ', 1),
                ('三郎', 'パイナップル', 1),
                ('三郎', 'リンゴ', 4),
                ('三郎', 'リンゴ', 5)) AS t("name", "item", "amount")
)

  SELECT "name"
       , "item"
       , SUM("amount") AS amount_sum
       , COUNT(DISTINCT "name") AS name_count
       , COUNT(DISTINCT "item") AS item_count
    FROM t
GROUP BY CUBE(1, 2)
;

以下のような結果になります

 name |     item     | amount_sum | name_count | item_count
------+--------------+------------+------------+------------
 三郎 | パイナップル |          1 |          1 |          1
 三郎 | リンゴ       |          9 |          1 |          1
 三郎 |              |         10 |          1 |          2
 二郎 | バナナ       |         10 |          1 |          1
 二郎 |              |         10 |          1 |          1
 太郎 | バナナ       |         10 |          1 |          1
 太郎 | リンゴ       |         10 |          1 |          1
 太郎 |              |         20 |          1 |          2
      |              |         40 |          3 |          3
      | バナナ       |         20 |          2 |          1
      | パイナップル |          1 |          1 |          1
      | リンゴ       |         19 |          2 |          1
(12 rows)

問題

GROUP BY CUBE を使うと、どの行がどのレベルの小計なのかとか総計はどれなのかとかわかりにくくなってしまいます。

GROUPING

そこで役に立ちそうなのが GROUPING です。

グループ化演算はグループ化セット（7.2.4参照）と一緒に使われ、結果の行を区別するものです。 GROUPING演算の引数は実際には評価されませんが、関連する問い合わせのGROUP BY句にある式と正確に一致する必要があります。最も右側の引数が最下位ビットになるようにビットが割り当てられます。各ビットは、対応する式が結果の行を生成するグループ化セットのグループ化条件に含まれていれば0、そうでなければ1です。例えば以下のようになります。

とりあえず使ってみます。

WITH t AS (
  SELECT *
    FROM (VALUES('太郎', 'リンゴ', 10),
                ('太郎', 'バナナ', 2),
                ('太郎', 'バナナ', 8),
                ('二郎', 'バナナ', 9),
                ('二郎', 'バナナ', 1),
                ('三郎', 'パイナップル', 1),
                ('三郎', 'リンゴ', 4),
                ('三郎', 'リンゴ', 5)) AS t("name", "item", "amount")
)

  SELECT "name"
       , "item"
       , SUM("amount") AS amount_sum
       , COUNT(DISTINCT "name") AS name_count
       , COUNT(DISTINCT "item") AS item_count
       , GROUPING("name", "item")
    FROM t
GROUP BY CUBE(1, 2)
ORDER BY 6
;

結果は↓

 name |     item     | amount_sum | name_count | item_count | grouping
------+--------------+------------+------------+------------+----------
 三郎 | パイナップル |          1 |          1 |          1 |        0
 三郎 | リンゴ       |          9 |          1 |          1 |        0
 二郎 | バナナ       |         10 |          1 |          1 |        0
 太郎 | バナナ       |         10 |          1 |          1 |        0
 太郎 | リンゴ       |         10 |          1 |          1 |        0
 二郎 |              |         10 |          1 |          1 |        1
 太郎 |              |         20 |          1 |          2 |        1
 三郎 |              |         10 |          1 |          2 |        1
      | リンゴ       |         19 |          2 |          1 |        2
      | パイナップル |          1 |          1 |          1 |        2
      | バナナ       |         20 |          2 |          1 |        2
      |              |         40 |          3 |          3 |        3
(12 rows)

grouping に 0 か 1 か 2 か 3 が現れています。
これはそれぞれ B'00'、 B'01'、B'10'、 B'11' に対応しています。
2 bit あるのは引数が 2 個だからで、それぞれの bit に name と item が対応していて、対応する bit が立っている列の計である事を表しています。全ての bit が立っている値（今回の場合は 3）は総計になります。

分かりやすく分類してみる

WITH t AS (
  SELECT *
    FROM (VALUES('太郎', 'リンゴ', 10),
                ('太郎', 'バナナ', 2),
                ('太郎', 'バナナ', 8),
                ('二郎', 'バナナ', 9),
                ('二郎', 'バナナ', 1),
                ('三郎', 'パイナップル', 1),
                ('三郎', 'リンゴ', 4),
                ('三郎', 'リンゴ', 5)) AS t("name", "item", "amount")
)

  SELECT "name"
       , "item"
       , SUM("amount") AS amount_sum
       , COUNT(DISTINCT "name") AS name_count
       , COUNT(DISTINCT "item") AS item_count
       , CASE GROUPING("name", "item")
         WHEN 0 THEN '明細'
         WHEN 1 THEN 'name 小計'
         WHEN 2 THEN 'item 小計'
         WHEN 3 THEN '総計'
         ELSE 'あり得ない'
         END
    FROM t
GROUP BY CUBE(1, 2)
ORDER BY GROUPING("name", "item")
;

SELECT の GROUPING は CASE で振り分けて、ソートは GROUPING そのものにしてみます。

 name |     item     | amount_sum | name_count | item_count |   case
------+--------------+------------+------------+------------+-----------
 三郎 | パイナップル |          1 |          1 |          1 | 明細
 三郎 | リンゴ       |          9 |          1 |          1 | 明細
 二郎 | バナナ       |         10 |          1 |          1 | 明細
 太郎 | バナナ       |         10 |          1 |          1 | 明細
 太郎 | リンゴ       |         10 |          1 |          1 | 明細
 二郎 |              |         10 |          1 |          1 | name 小計
 太郎 |              |         20 |          1 |          2 | name 小計
 三郎 |              |         10 |          1 |          2 | name 小計
      | リンゴ       |         19 |          2 |          1 | item 小計
      | パイナップル |          1 |          1 |          1 | item 小計
      | バナナ       |         20 |          2 |          1 | item 小計
      |              |         40 |          3 |          3 | 総計
(12 rows)

なんとなく分かりやすくなりました。便利。

まとめ

どこの列が NULL だったら、みたいな条件で小計かどうかを判定するより、GROUPING を使った方がスッキリと分類出来そうで便利だと思いました。
最近は集計は BigQuery ばかり使ってるんですが、PostgreSQL にある CUBE が使えなくて困ってます。早く BigQuery にも CUBE が来て欲しいです（ついでに GROUPING も）。