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

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

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

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

なお、掲載する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冊です。

C# JSON文字列から不要な要素を削除する

JSONの扱いでちょっとした前処理が必要となりましたので、メモしておきます。

以下のようなフラットなJSON文字列を扱うケースがありました。

{
  "key1": "value1",
  "key2": "value2"
}

任意のキーと値(文字列型)が追加されるので、Dictionary型とした方が都合が良く、以下のようにJsonConvert.DeserializeObject<Dictionary<string, string>>(json)でデシリアライズさせていました。

using System;
using System.Collections.Generic;
using Newtonsoft.Json;

namespace ConsoleApplication
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var json = "{\"key1\":\"value1\",\"key2\":\"value2\"}";

            var dictionary = JsonConvert.DeserializeObject<Dictionary<string, string>>(json);

            foreach (var t in dictionary.Keys)
            {
                Console.WriteLine($"{t}: {dictionary[t]}");
            }
        }
    }
}

しかし業務要件の変更で、1つのキーのみ配列が渡されるようになりました。 （JSONでやり取りしている以上、当然そういうケースもあります。）

{
  "key1": "value1",
  "key2": "value2",
  "array1": [
    { "childkey1": "childvalue1" },
    { "childkey2": "childvalue2" }
  ]
}

フラットなkey-value構造が崩れるので、このままではパースできずにエラーとなってしまいます。

