け日記

SIerから転職したWebアプリエンジニアが最近のITのキャッチアップに四苦八苦するブログ

「データ集計・分析のためのSQL入門」 まとめ

データ分析ソリューション事業を軸に展開されているALBERTの方々によって書かれた「データ集計・分析のためのSQL入門」を読みましたので、備忘録としてまとめておきます。

全5章で構成されており、集計・分析レポートの基本(Chapter 1)、SQLの基本(Chapter 2)、基本統計量や偏差値の算出(Chapter 3)、デシル分析・RFM分析(Chapter 4)、アソシエーション分析・時系列分析・アトリビューション分析(Chapter 5)といった内容となってます。

データ集計・分析のためのSQL入門

データ集計・分析のためのSQL入門

今回もDVD Rental ERモデルを使ったSQLとなっています。

PostgreSQL Sample Database

Chapter 3

尖度

正規分布と比較したときのデータ分布が尖り具合を表す指標で、正の値となるときは分布が集中している(=尖っている)、負の値となるときは分布が分散している(=なだらか)ことを示します。

f:id:ohke:20170504224141p:plain

select (
    select
      (t1.n*(t1.n + 1.0)) / ((t1.n - 1.0)*(t1.n - 2.0)*(t1.n - 3.0))
    from
      (
        select 
          count(amount) as n
        from
          payment
      ) t1
  ) * (
    select
      sum(power(payment.amount - t2.a, 4)) / (1.0 / (t2.n - 1) * sum(power(payment.amount - t2.a, 2)))
    from
      (
        select
          count(amount) as n
          , avg(amount) as a
        from
          payment
      ) t2
      , payment
    group by
      t2.n
      , t2.a
  ) - (
    select
      (3.0 * power((t3.n - 1.0), 2) / ((t3.n - 2.0) * (t3.n - 3.0)))
    from
      (
        select
          count(amount) as n
        from
          payment
      ) t3
  ) as kurtosis
;

歪度

正規分布と比較したときのデータ分布の歪み具合を表す指標で、正の値となるときは分布が正の方向に歪んでいる、負の値となるときは分布が負の方向に歪んでいることを示します。

f:id:ohke:20170504224156p:plain

select (
    select
      t1.n / ((t1.n - 1.0)*(t1.n - 2.0))
    from
      (
        select 
          count(amount) as n
        from
          payment
      ) t1
  ) * (
    select
      sum(power(payment.amount - t2.a, 3)) / power(1.0 / (t2.n - 1) * sum(power(payment.amount - t2.a, 2)), 1.5)
    from
      (
        select
          count(amount) as n
          , avg(amount) as a
        from
          payment
      ) t2
      , payment
    group by
      t2.n
      , t2.a
  ) as skewness
;

Chapter 4

