渋谷駅前で働くデータサイエンティストのブログ

元祖「六本木で働くデータサイエンティスト」です / 道玄坂→銀座→東京→六本木→渋谷駅前

『ビッグデータ分析・活用のためのSQLレシピ』はデータ分析でSQLクエリを叩く人なら必読の黒魔術大全

こちらの本をご恵贈いただきました。

ビッグデータ分析・活用のためのSQLレシピ

ビッグデータ分析・活用のためのSQLレシピ

一般的な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に限らずデータ分析のために必要な心構え

9 知識に留めず行動を起こす

いわゆるデータ分析プロセスについての解説が最後に1章割かれています。データ分析業界で特にRやPythonを駆使するような界隈では既知の話題も多いかと思いますが、SQL周りからデータ分析業界に入ろうとしている人にとっては有用なパートなので一読することをお薦めいたします。


書評・感想など


一言で言うと「よくぞここまでSQL黒魔術ばかりをこんなに沢山集められたなぁ。。。」というのが偽らざる感想です(汗)。確かに、世の中にはRやPythonはいざ知らずJavaやC / C++などで扱おうとしてもデータ容量が大き過ぎて分析に回せず、やむなくDB上で全て完結させたくなるようなシチュエーションというのは割と多いのですが、だからと言ってDB上でSQL黒魔術を駆使して完結させようという人はまだまだ珍しいと思います(笑)。


ということで、既に他のプログラミング言語・データ分析フレームワークでscalableな環境を構築できている人には必ずしも有用ではないかもしれませんが、それでもある程度のレベルまでDB上でデータ処理・分析を完結させたい時には本書は絶対に(そして必要以上に笑)役立つ一冊だと思います。いかなる種類のDBであれ、データ分析用途に触ることが多い人であれば必携と言っても過言ではないでしょう。。。ともあれ凄い本でした。