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

ERAlchemyを使ってER図を描く

以前からER図をいい感じに出力できるツールを探していたのですが、ERAlchemyというのを知ったので使ってみました。
リポジトリ:Alexis-benoist/eralchemy
PyPI: ERAlchemy

Pythonで実装されたコマンドラインツールということで、Homebrewか、pipでインストールできます。


# どちらか行う。
$ brew install eralchemy
$ pip install eralchemy

自分はbrewのほうでインストールしました。
内部でgraphvizを使っているそうなので、もしかしたら事前にインストールしておく必要があるかもしれません。

既存のデータベースから作図する機能もあるようなのですが、自分はerファイルというテキストファイルから作成する方法で使っています。
erファイルのルールですが、ERAlchemyのページには(今のところ)簡単な例以上の説明は無いようです。

元々erdというツールの影響を受けて作られたそうで、erファイルの書式等はerdのドキュメントを読んで試すのが良さそうです。

ERAlchemy was inspired by erd, though it is able to render the ER diagram directly from the database and not just only from the ER markup language.

リレーションに使える記号の一覧を、ERAlchemyのソースコード中から見つけ出したりしてたのですが、無駄な手間でした。
僕も素直にerdのドキュメントを読めばよかったです。

ちなみにこちらから分かる通り、[*?+1]の4種類と未指定が使えます。
https://github.com/Alexis-benoist/eralchemy/blob/master/eralchemy/models.py


class Relation(Drawable):
    """ Represents a Relation in the intermediaty syntax """
    RE = re.compile(
        '(?P[^\s]+)\s*(?P[*?+1])--(?P[*?+1])\s*(?P[^\s]+)')  # noqa: E501
    cardinalities = {
        '*': '0..N',
        '?': '{0,1}',
        '+': '1..N',
        '1': '1',
        '': None
    }

さて、使い方は簡単で、次のコマンドで動きます。

eralchemy -i ‘入力ファイル(erファイル)’ -o ‘出力ファイル(pdfやpngなど)’
サンプルファイルを用意していただけてるので、curlでもってくるところからやってみましょう。


$ curl 'https://raw.githubusercontent.com/Alexis-benoist/eralchemy/master/example/newsmeme.er' > markdown_file.er
$ eralchemy -i markdown_file.er -o erd_from_markdown_file.png

これで、画像ファイルが生成されました。(ブログに貼るためにpngファイルにしましたが、通常はpdfの方が使いやすいかと思います。)

erファイルの中身も少しみておきましょう。
まず、テーブル定義は次のように指定します。


[tags]
    *id {label:"INTEGER"}
    slug {label:"VARCHAR(80)"}
    name {label:"VARCHAR(80)"}

{label:hogehoge}は省略できます。

そしてリレーションは次のように指定します。


users *--? posts
posts *--? comments
users *--? comments
comments *--? comments
tags *--? post_tags
posts *--? post_tags

この例は全て 0以上 対 0or1 のリレーションですが、+で1以上、1で厳密に一つを指定できます。

サンプルの中では使われていませんが、
erdの方のドキュメントを見る限りでは、背景色やフォントの指定などもできそうなので、色々試してみたいと思います。

scipyで階層的クラスタリングの樹形図を書く時に上位のクラスタのみ表示する

以前書いた、 scipyで階層的クラスタリング の記事の続きです。

階層的クラスタリングを行って結果を樹形図(デンドログラム)で表示すると、元のデータが多い場合は非常にみづらいものになります。
このような時は、樹形図の表示を途中で打ち切って必要なクラスタ分だけ表示するとクラスタ間の関係が掴みやすくなります。
scipyのdendrogram関数では、 truncate_mode というオプションが用意されており、これと、 値pを適切に指定することで実現できます。
ドキュメント: scipy.cluster.hierarchy.dendrogram

truncate_mode はNone, lastp, levelの3つの値を取ります。
Noneがデフォルト、lastpがデンドログラムの上から数えてp個のノードを残す、
levelは逆に下から数えて、各ノードがp回マージされるように動きます。