WITH ROLLUP関数(SQL ServerMySQL

GROUP BY句で指定した列ごとに合計を持つ行を追加されます。 以下では、最終行に男性・女性・totalの総合計値が表示されます。

select
  coalesce((cast(age10s as varchar(8)), 'total') as '年代'
  , sum(case when sex = 'Man' then 1 else null end) as '男性'
  , sum(case when sex = 'Woman' then 1 else null end) as '女性'
  , count(*) as 'total'
from
  test
group by 
  age10s
with rollup
;

PERCENT_RANKウィンドウ関数

デシル分析に使う関数で、order byで指定した値が全体の値域の中で上位何%に入るのかを計算します。 以下のSQLではcustomer_id毎の売上をランキング化しています。

select
  payment_id
  , amount
  , percent_rank() over (order by amount desc) as pct_rank
from
  payment
order by
  payment_id
;

RFM分析

「売上」という1つの指標だけではなく、Recency(より最近購入している顧客のほうが優良顧客)、Frequency(より購買頻度が高い顧客のほうが優良顧客)、Monetary(より購買金額が大きい顧客のほうが優良顧客)の3つの指標を導入することで、それぞれのグループの特性を捉えて分析できるようになります。

閾値を設けてRFMそれぞれで顧客を3段階に分類するSQLは以下のようになります。 ランクごとにcase文でラベルを付けています。

  • 2007/5/1を起点として、最終訪問が1日前までならR1、2日前までならR2、それより前ならR3
  • 2007/5/1までの、訪問回数が25回以上ならF1、25回未満20回以上ならF2、20回未満ならF3
  • 2007/5/1までの、合計金額が120以上ならM1、120未満100以上ならM2、100未満ならM3
select
  t.customer_id
  , case
      when t.diff_date <= 1 then 'R1'
      when t.diff_date > 1 and t.diff_date <= 2 then 'R2'
      when t.diff_date > 2 then 'R3'
    end as recency_rank
  , case
      when t.frequency >= 25 then 'F1'
      when t.frequency < 25 and t.frequency >= 20 then 'F2'
      when t.frequency < 20 then 'F3'
    end as frequency_rank
  , case
      when t.total_amount >= 120 then 'M1'
      when t.total_amount < 120 and t.total_amount >= 100 then 'M2'
      when t.total_amount < 100 then 'M3'
    end as monetary_rank
from
  (
    select
      customer_id
      , date_part('day', '2007-05-01' - max(payment_date)) as diff_date
      , count(amount) as frequency
      , sum(amount) as total_amount
    from
      payment
    where
      payment_date < '2007-05-01'
    group by
      customer_id
  ) t
order by
  customer_id
;

Chapter 5

アソシエーション分析

「商品Xを購入した顧客は高確率で商品Yも購入している」といったルールを導き出して、レコメンデーションなどの施策に繋げていくのが、アソシエーション分析で、Confidence(信頼度)、Support(支持度)、Lift(リフト)の指標で定量化されます。

  • 信頼度は、Aを買う場合にBも一緒に購入される割合で、商品間の関連性の度合いを表す指標
    • AとBを購入した回数 ÷ Aを購入した回数
    • 方向性があり、先の例では「Aを買う場合にBも一緒に購入される割合」と「Bを買う場合にAも一緒に購入される割合」は、一般に一致しない
  • 支持度は、全購入の内、商品Aと商品Bが一緒に購入される割合で、その関連が全体の中でどの程度ボリュームがあるのかを表す指標
    • AとBを購入した回数 ÷ 全購入回数
    • たまたま1回AとBを一緒に購入された場合に、信頼度が100%になるが、支持度を使って足切りする
  • リフトは、商品Aを買った人に商品Bをおすすめすることで、何もしない場合と比べて、商品Bの購買確率がどの程度上がるのかを表す割合
    • Aを購入するとBも購入する確率 ÷ 全購入回数の中でBが購入される確率
    • リフトが1より小さい場合は、おすすめしないほうが良い
  • 組合せ爆発を起こすので、支持度が一定以下のアイテムについては、はじめから信頼度を計算しないことで計算量を減らすことができる(アプリオル・アルゴリズム)

以下のSQLではcustomer_id毎に、レンタルしたfilm_idをセルフジョインして、film_id間の信頼度・支持度・リフトを算出しています。

select
  t1.film_id as a
  , t2.film_id as b
  , count(*) / sum(count(*)) over (partition by t1.film_id) as confidence
  , count(*) / sum(count(*)) over () as support
  , (count(*) / sum(count(*)) over (partition by t1.film_id)) / ((sum(count(*)) over (partition by t2.film_id)) / (sum(count(*)) over())) as lift
from
  (
    select
      r.customer_id
      , i.film_id
    from
      rental as r
    inner join
      inventory as i
    on
      r.inventory_id = i.inventory_id
  ) as t1
  inner join
  (
    select
      r.customer_id
      , i.film_id
      , 1 as count
    from
      rental as r
    inner join
      inventory as i
    on
      r.inventory_id = i.inventory_id
  ) as t2
  on
    t1.customer_id = t2.customer_id
where
  t1.film_id <> t2.film_id
group by
  t1.film_id
  , t2.film_id
order by
  t1.film_id
  , t2.film_id
;

まとめ

SQLでは疎かにしがちな基本的な統計値(中央値、最頻値、標準偏差、尖度、歪度など)の計算を丁寧に書き下されているので、様々な場面でデータの大まかな傾向をチェックするために使えそうです。 またRFM分析やバスケット分析など、あるあるケースを例題に説明されており、実利用でも役立つかと思います。

とはいえ、Chapter 5ではデシジョンツリーや時系列分析やなど、最大6ページに及ぶ長大なSQLが並び、さすがにそこまでやろうとするとSQLでは苦しいなあという印象でした。