月: 2019年10月

今回もPrestoの話題です。
最近の更新で、
ROLLUPやCUBE,GROUPING SETなどを使って、総計を出したり、複数の条件での集計をまとめて行う方法を紹介しましたが、
これらの操作を行うと、多くのNULL値が発生します。
ROLLUPであれば、値が入ってるのが個別の集計で、NULLになっている行の値が総計と判定できるのですが、
ここで元々その列にNULLがあると、見分けがつかず、面倒なことになります。

単なるイメージですが、こんな感じの結果が出ます。
|gender|cnt|
|男性|100|
|女性|200|
|NULL|50| # これは元々NULL
|NULL|350| # ROLLUPで発生したNULL

このような、元々あったNULLと集計によって発生したNULLを見分けるのに、
GROUPING という専用の関数が用意されています。
ドキュメントはこちらのページのGROUPING Operationを参照。

SELECT句の中で、 grouping(col1, …, colN) のように使い、整数値を返します。
結果の数値が少し癖があるのですが、引数の一番右側の列(つまりcolN)が1,
そこから順番に、colN-1が2, colN-2が4($=2^2$)と、順番にビットの桁が割り当てられ、
その列の値が、ROLLUPやCUBEによって発生したNULLになっているビットの和を返します。
(わかりにくいですね。)

会社のDWHに打った実クエリを貼るわけにいかないのでドキュメントで紹介されている例をそのまま転載します。

SELECT
    origin_state,
    origin_zip,
    destination_state,
    SUM(package_weight),
    GROUPING(
        origin_state,
        origin_zip,
        destination_state
    )
FROM
    shipping
GROUP BY
    GROUPING SETS (
        (origin_state),
        (origin_state, origin_zip),
        (destination_state)
    );

-- 結果
origin_state | origin_zip | destination_state | _col3 | _col4
--------------+------------+-------------------+-------+-------
California   | NULL       | NULL              |  1397 |     3
New Jersey   | NULL       | NULL              |   225 |     3
New York     | NULL       | NULL              |     3 |     3
California   |      94131 | NULL              |    60 |     1
New Jersey   |       7081 | NULL              |   225 |     1
California   |      90210 | NULL              |  1337 |     1
New York     |      10002 | NULL              |     3 |     1
NULL         | NULL       | New Jersey        |    58 |     6
NULL         | NULL       | Connecticut       |  1562 |     6
NULL         | NULL       | Colorado          |     5 |     6
(10 rows)

grouping(
origin_state,
origin_zip,
destination_state
)
と指定されている各行が順番に、4,2,1のビットに対応していて、
例えば、結果の3行目、New Yorkの行であれば、
origin_zipとdestination_stateの行がGROUPING SETSによって発生したNULLになっているので、
2+1で3となっています。

この性質を使って、例えば NULL を’合計’とか’小計’とかに書き換えて返すクエリを構築することができます。

今回もPrestoのクエリのテクニックです。
前回の記事で、ROLLUPを使って集約(GROUP BY)した値と総計を同時に計算する方法を紹介しましたが、
もっと柔軟に、いろいろな組み合わせで集約をしたい場面があります。
面倒なのでいつも個別のクエリで出力してUNIONしたりpandasで結合したりしていますが、
Prestoにはそのような時に使える構文として、GROUPING SETSというのが用意されています。
ドキュメントのクエリをそのまま紹介させていただきますが、
次のように書きます。

SELECT
    origin_state,
    origin_zip,
    destination_state,
    SUM(package_weight)
FROM
    shipping
GROUP BY
    GROUPING SETS (
        (origin_state),
        (origin_state, origin_zip),
        (destination_state)
    );

こうすると、origin_state　で集約したpackage_weightの合計 (この時origin_zipと、destination_stateはNULL)、
origin_stateと origin_zip　で集約したpackage_weightの合計 (この時destination_stateはNULL)、
destination_state　で集約したpackage_weightの合計 (この時origin_stateと、origin_zipはNULL)、
がまとめて出力されます。

 origin_state | origin_zip | destination_state | _col0
--------------+------------+-------------------+-------
 New Jersey   | NULL       | NULL              |   225
 California   | NULL       | NULL              |  1397
 New York     | NULL       | NULL              |     3
 California   |      90210 | NULL              |  1337
 California   |      94131 | NULL              |    60
 New Jersey   |       7081 | NULL              |   225
 New York     |      10002 | NULL              |     3
 NULL         | NULL       | Colorado          |     5
 NULL         | NULL       | New Jersey        |    58
 NULL         | NULL       | Connecticut       |  1562
(10 rows)

GROUPING SETS の中に () を入れてやれば全部の合計も出せます。
個別にクエリを書いてUNION ALLでつなげるのに比べると、記述量を劇的に減らせますね。

さらに、いくつかの列について、全ての組み合わせで、GROUPING SETS を作りたい場合、CUBE という演算子が使えます。

SELECT
    origin_state,
    destination_state,
    SUM(package_weight)
FROM
    shipping
GROUP BY
    CUBE(
        origin_state,
        destination_state
    );

と、次のクエリは同じ意味です。

SELECT
    origin_state,
    destination_state,
    SUM(package_weight)
FROM
    shipping
GROUP BY
    GROUPING SETS (
        (origin_state, destination_state),
        (origin_state),
        (destination_state),
        ()
    );

CUBEの中に入れてる列が2つだとそうでもないですが、これが3列も４列もとなっていくとかなり記述量が変わってきます。

(注意)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の二通りなんてデータもなかなか無いでしょうが、ただの例なのでご了承ください。)
慣れると便利なのでためしてみてください。

注意:Prestoの方でクエリを書いていることを前提とします。

トレジャーデータを使っていて、各DBのそれぞれのテーブル毎の列名の一覧を取得したくなったのでその方法のメモです。

対象のテーブルが少なければ、
DESCRIBE table_name
を順番に実行すれば十分ですが、対象テーブルが多くなるとこれでは大変です。

この場合、Presotのメタデータにアクセスすると手軽に列名の一覧を得ることができます。

FAQの次の質問が参考になります。
23. How do I access TD table metadata using Presto?

クエリをそのまま引用します。

# List TD Databases
SELECT * from information_schema.schemata

# List TD Tables
SELECT * from information_schema.tables

# List all column metadata
SELECT * from information_schema.columns

このうち、3番目の
SELECT * from information_schema.columns
を使うと、DB、テーブル、列を含む情報を取得できます。
不要な情報もあるので、自分は次の形で使うことが多いです。

SELECT
    table_schema,
    table_name,
    column_name
FROM
    information_schema.columns

通常のSELECTと同じように、WHERE句で特定のDB(schema)のみなどの条件をつけることもできます。