それぞれ動かしてみましょう。
truncate_mode=”lastp”, p=16

truncate_mode=”level”, p=3
の場合に、表示されるノードがどちらも16個になるので、動きの違いも見ておきます。


from sklearn.datasets import load_iris
import matplotlib.pyplot as plt
from scipy.cluster.hierarchy import linkage
from scipy.cluster.hierarchy import dendrogram

# データ取得。
X = load_iris().data

# ユークリッド距離とウォード法を使用してクラスタリング
z = linkage(X, metric='euclidean', method='ward')

# 結果を可視化
fig = plt.figure(figsize=(8, 15), facecolor="w")
ax = fig.add_subplot(3, 1, 1, title="樹形図: 全体")
dendrogram(z)
ax = fig.add_subplot(3, 1, 2, title="樹形図: lastp 16")
dendrogram(z, truncate_mode="lastp", p=16)
ax = fig.add_subplot(3, 1, 3, title="樹形図: level 3")
dendrogram(z, truncate_mode="level", p=3)
plt.show()

結果がこちら。

truncate_mode=”lastp” は、樹形図全体の上部の部分をそのまま切り出したものになっていて、
truncate_mode=”level” の方は、各枝に至るまでの分岐回数が一定になっているのがわかります。
また、どちらも図がすっきりしてみやすくなりました。

Optunaで学習曲線を可視化する

Optunaの機能や挙動についてもっとしっかり理解したいので、うまい可視化の方法を考えていたのですが、
ドキュメント中に、Visualizationのセクションを見つけたので、
まずはこれを試してみることにしました。

現段階(version 0.16.0)では、定義されている関数は
optuna.visualization.plot_intermediate_values(study)
だけのようなのでこれを試します。
(最初に試した時、 Plotly が入ってないという警告が出たのでpipインストールしておきます。)

早速使ってみると、次のようにトライアルごとの学習の進捗が可視化できました。

Optunaで枝刈りも使ってKerasのパラメータチューニングの記事で紹介したコードを動かした後に、jupyterで次のコードを実行します。


optuna.visualization.plot_intermediate_values(study)

jupyterで動かすと、マウスカーソルを当てた時にそれぞれの線が何回めのトライアルなどかなどの情報がポップアップされるので、
ぜひ試していただきたいです。
pngエクスポート機能もあり、それで出力した画像がこちら。

見込みがない試行がさっさと打ち切られているのがわかります。
(正解率が低いのに最後まで走ってるのは序盤の試行です。)

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

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

Optunaの枝刈りの基準を変更する

前回の記事: Optunaで枝刈りも使ってKerasのパラメータチューニング
の続きです。

Kerasのパラメーターチューニングの中で、枝刈りを使ってみたのですが
100回の試行の中で最後まで走ったのが1/5の19回だけというのは少し不安です。
もしかしたらこのくらいで適切なのかもしれませんが、まだ慣れないライブラリなのでよく感覚がつかめていません。

ということで、もう少し枝刈りの基準を緩める方法を探しました。
そのためには、別のPrunerを使うと良いようです。
前回の記事で使ったのは、MedianPruner という、それまでの試行の中央値より成績が悪ければ打ち切るPrunerでした。
ただ、探索対象のパラメーターの中に学習率等も入っていましたし、序盤のepockで成績が悪くても最終的に高い成果をあげるものもある気がします。

そこで、PercentilePrunerを試してみます。
ドキュメントはこちら。 

percentile – Percentile which must be between 0 and 100 inclusive (e.g., When given 25.0, top of 25th percentile trials are kept).

とある通りなので、上位60パーセンタイルくらいを許容するようにすれば、中央値(50パーセンタイル)を基準にするより緩くなりそうです。
(ちなみに75パーセンタイルで試したらなかなか枝刈りされなくなりました。)

前回のコードのprunerを指定してる部分を次のように書き換えて試してみます。


study = optuna.create_study(
                direction="maximize",
                # pruner=optuna.pruners.MedianPruner()
                pruner=optuna.pruners.PercentilePruner(60.0)
            )

