今更だけど尊敬している先輩が書いた本を読んだ。よかった。
内容はそんな難しくなくて、ある程度知っていることも多かった。今までは雰囲気で分析SQLを書いていたので group by とウィンドウ関数に苦手意識があったがそれが減ったのと、今後分析するときにこの本に還ってくれば良いという安心感を得られた。
セルフジョインやクロスジョイン、スカラーサブクエリーなどは記憶になかったので知れてよかった。
私は分析基盤を構築することはあまりないかなあと思っているので、後半の構築や運用の話は流し読みした。でもその中でジョブ管理システムが出てきて、新卒の時にWindows98みたいな見た目のジョブ管理システム(たぶんJP1だったと思う)を使っていたことや、泣きながらSybaseのストアドプロシージャを書いていたことを思い出し懐かしい気持ちになった。
以下雑メモ:
- ランダムサンプリング
where random() < 0.001みたいにしてデータからランダムに何件か取得する
- group by の絞り込みは having
- interval xx時間前後
- extract 年や月、日を取り出せる
- date_trunc
2014-09-28 12:34:56を2014-09-01 00:00:00みたいにできる - セルフジョイン
- 自分自身とジョインする
- 1年前のデータとジョインして伸び率を出すなど
- クロスジョイン
- 全ての組み合わせを生成する
- 縦持ち横持ちテーブルの変換など
- スカラーサブクエリー
- select や where で利用できるサブクエリ
- ウィンドウ関数
- view はテーブルのように見えるが使われるたびに select を実行する
create view hoge as select ...- with でもいけそう?
- case で条件分岐
case 式 X when 式1 then 結果1 ... else 結果E end - PostgreSQL には json_to_record 関数があり json を変換できる
- セッション分析
- lag ウィンドウ関数を使って前の行の値を取ることができる
- 分析SQLにおいてはSQLのindexはほぼ効果がないが、論理パーティションという日や月毎に切ったテーブルを用意しておくのは効果的
