ROLLUPを使った合計の計算

(注意)prestoを前提とします。 MySQLにもROLLUPはありますが少し書き方が違うようです。

最近よく使うようになった、ROLLUPという文法の紹介です。
(これまではTableauかPythonで計算するか、どうしてもSQLで関係つさせたい時はUNIONして対応していた。)

SQLでGROUP BYを使って何か集計した時、それらの合計(や、全体の平均、カウントなど)も出したいという場面はよくあります。
パソコンとスマホとか、男性と女性とか、で集計して、同時に全体の数値も見たいという場合ですね。

そのような時に ROLLUP を使えます。
ドキュメントはこのページの中。
実データを出せないのでイメージになってしまうのですが、
例えば、userテーブルのレコード数をgender列の値別に数える場合、通常は、

SELECT
    gender,
    COUNT(*) AS cnt
FROM
    user
GROUP BY
    gender

とやって、

|gender|cnt|
|男性|100|
|女性|200|

のような結果を得ると思います。
ここに、合計も一緒に出したい場合、

SELECT
    gender,
    COUNT(*) AS cnt
FROM
    user
GROUP BY
    gender
UNION ALL SELECT
    NULL AS gender
    COUNT(*) AS cnt
FROM
    user

のように書くと一応算出できるのですが、ちょっと要領の悪い書き方になります。

これが、ROLLUPを使って、

SELECT
    gender,
    COUNT(*) AS cnt
FROM
    user
GROUP BY
    ROLLUP(gender)

とすると、
|gender|cnt|
|男性|100|
|女性|200|
|NULL|300|
のような結果を得ることができます。

さらに ROLLUP はカンマ区切りで複数列指定することもでき、
そうすると段階的に小計を出してくれます。(これは何か手頃のデータで試すのが一番良いです。)

SELECT
    gender,
    age,
    COUNT(*) AS cnt
FROM
    user
GROUP BY
    ROLLUP(gender, age)

とすると、出力は次のようになります。
|gender|age|cnt|
|男性|20|30|
|男性|30|70|
|男性|NULL|100|
|女性|20|80|
|女性|30|120|
|女性|NULL|200|
|NULL|NULL|300|

(年齢が20と30の二通りなんてデータもなかなか無いでしょうが、ただの例なのでご了承ください。)
慣れると便利なのでためしてみてください。