こちらの本をご恵贈いただきました。
- 作者: 加嵜長門,田宮直人
- 出版社/メーカー: マイナビ出版
- 発売日: 2017/03/27
- メディア: Kindle版
- この商品を含むブログを見る
一般的なSQLの本というのはDB管理の一環としてのインフラエンジニア向けの技術書であることが多く、意外にもデータ分析を主目的としたSQLの使い方やクエリの工夫の仕方について書かれた本というのはあまり多くないんですよね。故に、多くのデータ分析者は難解なテーマを実現するクエリを書きたくても参考になる資料が乏しく、途方に暮れるわけです。僕もHive / Redshift / BigQueryのクエリを書きながら「こんな分析がしたいんだけど、それを実現できるクエリってないよなぁ。。。」と何度天を仰いだことか。
そんなところに突然降って湧いてきたのがこちらの新刊書。もう目次を見ただけで笑いが止まらなくなるほどのSQL黒魔術のオンパレードで、「これぞSQL黒魔術大全」と言いたくなるほどの素晴らしい(そして恐ろしい笑)内容に脱帽。ということで、発売から若干日が経っていますが遅ればせながら書評させていただこうと思います。
ちなみにconflict of interestとして、著者のお一人である田宮さんとは前々職以来の友人であり、これはそのご縁でご恵贈いただいた書籍の書評記事であることを予めお断りしておきます。
本書の内容
基本的には「同じ内容のデータ分析向けクエリをPostgreSQL / Hive / Redshift / BigQuery / SparkSQLで書き分けた事例を並べた」本です。これ物凄く重要な話で、例えばURLのパース関数があるSQL (Hive / SparkSQL)とないSQL (PostgreSQL / Redshift / BigQuery)があるわけで、そういう時にURLの入ったカラムからドメイン下のパスだけ抜いてくるみたいなことをしたい場合は各SQLごとにクエリを大きく書き分けなければいけないわけです。それらの書き分けが最初からなされて説明されているというのがこの本の一番優れている点だと思います。その他、GROUP BY句で選択できるkeyが各SQLで異なるみたいな細かいポイントも踏まえて書き分けられていて、非常に懇切丁寧だという印象です。
いずれのクエリ解説に関しても実際に動くクエリがきちんと掲載されていますので、できれば本書内のいずれの例に関しても読者の方でデータを用意してテーブルを立ててDBに入れて、実際に手元でクエリを写経しながら動作確認しつつ読み進めていくのが良いと思います。
序論
1 ビッグデータ時代に求められる分析力とは
2 本書で扱うツールとデータ群
あまりコードが出てこない字面の多い箇所ですが、特に2章のログデータの蓄積に関する下りを初めとして「データ分析基盤構築の実務」のエッセンスが詰まっていますので、これからデータ分析基盤を具体的に作っていこうと考えている人にとっては必読だと思います。またここで本書が扱う各種DBの個々の特徴や相違点がまとめられていますので、こちらも参考になるはずです。
基本のSQL
3 データ加工のためのSQL
本書はただのSQLの教科書ではないので、いきなりそこそこ難しいクエリが出てきます(笑)。手始めにCASE ~ WHEN句でコード値からカテゴリ変数に変換するクエリや、URLをパースするクエリ(恥ずかしながらHiveにURLをパースする関数があると初めて知りました汗)から入り、要約統計量を算出するクエリや、ややテクニカルなものだと横持ちデータを縦持ちデータに変換するクエリなんかも出てきます。
初歩的な分析のためのSQL
4 売上を把握するためのデータ抽出
ここから段々エグいクエリが増えてきます(笑)。例えば時系列データ分析のパートでは移動平均を算出するクエリや、ヒストグラムを作るための度数分布表を返すクエリなどが出てきます。というか、本書を読んで初めて度数分布表をSQLで作れるなんて知りました。。。あまりにもびっくりしたので引用すると(pp.124-128)、POSのトランザクションデータのテーブルをpurchase_detail_logとすると
WITH stats AS ( SELECT -- 金額の最大値 MAX(price) AS max_price -- 金額の最小値 , MIN(price) AS min_price -- 金額の範囲 + 1 , MAX(price) + 1 - MIN(price) AS range_price -- 階級数 , 10 AS bucket_num FROM purchase_detail_log ) , purchase_log_with_bucket AS ( SELECT price , min_price -- 正規化金額:対象の金額から最小金額を引く , price - min_price AS diff -- 階級範囲:金額範囲を階級数で割る , 1.0 * range_price / bucket_num AS bucket_range -- 階級の判定:FLOOR(正規化金額 / 階級範囲) , FLOOR( 1.0 * (price - min_price) / (1.0 * range_price / bucket_num) -- indexを1から始めるために1を足す ) + 1 AS bucket FROM purchase_detail_log, stats ) SELECT bucket -- 階級の下限と上限を計算する , min_price + bucket_range * (bucket - 1) AS lower_limit , min_price + bucket_range * bucket AS upper_limit -- 度数をカウントする , COUNT(price) AS num_purchase -- 合計金額を計算する , SUM(price) AS total_amount FROM purchase_log_with_bucket GROUP BY bucket, min_price, bucket_range ORDER BY bucket ;
と、書けるそうです(Hive / Redshift / BigQuery / SparkSQL)。
一歩進んだ分析のためのSQL
5 ユーザーを把握するためのデータ抽出
6 Webサイトでの行動を把握するためのデータ抽出
ここからさらにエグさが増してきます(笑)。5章はECサイトやアプリのユーザーに関する分析クエリがお題です。挙げるとキリがないんですが、例えば「ベン図を書くためのクエリ」「RFM分析のためのクエリ」とかは凄いですね。。。またかつて僕も書いたことがある「継続率・定着率を算出するクエリ」もここに出てきます。6章はwebサイト内でのユーザー行動の把握がお題で、例えば「購買ファネルの各ステップにおける離脱率を算出するクエリ」とか「Google Analyticsのユーザーフローと同じ数字を出すためのクエリ」とかが出てきます。。。ここまで来るともう唖然とするばかりです。
どう見ても本来なら別の言語でやるべき分析のためのSQL黒魔術
7 データ活用の精度を高めるための分析術
8 データを武器にするための分析術
そして、ここに黒魔術極まれりという感じの内容が来ます。7章は様々な付随情報を活用して地理データや土日祝日データなどを結合させて、新たな特徴量を作るというようなお題がメイン。特定IPアドレスからのアクセスを除外するなどの異常値検出や、異なるテーブル間の差分の抽出や、スピアマンの順位相関係数の算出などが扱われています。8章はサイト内検索行動の把握・集計や、ずばりデータマイニングがお題。一番凄いなと思ったのが、アソシエーション分析のsupport / confidence / liftを算出するクエリや、item-to-item / user-to-itemレコメンドのスコアを算出するクエリ。。。SQLでこんなことまで出来るんですね(汗)。本書pp.411-416から引用すると、action_logをアイテムの閲覧・購入ログテーブルとして
WITH ratings AS ( SELECT user_id , product -- 商品の閲覧数 , SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END) AS view_count -- 商品の購入数 , SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS purchase_count -- 閲覧数と購入数を3:7の割合で重み付き平均する , 0.3 * SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END) + 0.7 * SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS score FROM action_log GROUP BY user_id, product ) , product_base_normalized_ratings AS ( -- アイテムを基準にした2ノルム正規化を行う SELECT user_id , product , score , SQRT(SUM(score * score) OVER(PARTITION BY product)) AS norm , score / SQRT(SUM(score * score) OVER(PARTITION BY product)) AS norm_score FROM ratings ) SELECT r1.product AS target , r2.product AS related -- 両方のアイテムを閲覧または購入しているユーザー数 , COUNT(r1.user_id) AS users -- スコア同士の掛け算を合計して、関連度を計算する , SUM(r1.norm_score * r2.norm_score) AS score -- 商品の関連度順 , ROW_NUMBER() OVER(PARTITION BY r1.product ORDER BY SUM(r1.norm_score * r2.norm_score) DESC) AS rank FROM product_base_normalized_ratings AS r1 JOIN product_base_normalized_ratings AS r2 -- 共通のユーザーが存在する商品のペアを作成する ON r1.user_id = r2.user_id GROUP BY r1.product, r2.product ORDER BY target, rank ;
と書けるようです(滝汗)。一応、このやり方だとコサイン類似度を算出しているのと同じことになるため、行列分解系の手法を使った時ほど万全ではないものの、基本的なレコメンドはこれで出来ていることになると思います。
書評・感想など
一言で言うと「よくぞここまでSQL黒魔術ばかりをこんなに沢山集められたなぁ。。。」というのが偽らざる感想です(汗)。確かに、世の中にはRやPythonはいざ知らずJavaやC / C++などで扱おうとしてもデータ容量が大き過ぎて分析に回せず、やむなくDB上で全て完結させたくなるようなシチュエーションというのは割と多いのですが、だからと言ってDB上でSQL黒魔術を駆使して完結させようという人はまだまだ珍しいと思います(笑)。
ということで、既に他のプログラミング言語・データ分析フレームワークでscalableな環境を構築できている人には必ずしも有用ではないかもしれませんが、それでもある程度のレベルまでDB上でデータ処理・分析を完結させたい時には本書は絶対に(そして必要以上に笑)役立つ一冊だと思います。いかなる種類のDBであれ、データ分析用途に触ることが多い人であれば必携と言っても過言ではないでしょう。。。ともあれ凄い本でした。