最終的な結果はこちら。
試行のうち、39回は最後まで走るようになり、
正解率も前回の 0.92734375より少し良くなりました。
(この後も何度か試しましたが、必ずしよくなるわけではないです。)


"""
Study statistics: 
  Number of finished trials:  100
  Number of pruned trials:  61
  Number of complete trials:  39
Best trial:
  Value:  0.928125
  Params: 
    n_layers: 1
    n_units_l0: 126.84330007270901
    dropout_l0: 0.4023051335982798
    lr: 0.007029694763599239
"""

今回は、Prunerを変更することで枝刈りの具合を調整できるということが確認できました。
また、結果的に枝刈りが少ない方が最終的な成績は少しだけ良かったのですが、その差は軽微であることもわかりました。
今回かなり小さなデータで試しているのですが、
本当に学習に長時間かかるモデルを試す場合は、もっと高頻度に枝刈りする方がよい場合もありそうです。

Optunaで枝刈りも使ってKerasのパラメータチューニング

少し間が空きましたが、再びOptunaの記事です。
今度はKerasのモデルのパラメーターをチューニングしてみます。
その際、枝刈りも使いました。

Optunaに、Keras用のコールバックが用意されていて、
これを使うと、エポック毎に判定が走り、学習が十分進んでいないと学習を打ち切ってくれるので、
効率的に探索ができます。
classoptuna.integration.KerasPruningCallback

githubのサンプルコードを(少しだけ修正しましたが)ほぼ写経しながら、
コードを書いてみました。


import keras
from keras.datasets import mnist
from keras.utils import to_categorical
from keras.layers import Dense
from keras.layers import Dropout
from keras.models import Sequential
import optuna
from optuna.integration import KerasPruningCallback

# 訓練データとテストデータの件数を指定
N_TRAIN_EXAMPLES = 3840
N_TEST_EXAMPLES = 1280
BATCHSIZE = 128
CLASSES = 10
EPOCHS = 20

# MNISTのデータを準備
(x_train, y_train), (x_test, y_test) = mnist.load_data()
# 特徴量を0~1に正規化する
x_train = x_train.reshape(60000, 784)[:N_TRAIN_EXAMPLES]/255
x_test = x_test.reshape(10000, 784)[:N_TEST_EXAMPLES]/255

# ラベルを1 hot 表現に変換
y_train = to_categorical(y_train[:N_TRAIN_EXAMPLES], CLASSES)
y_test = to_categorical(y_test[:N_TEST_EXAMPLES], CLASSES)


def create_model(trial):
    """MLPモデルの構築
    """

    # 層の数を選択する
    n_layers = trial.suggest_int("n_layers", 1, 3)
    model = Sequential()
    for i in range(n_layers):
        num_hidden = int(
                            trial.suggest_loguniform(
                                    f"n_units_l{i}",
                                    4,
                                    128
                                )
                            )
        model.add(Dense(num_hidden, activation="relu"))
        dropout = trial.suggest_uniform(f"dropout_l{i}", 0.2, 0.5)
        model.add(Dropout(rate=dropout))
    model.add(Dense(CLASSES, activation="softmax"))

    # 学習率も最適化する。
    lr = trial.suggest_loguniform("lr", 1e-5, 1e-1)
    model.compile(loss="categorical_crossentropy",
                  optimizer=keras.optimizers.RMSprop(lr=lr),
                  metrics=["acc"])

    return model


def objective(trial):
    # 前のセッションをクリアする
    keras.backend.clear_session()
    # モデル作成
    model = create_model(trial)

    # モデルの学習
    # KerasPruningCallbackでepoch毎に枝刈りの判定。
    model.fit(
        x_train,
        y_train,
        batch_size=BATCHSIZE,
        callbacks=[KerasPruningCallback(trial, "val_acc")],
        epochs=EPOCHS,
        validation_data=(x_test, y_test),
        verbose=2
    )

    # モデルの評価
    score = model.evaluate(x_test, y_test, verbose=0)
    return score[1]


