BLOG ブログ

Tableau

SQLでデータプレパレーション

はじめに

いつも固い話題ばかりブログに書いておりますので、今日は柔らかめの話題を取り上げます。先日、私の友人が「小江戸大江戸200km」というウルトラマラソン大会に参加しました。

最大36時間かけて206kmを踏破するという過酷なイベントです。

そのイベントに参加した友人の記録ですが以下の通りにWebサイトに掲載されています。

SQLでデータプレパレーション

5列からなる上記データの列構成は以下の通りになっています。

  • 順位
  • 計測地名
  • スタート地点から計測地ごとの累計距離
  • スタート地点から計測地ごとの走行時間
  • 計測した時刻

このデータをデータベース(今回はBigQuery)にアップロードして、SQLでデータプレパレーションを行い、BIツールTableau側ではあえて計算式を利用せずに、計測地間ごとの平均速度を可視化してみたいと思います。

SQLが使えるよう、まずはBigQueryにアップロードする

SQLはデータベースに対して適用するので、まずはデータをBigQueryにアップデートする必要があります。BigQueryのカラム名(=列名)には、日本語などの2バイト文字は使えないので、大会のデータをCSVに落とし、手元のCSVファイルでカラム名を英数に変更します。

するとBigQueryにアップデートできるようになります。

こちらがアップロードが完了したテーブルのスキーマです。

SQLでデータプレパレーション

データベース上の「表」のことはテーブルと呼びます。スキーマとはどのような種類のデータをどのような構造で格納しているかを定義したもので、以下のスキーマからは各列の値がどのようなデータ型で格納されているのかを理解することができます。

具体的には、

  1. rankというフィールド名の列には、整数(INTEGER)型の値
  2. placeというフィールド名の列には、文字列(STRING)型の値
  3. disatnceというフィールド名の列には、浮動小数点型(FLOAT)型の値
  4. durationというフィールド名の列には、文字列(STRING)型の値
  5. timeというフィールド名の列には、タイムスタンプ(TIMESTAMP)型の値

がそれぞれ格納されていることが分かります。

データ型は、関数を使うときに少し気にする必要があります。いくら数字に見えてもデータ型が仮に文字列型であれば四則演算はできません。

プレビューボタンをクリックすると実際にどのような形でデータが格納されているのかを確認することができます。

SQLでデータプレパレーション

きれいにデータが格納されていますね。

SQLで直前の行の値を取得する

区間ごとの平均速度(分/km)を求めるには区間ごとの「所要時間」と「走行距離」が必要です。それらが同一の行にあれば、単純な割り算で平均速度は求めることができます。

ところが、上記のテーブルを見ると、所要時間はスタートからの累計の経過時間(duration)、もしくは計測時刻(time)が記録されていて、必要な所要時間はどの列にも存在しません。走行距離も同様で、スタートから累計の走行距離はdistance列に記録されていますが、区間ごとの走行距離は記録されていません。

さて、どうしましょうか?

ここで知ってほしいのは、SQLでは、同一行にある値同士は計算できる。ということです。ということは、例えば、計測地間の走行距離を求めるのに、上記のテーブルの各行に「直前行のdistance」があれば、引き算で区間における走行距離が求められそうです。

こんな感じですね。offset_distanceという新しい列に直前の行のdistanceの値が格納されているのが確認できます。

SQLでデータプレパレーション

プレビューで見たもともとのテーブルに対して記述したSQLは以下のたった7行です。しかも関数を使っているのは3行目だけです。LAGという関数が(何行前でも良いのですが、何も指定しないと直前の行)直前の行のdistanceの値を取得しています。

SQLでデータプレパレーション

こんな簡単な記述でデータプレパレーションができる。ということをまず感じていただければ嬉しいです。

では、distanceからoffset_distanceを差し引いて、区間の走行距離を求めます。

rankは今は 不要なので、取得しないことにして、4行目を追加して、以下のように記述しました。

SQLでデータプレパレーション

結果は、以下です。step_distanceの列を見てください。うまくいっていますね。

SQLでデータプレパレーション

SQLでタイムスタンプ型の値の引き算をする

step_distanceで区間間の走行距離が取得できたので、次は、区間間の所要時間を取得します。 考え方は走行距離の場合 と同じで、直前の行のtimeの値を取得して、例えば
offset_timeとし、timeからoffset_timeを引き算すればいいですね。

SQLは以下の通りになります。不要な列は除いています。

SQLでデータプレパレーション

結果 は以下の通りです。5行目にあるTIMESTAMP_DIFFというのがタイムスタンプ型の値の引き算をする関数です。最後の引数、minuteを指定しているので結果 を分単位で取り出しています。

SQLでデータプレパレーション

ここまでくれば、step_time_minを、step_distanceで割って、平均速度(分/km)を求めるのは簡単ですね。

SQLで見やすく整形する

以下のような結果を得ることができました。

SQLでデータプレパレーション

すっきりと見やすいテーブルになっていますね。上記を実現したSQLは以下となっていて、ROW_NUMBER()という行番後 を振る分析関数やCONCATという文字列連結 を行う関数を利用したり、サブクエリというテクニックで見やすく整形したりしています。

SQLでデータプレパレーション

データプレパレーションしたデータにTableauから接続する

上記でデータプレパレーションの終わ った 状態 のデータはBigQueryにビューやテーブルとして保存することもできますし、TableauからBigQueryにカスタムSQLを使って接続することもできます。

今回はBigQueryに記 述したSQLをそのままコピペ で利用して、カスタムSQL経由で接続してみます。

SQLでデータプレパレーション

完成したVizはこちらです。データソースにavg_speedがあるので、Tableau側では一切計算式を作ることなく、可視化に成功しました。

SQLでデータプレパレーション

SQLの分析関数(WINDOW関数)

上記の分析を行う上で、「肝」となった、直前の行の値を取得する関数はLAG()という関数でした。LAG()は分析関数(WINDOW関数とも呼ばれます)のひとつであるナビゲーション関数の一種です。分析関数には他にも、 番号付け関数や集計分析関数など、多くの強力な関数が用意され、必要なデータプレパレーションを助けてくれます。

まとめ

如何でしたでしょうか?SQLが特に、分析関数を利用してデータプレパレーションにどう使えるのか?おおよそのイメージはつかんでいただけたのではないでしょうか?

今後、データは、大きく、粒度が 細かく、また、複数のデータの結合 が必要になってくると思 われます。すると、我々 分析から価値を引き出すことを仕事にしている人間は、もうエクセルだけでは太刀打ちできないばかりか、BIツール「だけ」ができても効率良い可視化を実現することは難しく、SQLを駆使したデータプレパレーションのスキルが求められるようになるのではないかと思います。

ご意見・ご相談、料金のお見積もりなど、お気軽にお問合わせください

お問合せ メルマガ登録 資料DL

TOP