「10年戦えるデータ分析入門」第1部まとめ

最近は生データの分析などで難しめのSQLを書くことが増えてきましたので、SQLの地力を鍛えるために、クックパッド・青木さんが著者の「10年戦えるデータ分析入門」を読みました。

本書は2部構成となっており、第1部ではSQLを使ったデータ分析のテクニック、第2部では分析のためのインフラシステムの構築についてそれぞれ述べられてます。今回は第1部で知らなかったことや「これは使いでがあるな」と思ったことを、備忘録として整理します。

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)

作者: 青木峰郎
出版社/メーカー: SBクリエイティブ
発売日: 2015/06/30
メディア: 単行本
この商品を含むブログ (6件) を見る

なお、掲載するSQLの一部は、DVD Rental ERモデルを使ったものとなっています。

PostgreSQL Sample Database

第3章

ランダムサンプリング

random()で0.0以上1未満の値が得られるので、where句で使うとランダムサンプリングができます。

select
  *
from 
  payment
where
  random() < 0.001
;

第5章

date_trunc関数

date_trunc('month', request_time)で、月以降を"1日 00:00:00"で切り捨てることができるので、年ごと・月ごと・日ごとなどに集計する場合に便利です。

select 
  *
from
  payment
where
  date_trunc('month', payment_date) = timestamp '2007-02-01 00:00:00'
;

第7章

coalesce関数

nullでない最初の値を返す。nullを含むカラムの値に初期値を設定するなど、使い途が多いです。

select 
  coalesce(staff_id, 0) as staffid
  , count(*)
from
  payment
group by
  staffid
;

併売率

「ある商品を購入する場合、この商品も合わせて購入されている」割合を併売率というそうです。セルフジョインで併売率を求めることができます。

select
  c.item_id
  , c.item_id2
  , cast(c.order_count as real) / i.order_count as confidence
from
  (
    select
      l.item_id
      , r.item_id as item_id2
      , count(distinct l.order_id) as order_count
    from
      order_details as l
      inner join order_details as r
      on l.order_id = r.order_id
        and l.item_id <> r.item_id
    where
      l.order_time between timestamp '2015-04-01 00:00:00' and timestamp '2015-04-30 23:59:59'
  ) c
  inner join
  (
    select
      item_id
      , count(distinct order_id) as order_count
    from
      order_details
    where
      order_time between timestamp '2015-04-01 00:00:00' and timestamp '2015-04-30 23:59:59'
  ) i
  on c.item_id = i.item_id
;

第8章

恥ずかしながら、ウィンドウ関数をよく知らなかったので、8章は特に勉強になりました。

スカラーサブクエリ

select節やwhere節に式の一部としてもサブクエリが使える。

-- 顧客毎の支払合計が全体の支払合計に占める割合を計算
select 
  customer_id
  , sum(amount)
  , sum(amount) / (select sum(amount) from payment)
from
  payment
group by
  customer_id
;

sumウィンドウ関数

partition byで指定された値ごとに合計を算出できます。

ウィンドウ関数全体に言えることですが、行を集約されずに(group byと異なる点)、集約関数の結果の値を得られるという利点を持っています。

-- 顧客毎の総支払額を3番目のカラムで表示
select 
  customer_id
  , amount
  , sum(amount) over (
      partition by customer_id
    ) as customer_total
from
  payment
;

rankウィンドウ関数

ウィンドウ関数の計算はwhereやhavingよりも後のため、絞り込む場合はサブクエリ化して、外側のクエリのwhereで絞る必要があります。

-- 支払総額上位3名の支払時刻・支払額の一覧を列挙する。
select
  *
from (
  select 
    customer_id
    , payment_date
    , amount
    , rank() over (
      partition by customer_id
      order by amount desc
    ) as customer_rank
  from
    payment
) tmp
where
  customer_rank <= 3
order by
  payment_date
;

累積和

sumウィンドウ関数を使って累積和を計算します。 rows between unbounded preceding and current rowとすることで、「過去の全ての行から現在の行まで」と指定するのがミソで、ウィンドウフレームと言うそうです。 n行前ならn preceding、n行後ならn followingという指定もできます。

-- スタッフ毎の売上の累積和を計算する
select
  t.sales_date
  , t.staff_id
  , t.sales_amount
  , sum(sales_amount) over (
      partition by staff_id
      order by sales_date
      rows between unbounded preceding and current row
    ) as cumulative_sales_amount
from (
  select
    date_trunc('day', payment_date) sales_date
    , staff_id
    , sum(amount) sales_amount
  from
    payment
  group by
    sales_date
    , staff_id
  ) t
;

デシル分析

デシル分析は、ある値でソート（昇順・降順）し、行を任意のn個に等分割し、上から番号を振っていく分析で、売上額に応じて顧客をランク付けするといったケースに使えます。

-- 月ごとの売上に応じて顧客を3段階のランクに分ける
select
  t.sales_month
  , t.customer_id
  , t.sales_amount
  , ntile(3) over (
      partition by sales_month
      order by sales_amount desc
    ) as customer_rank
from (
  select
    date_trunc('month', payment_date) sales_month
    , customer_id
    , sum(amount) sales_amount
  from
    payment
  group by
    sales_month
    , customer_id
  ) t
;

第9章

generate_seriesテーブル関数（PostgreSQL）

連番のみのテーブルを生成する関数です。以下の場合では、1から4の値が入った1カラム×4行のテーブルが生成されます。

select * from generate_series(1, 4);

JSONをカラムに展開する（PostgreSQL）

「そんなことできるんだ！」と驚いたSQLの1つですが、JSON文字列が入った1つのカラムを、キー毎に複数のカラムへ展開させています。以下のSQLではjson_paramsに{"view_seconds":36,"scroll_ratio":0.7,"click_button":"discount_campaign"}といった文字列が入っており、3つのカラムに展開しています。ポイントは3点です。

PostgreSQLにはjson型があり、文字列からキャストできる
json_to_recordテーブル関数で、view_seconds、scroll_ratio、click_buttonの3カラムからなる1行のテーブルを生成できる
最後にクロスジョインして、元のaccess_logテーブルにくっつける

select
  l.request_time
  , v.view_seconds
  , v.scroll_ratio
  , v.click_button
from
  access_log as l
  cross join json_to_record(cast(l.json_params as json))
    as v (
      view_seconds integer
      , scroll_ratio real
      , click_button text
    )
;

第10章

lagウィンドウ関数

前の行の値を取得できる関数です。前回のアクセスからどのくらい時間が経ったか、などを算出できます。

-- 顧客毎に前回の支払が何時間前かを計算する
select
  payment_date
  , customer_id
  , date_trunc('hour',
      payment_date - lag(payment_date) over (
        partition by customer_id
        order by payment_date
      )
    ) as payment_interval
from
  payment
;

正規表現とのマッチング

今までlikeくらいしか使ってきませんでしたが、正規表現によるマッチングとCaseを組み合わせると、強力なツールになりそうです。

-- カテゴリ名でo・・・e・・・yに該当する行数をカウント（'Comedy'など）
select
  sum(case when name ~ 'o+[a-z]*e+[a-z]*y$' then 1 else 0 end)
from
  category
;

まとめ

アクセスログのセッション化やコンバージョンレートの算出など、実際の分析に近いSQLとなっているので、理解が捗りました。おすすめの1冊です。