Skip to content

Instantly share code, notes, and snippets.

@yuu-ito

yuu-ito/PureDataに投げるSQLを書く上で気にしていること.md

Last active July 26, 2016 15:16
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save yuu-ito/03e5cb022b493ac56869 to your computer and use it in GitHub Desktop.
Save yuu-ito/03e5cb022b493ac56869 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
PureDataに投げるSQLを書く上で気にしていること.md

PureDataに投げるSQLを書く上で気にしていること #vgadvent2014

※これはVOYAGE GROUP エンジニアブログ：Advent Calendar 2014の3日目の記事です。

もうこんな季節なんですね。 去年のエントリから1年経ってると思うとホントにあっという間にでした！

弊社では、運用しているメディアサービスのデータ解析環境としてDWH(データウェアハウス)製品のひとつであるPureData(IBM PureData for Analytics)を使っていたりします。 Amazon RedshiftやGoogle BigQuery などのクラウドサービスもあったりするので高性能なシステムでの分析ができる機会は増えてくるのかなと思います。

便利である一方、ウェブアプリケーションで利用するSQLに比べるとずっと大きなクエリになって 人が書いたのを読み解くのはもちろん、自分が以前書いたのを見直すのも一苦労です。

今回はPureDataに投げるSQLを書く上で意識していることをまとめたいと思います。 実行計画、パフォーマンスなどについては触れないので大雑把ではありますが、 ちょっとしたTIPS程度に見てもらえたらと思います。

サブクエリはwith句でまとめる

withを使うとインラインビュー(内部ビュー)を表現できます。 例えば、「11月にアクセスしたユーザのうち、10月にもアクセスしたユーザの人数」を集計するとしたら こんな感じで書けると思います。

with 
v_201410_users as ( select distinct user_id from access_log where date >= '2014/10/01' and date  < '2014/11/01' ),
v_201411_users as ( select distinct user_id from access_log where date >= '2014/11/01' and date  < '2014/12/01' )
select
 count(distinct user_id)
from v_201411_users 
where user_id in (select user_id from v_201410_users)

viewを使わない場合に比べると全体は大きくなってしまうかもしれないですが、 メインとなるクエリの部分は見渡しが良くなると思います。 加えてビュー毎に集計条件など確認しやすいです。またビューの自己結合で書いてもすっきり書けそうです。 さらにこれに別のキャンペーンに参加してたユーザを含める、もしくは除外するなど条件が増えるとクエリは大きくなるので インラインビューの効果がもっと出てくると思います。

window関数で表現できないか考えてみる

1週間の集計結果を数ヶ月分、日付を変数で入れ替えて...みたいなこと良くすると思います。 PureDataの場合、たくさんSQLを実行するよりも一度に集計したほうが早いので window関数、特にsum,count,maxなどの集合関数と組み合わせるパターンと rank関数でソートしたり結果を絞り込んだりすると一発で結果を返せるかもしれないです。

window関数に関してはPostgresのページがわかりやすいです。

僕がwindow関数について知ったのもPureData(当時はNetezzaでしたが)で、 over句の中のpartition by, order by あたりがややこしくてハマりましたが、覚えるととても便利です。

データ整形はできるかぎりDWHに寄せる

Webサービスで実行するようなSQLだと最初のクエリで何かしらのidのリストを返し、 別のSQLのin句に結果を加えてもう一度問い合わせるなどしたりすると思いますが 最初のidの件数が多いとクエリがとても大きくなります。 この場合も最初のwith句でそのidを抽出する条件を書き、メインとなるSQLに組み込むほうが DWHの内部で完結するため、断然速いです。 idの抽出条件も含めることができるのでメンテや共有もしやすいと思います。

分析にはRを使っているのですが、そのままPureDataのテーブルを抽出してこようものなら 作業PCのメモリに乗せきれなくなるので、サマリデータにするのはもちろん、reshapeのような縦横変換もSQLのcase文でガリガリ書いたりして 極力データ整形をPureDataに任せるようにしています。全体の作業効率もそのほうが良いです。 (話がそれますが、Rのデータ整形はmagrittr, dplyrを使い始めてからとても楽になりました。)

まとめ

  • with句を使ってインラインビューにするとメインのクエリがすっきりして読みやすい。
  • window関数は一般的な関数に比べると少し特殊だけど覚えると便利。
  • 全体の分析作業のパフォーマンスの上げるのであれば、データ整形は出来る限りDWHに寄せる。
    • 分析アプリケーションもしくはサービス上に持ってくるデータの量を減らすのが重要。

明日は @katzchang さんです。お楽しみに！

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment