PrestoのUNNESTを利用した横縦変換

以前Prestoのクエリで縦横変換(pivot)を行う方法を初回しましたが、今回はその逆で横縦変換(unpivot)を紹介します。

参考: PrestoのMap型を使った縦横変換

参考記事と逆の変換やるわけですね。
そのため元のテーブルがこちら。

横持ちのテーブル
uid c1 c2 c3
101 11 12 13
102 21 22 23

結果として出力したいテーブルがこちらになります。

縦持ちのテーブル (vtable)
uid key value
101 c1 11
101 c2 12
101 c3 13
102 c1 21
102 c2 22
102 c3 23

一番シンプルな書き方は、UNIONを使う方法だと思います。
key の値ごとにvalue を抽出してそれぞの結果を縦に積み上げます。


SELECT
    uid,
    'c1' AS key,
    c1 AS value
FROM
    htable
UNION ALL SELECT
    uid,
    'c2' AS key,
    c2 AS value
FROM
    htable
UNION ALL SELECT
    uid,
    'c3' AS key,
    c3 AS value
FROM
    htable

ただ、この書き方には課題もあって、元の列数が多いとクエリが非常に冗長になります。
そこで、 UNNESTを使った方法を紹介しておきます。

ドキュメントは SELECT の説明のページの途中に UNNESTの章があります。

これを使うと次の様に書けます。


SELECT
    uid,
    t.key,
    t.value
FROM
    htable
CROSS JOIN UNNEST (
  array['c1', 'c2', 'c3'],
  array[c1, c2, c3]
) AS t (key, value)

とてもシンプルに書けました。

PrestoのMap型を使った縦横変換

前回の記事で紹介したPrestoのMap型ですが、これを使うとデータの縦横変換(ピボット)がスマートに行えます。

参考: ピボットテーブル&チャート

上記リンク先のトレジャーデータの記事中の画像のテーブルを例に説明します。

縦持ちのテーブル (vtable)
uid key value
101 c1 11
101 c2 12
101 c3 13
102 c1 21
102 c2 22
102 c3 23

このようなテーブルを次のように変換したいとします。

横持ちのテーブル
uid c1 c2 c3
101 11 12 13
102 21 22 23

この変換は、MAP_AGGを使って、key列とvalue列の対応のMapを一旦作成し、
それぞれのMapから各Keyの値を取り出すことで実現できます。
具体的にクエリにしたのが次です。


SELECT
    uid,
    kv['c1'] AS c1,
    kv['c2'] AS c2,
    kv['c3'] AS c3
FROM (
    SELECT
        uid,
        MAP_AGG(key, value) AS kv
    FROM
        vtable
    GROUP BY
        uid
) AS t

個人的には服問い合わせはあまり好きではなく、PrestoではWITHを使って書きたいので次のようにすることが多いです。


WITH
    t AS (
        SELECT
            uid,
            MAP_AGG(key, value) AS kv
        FROM
            vtable
        GROUP BY
            uid
    )
SELECT
    uid,
    kv['c1'] AS c1,
    kv['c2'] AS c2,
    kv['c3'] AS c3
FROM
    t

Prestoではない、(MAP型のない)通常のSQLでは次のように書かないといけないのですが、
上記のMap型を使ったものの方が随分すっきりかけているように見えます。
(MAP_AGGを知らない人には読めないのが難点ですが)


SELECT
    uid,
    MAX(
        CASE WHEN key = 'c1' THEN
            value
        ELSE
            NULL
        END
    ) AS c1,
    MAX(
        CASE WHEN key = 'c2' THEN
            value
        ELSE
            NULL
        END
    ) AS c2,
    MAX(
        CASE WHEN key = 'c3' THEN
            value
        ELSE
            NULL
        END
    ) AS c3
FROM
    vtable
GROUP BY
    uid

PrestoのMap型について

Prestoには Mapというデータ型があります。
これは Pythonのdictと似たようなデータ型で、 キーと値のペアで構成されるものです。

ドキュメントを見ると、
MAP(ARRAY['foo', 'bar'], ARRAY[1, 2]) というMAPを作る関数の例が紹介されていますが、実行すると次のような結果を得ることができます。


-- クエリ
SELECT
    MAP(ARRAY['foo', 'bar'], ARRAY[1, 2])

-- 以下結果
{"bar":2,"foo":1}

以前紹介した、 TD_PARSE_AGENT という関数がありますが、この結果もMapです。
参考: TreasureDataのTD_PARSE_AGENT関数が便利