study = optuna.create_study(
                direction="maximize",
                pruner=optuna.pruners.MedianPruner()
            )
study.optimize(objective, n_trials=100)
pruned_trials = [t for t in study.trials if t.state == optuna.structs.TrialState.PRUNED]
complete_trials = [t for t in study.trials if t.state == optuna.structs.TrialState.COMPLETE]
print("Study statistics: ")
print("  Number of finished trials: ", len(study.trials))
print("  Number of pruned trials: ", len(pruned_trials))
print("  Number of complete trials: ", len(complete_trials))

# 結果の表示
print("Best trial:")
trial = study.best_trial
print("  Value: ", trial.value)
print("  Params: ")
for key, value in trial.params.items():
    print(f"    {key}: {value}")

"""
探索・学習中に出てくる大量の出力は省略。

Study statistics: 
  Number of finished trials:  100
  Number of pruned trials:  81
  Number of complete trials:  19
Best trial:
  Value:  0.92734375
  Params: 
    n_layers: 1
    n_units_l0: 114.75796459517468
    dropout_l0: 0.3088006045981605
    lr: 0.0035503337631505026

"""

100回のトライアルのうち、最後まで走ったのは19回だけのようです。
もっと色々なパターンを試さないと、これがどれほど効率的なのかと、
最終的な結果がどれだけ優れてるのかってのは判断しかねるのですが、
データが少ない中で90%程度の正解率も出していますし、
これまでMNISTを触ってきた経験からあまり違和感のないパラメータが選ばれている感じはします。

create_model の中で、最初に選ばれるlayerの数によって、
ユニットの数やdropoutの割合のなどのパラメーター数が変わるのですが、
Optunaならそのあたりもかなり柔軟にかけることを実感できました。

参考ですが、n_layersが3だったtraialでは、次のようにパラメータが多くサンプリングされています。


print(study.trials[3].params)
"""
{
    'n_layers': 3,
    'n_units_l0': 8.18713988230328,
    'dropout_l0': 0.3510040004516323,
    'n_units_l1': 99.80407964220358,
    'dropout_l1': 0.22630235294167378,
    'n_units_l2': 6.585776717612663,
    'dropout_l2': 0.3731719751601379,
    'lr': 0.000710144846966038
}
"""

なお、n_layersは1の方が良いようで、かなり早い段階で、1の場合ばかり探索されるようになっています。
時短のためデータを減らしているのも影響していそうですね。


print([study.trials[i].params["n_layers"] for i in range(100)])
"""
[3, 1, 1, 3, 2, 2, 2, 1, 1, 3, 1, 1, 1, 1, 2, 1, 2, 1, 1, 2,
 1, 1, 1, 1, 1, 1, 2, 1, 1, 2, 1, 1, 1, 1, 3, 1, 1, 2, 1, 1,
 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
 2, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
"""

np.nonzero()を応用して配列中の条件を満たす要素のインデックスを取得する

前回の記事の応用版です。
参考: Numpy配列の非ゼロ要素のインデックスを取得する

非ゼロに限らず、何か特定の条件を満たす要素を抽出したい場面はよくあります。
(正の数だけとか、数値以外の条件とか)
その場合も、nozeroを使うことができます。

これは、Pythonのbool型の値、Flaseが0と見なされることを利用します。
対象の行列に対して、各要素が条件を満たすかどうかを
bool型(True/False)で示す配列を作り、それに対してnonzero()を使います。

前回の記事で使った行列の例を使って、
値が奇数の要素のインデックスを取り出してみましょう。


import numpy as np

# データの準備
ary = np.array(
        [
            [0, 0, -2, 0],
            [1, 0, 2, 0],
            [0, 0, 1, 0],
            [3, 0, 0, 0],
        ]
    )

# 奇数要素のインデックスを取得する。
xx, yy = np.nonzero(ary % 2 == 1)
for x, y in zip(xx, yy):
    print(f"ary[{x}, {y}]={ary[x, y]}")

"""
ary[1, 0]=1
ary[2, 2]=1
ary[3, 0]=3
"""