SQL | 分析ノート

2020-01-132020-01-13

縦持ちのテーブル (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

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] で値にアクセスする、ということだけ覚えておけば、
特に問題なく使うことができると思います。

2019-10-282019-10-28

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 を’合計’とか’小計’とかに書き換えて返すクエリを構築することができます。

2019-10-252019-10-25

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

2019-10-242019-10-24

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

2019-10-172019-10-17

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

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

2019-07-262019-07-26

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

2019-07-192019-07-19

PrestoのWITH句

２年ほど前、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を検索しても出てこないのでこれまで見つけれてなかった。)
非常にあっさりとしか説明されてなくて、職場の既存クエリ読んでもらった方が良さそうな情報量ではあります。

2019-07-102019-07-10

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

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

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

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


SHOW PROCESSLIST

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

2019-07-052019-07-05

MySQLのテーブルをDB内でコピーする2つの方法

先日、諸事情あってMySQLのテーブルをコピーしておきたいことがありました。

SELECT以外の操作は滅多にやらないので、少し調べて出てきたのがこちらの方法です。
1つ目のクエリで元のテーブルと同じ構造のテーブルを作り、
2つ目のクエリでデータを移しています。


CREATE TABLE new_table_name LIKE table_name;
INSERT INTO new_table_name SELECT * FROM table_name;

今回必要だった要件は、これで満たせたのですが、一個のクエリでコピーする方法があったはず、
というおぼろげな記憶があったのでもう少し探して見つけたのがこちら。


CREATE TABLE new_table_name SELECT * FROM table_name;

これなら1行で中のテーブルをコピーできます。

なんで一行で済む2番目の方法よりも、クエリを二つ使う1番目の方法の方がよく使われているのか気になったので、後日実験してみました。

結果わかったのは、データはどちらの方法でも同じようにコピーされるのですが、
2番目の方法では、テーブルの設定の一部がコピーされないことです。

試したのがこちら。
まず、元のテーブル。


mysql> desc sample1;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255) | YES  |     | NULL              |                             |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+

1番目の方法でコピーしたテーブル。こちらは元のテーブルと同じです。


mysql> desc sample2;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255) | YES  |     | NULL              |                             |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+

2番目の方法でコピーしたテーブル。
auto_incrementやDefaultの値の一部、主キー属性などがコピーされていません。


mysql> desc sample2;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   |     | 0                 |                             |
| name       | varchar(255) | YES  |     | NULL              |                             |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+

値はどちらの方法でもコピーされるのですが、
見落としがちな部分に差異があるので注意が必要ですね。

カテゴリー: SQL

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