Map から、キーをしていて値を取得するときは、上のTD_PARSE_AGENTの記事でやっているように、map に [‘key’]をつけて取得するか、
ELEMENT_AT(map(K, V), key) を使います。
個人的にはPythonなどと近い書き方の [ ] を使う方法の方が好きです。

そのほかの、Mapの使い方ですが、ドキュメントの
6.17. Map Functions and Operators
というページにまとまっているのでこちらがわかりやすいです。

さて、 Tableのある列の値を キーとし、別の列の値を値とするMapを作りたくなる場合があります。
そのようなときは、 MAP_AGG という関数が使えます。
ドキュメントでは別のページにあるので探しにくいのですが、こちらにあります。
6.14. Aggregate Functions
の Map Aggregate Functions。

キーに指定した列に重複があったらどうなるかが気になったのですが、
試してみたところ、バリューの列のどれかの値が何らかの規則で一つだけ選ばれて採用されるようです。
(どのような基準で選ばれているのかは結局わかりませんでした。使うときは注意が必要ですね。)

キーに重複があり、バリューを(配列の形で)全部残したいときは MULTIMAP_AGG が使えます。

MAP_AGG で MAPを作って、 map[key] で値にアクセスする、ということだけ覚えておけば、
特に問題なく使うことができると思います。

ROLLUPやCUBEで発生したNULLと、元々あったNULLを区別する

今回も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で複数種類の集約をまとめて行う

今回も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列も4列もとなっていくとかなり記述量が変わってきます。

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

トレジャーデータで列名の一覧を出力する

注意: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)のみなどの条件をつけることもできます。

INSERT文でWITH句を使う

PrestoのINSERT文で、別のテーブルからSELECTした結果を挿入する書き方があります。
こういうの。


INSERT INTO
    new_table (
        col1,
        col2,
        col3
    )
SELECT
   col1,
   col2,
   col3
FROM
   old_table

このとき、SELECT文がそこそこ複雑になると、以前の記事で紹介したWITH句を使いたくなるのですが、うまく動かず困っていました。
それはどうやら、 INSERT INTO より先に WITH句を書いてしまっていたのが原因のようです。

WITH は INSERT INTO と SELECT の間に 書くのが正解のようです。
例としてはこんな感じ。


INSERT INTO
    new_table (
        col1,
        col2,
        col3
    )
WITH
    tmp_table AS (
        SELECT
            col1,
            col2,
            col3
        FROM
            old_table
    )
SELECT
   col1,
   col2,
   col3
FROM
   tmp_table

PrestoのWITH句

2年ほど前、Prestoのクエリを書き始めた時に知って感動して以来、
ずっと使い続けている文法に WITH句 があります。

イメージとしてはSQL中でVIEWのようなものを作れる構文で、
これを使うとクエリのネストを浅くするなど可読性を大幅に向上させることができます。

例えば次の2つのクエリは同じ結果を返してくれます。
(この例だとまだメリットがわかりにくいですね。そもそも一番外側のSELECT句が純粋に無駄)
伝えたいのはSELECT句のネストがなくなり、フラットな書き方ができるということです。
階層がより深かったり、数百行以上の規模のクエリになるとこの効果は劇的に大きく出ます。
各一時テーブルに名前をつけられることも意図を伝える上で非常に有益です。

通常のネスとした書き方。


SELECT
    col1,
    col2_max
FROM (
    SELECT
        col1,
        MAX(col2) AS col2_max
    FROM
        table1
    GROUP BY
        col1
);

WITH句を使った書き方。


WITH
    table2 AS (
        SELECT
            col1,
            MAX(col2) AS col2_max
        FROM
            table1
        GROUP BY
            col1
    )
SELECT
    col1,
    col2_max
FROM
    table2;

うちのチームでは(その人がSQLを十分書ける人であれば)
トレジャーデータのアカウントを渡して基本的な注意事項を説明したら、
すぐに WITH の使い方を教えています。

その時に使える公式なドキュメントとか無いのかな、と思って探していたのですが、
9.32. SELECT の WITH Clause がそれのようです。
(目次でWITHを検索しても出てこないのでこれまで見つけれてなかった。)
非常にあっさりとしか説明されてなくて、職場の既存クエリ読んでもらった方が良さそうな情報量ではあります。

MySQLで実行中のクエリを確認する

タイトルの通り、MySQLで実行中のクエリ(正確にはプロセス)を確認するコマンドの紹介です。

プログラムがクエリの実行待ち等で止まってしまった時、
クエリが動いてるかどうか不安になる時などに使っています。

MySQLでは SHOW を使う専用の構文が用意されているので簡単です。


SHOW PROCESSLIST

ドキュメント:13.7.5.30 SHOW PROCESSLIST 構文