Unhandled Exception: Newtonsoft.Json.JsonReaderException: Unexpected character encountered while parsing value: [. Path 'array1', line 1, position 43.

これを回避するためには、パースできるクラスを自製してそのオブジェクトにデシリアライズする方法もありますが、もしarray1が必要無いのであれば、JSON文字列からarray1のキーと値を削除する方が楽です。

こうしたケースに便利なのがJObject(Newtonsoft.Json.Linq名前空間)で、JSONに対してLINQで処理できるようになります。

くだんの問題は、一旦JObjectとしてJSON文字列を前処理させることで解決できます。

1. JSON文字列をJObjectへデシリアライズさせて、Remove(LINQ)でarray1を削除する
2. 再度文字列にシリアライズしてから、Dictionary<string, string>へデシリアライズする

using Newtonsoft.Json.Linq;

var json = "{\"key1\":\"value1\",\"key2\":\"value2\",\"array1\":[{\"childkey1\":\"childvalue1\"},{\"childkey2\":\"childvalue2\"}]}";

var jobj = JObject.Parse(json);
jobj.Remove("array1");

var dictionary = JsonConvert.DeserializeObject<Dictionary<string, string>>(jobj.ToString());

キー名がわからずとにかく第1階層にある文字列型だけを取り出したいという場合も、JObjectはICollection<KeyValuePair<string, JToken>>を実装しているので、以下のようにLINQで処理できます。

jobj = JObject.Parse(json);

var dictionary = new Dictionary<string, string>();

foreach (var j in jobj)
{
    if (j.Value.Type == JTokenType.String)
    {
        dictionary.Add(j.Key, j.Value.ToString());
    }
}

// ToDictionary()を使えば1行で書けます
dictionary = jobj.Properties()
                .Where(jp => jp.Value.Type == JTokenType.String)
                .ToDictionary(jp => jp.Name, jp => jp.Value.ToString());

JSONで何らか前処理が必要になったときのツールとして持っておくと便利ですね。

「Pythonによる機械学習入門」 第2部 基礎編のまとめ

「Pythonによる機械学習入門」を読みましたので、第2部で得たことをまとめます。

総評すると「scikit-learnを使えば機械学習で有名な各種手法がお手軽に試せるぜ」といった感じです。

あくまで入門なので「どう使えば良いのか？」が主たる関心事で、「その方法でなぜ答えに近づけるのか？答えから遠のいてしまうのか？」「AとBの使い分けをどうすれば良いのか？」といった手法に対する理論的な説明は、別で学ぶ必要があります。

とはいえ、scikit-learnに加えてpandasやpyplotなども紹介されており、第3部ではデータのクレンジングやグリッドサーチなどについても触れられていますので、今あるデータをこね回してみるのには十分かと思います。

全体は4部構成で、第1部が導入、第2部が分類・回帰・クラスタリングに関する基礎編、第3部が手の写真画像を使った分類とセンサデータの回帰を行う実践編、第4部が付録となっており、今回は第2部についてまとめます。

Pythonによる機械学習入門

第3章 分類問題

scikit-learn付属のdigitsデータセット（8×8ピクセルの手書き数字画像）を使って、0〜9のラベルで分類します。

scikit-learnを使えば、classifier = tree.DecisionTreeClassifier()で呼び出すメソッドを変えることで簡単に分類器の種類へ変更できます。

from sklearn import tree
from sklearn import metrics

# 学習
classifier = tree.DecisionTreeClassifier()
classifier.fit(images[:train_size], labels[:train_size])

# 検証
predicted = classifier.predict(images[train_size:])

# 比較
print('Accuracy:', metrics.accuracy_score(expected, predicted))

• 学習データと訓練データの分離方法
  • ホールドアウト検証では、対象データの一部をテストデータとして取り出してそれ以外全てを学習データとする
  • k-分割交差検証では、対象データをk個に分割して、内1個をテストデータ・それ以外を学習データとして、k回の学習・検証（推論）を繰り返し、平均値を認識率とする
    • 少数のデータの中で学習データとテストデータをやりくりする方法で、kが小さいほど学習データが増えるので性能は向上させやすい（学習・検証の回数が増えるので、時間はかかる）
• 分類器の性能指標
  • 正答率（Accuracy）：全検証データの内、正しく分類された検証データの割合
  • 適合率（Precision）：あるラベルに分類されたデータの内、正しく分類された検証データの割合
  • 再現率（Recall）：あるラベルに分類されるべき検証データの内、そのラベルに分類された割合
  • F値（F-measure）：適合率と再現率の調和平均
  • 検証データに偏りがある場合、ラベルごとに算出される適合率や再現率の方が実体を正しく捉えられるケースも有る
• アンサンブル学習では、性能の低い分類器（弱仮説器）を組み合わせて、それぞれの分類結果を集約する
  • Random Forestでは、学習データセットから重複・欠落を許したサブセットを作って、サブセット数だけ弱仮設器を学習させて、多数決で検証する（バギング）
  • AdaBoostでは、難度の高い学習データを分類できる弱仮設器を重視するように、重み付けする（ブースティング）
• SVMでは、サンプルとの距離の二乗和が最大となるように分割する、2クラス分類器
  • 3クラス以上の場合は複数の分類器を組み合わせる

第4章 回帰問題

線形関数・非線形関数の値に乱数値を加えて擬似的に生成した波形のサンプルに対して、scikit-learnで線形回帰・非線形回帰を行います。

• 線形回帰と非線形回帰
  • y=x²の場合でも、X=x²と変数に置き換えられるなら、それは線形回帰できる
• model = linear_model.LinearRegression()を置き換えることで、使うモデルを変更できる
  • 最小二乗法、SVM、Random Forest、k-近傍法

from sklearn import linear_model

# 学習
model = linear_model.LinearRegression()
model.fit(x, y)

# 結果（傾きと切片）
print(model.coef_)
print(model.intercept_)

• 重回帰（2変数以上）の場合は、入力を[[x1_1, x2_1], [x1_2, x2_2], ...]のような多次元配列にする
  • y = 3 * x**2 + 2 * x + 4の場合も、[[x1**2, x1], [x2**2, x2], ...]とすることで重回帰分析（線形）できる

x1_x2 = np.c_[x1, x2]
model = linear_model.LinearRegression()
model.fit(x1_x2, y)

• 変数が多すぎる（例えば、4変数で表せる関数を9変数で回帰させる、など）と、表現力が高すぎて過学習の原因となる
  • モデルの複雑度を加味するRidge回帰やLasso回帰（罰則付き回帰）
  • 変数の数（ハイパーパラメータ）をどうやって推定するのか

第5章 クラスタリング

scikit-learn付属のirisデータセットを使って、花弁の長さと幅の値からあやめの品種（3種類）に分類します。

from sklearn import datasets

iris = datasets.load_iris()

iris['data'][i][j] # i番目のあやめのj番目のデータ
iris['target'][i] # i番目のあやめの品種

• k-meansではクラスタ数を予め決めて、クラスタに属するデータから中心への距離が最小となるように、所属や中心を変更する

from sklearn import cluster

# k-means法で3クラスタに分類
model = cluster.KMeans(n_clusters=3)
# 学習
model.fit(data)

• 階層的凝集型クラスタリングでは、１データ１クラスタからスタートして、近くのデータを凝集させながら、指定のクラスタ数まで絞る
  • 最短距離法、最長距離法、群平均法、ウォード法
• 非階層的クラスタリング（k-meansもこの１つ）では、評価関数を定義して、その評価関数が最適になるように分割する方法
  • Affinity Propagation