皆様こんにちは、開発部の竹上です。

業務内で簡単な分析をする必要があり「R」で行おうとしたら、思った以上に難しく
挫折しそうになったので、SQLで出来ないか模索してみました。

今回は、実験として簡単なテストデータから、自分なりに分析した結果をまとめてましたので
SQLでDB分析したいと思っている方は参考にご覧ください。

今回ご紹介するのは、主に方法論であって、どこに活用するかはまたの機会に。

改めて分析がDBで出来たら何が便利か？
・使い慣れている（習得コストが低いです。）
・SQLで全て解決（当たり前ですが重要）

じゃあ不便なところは？
個人的には、グラフ出力ができない。
これにつきます。

と便利不便は置いておいて、さっそくやってみましょう。

環境はPostgres 9.4を利用します。
※これからやることはPostgres 9.0以降でないと動かないのでご注意ください。

では必要なデータを準備します。
まずはテーブル定義です。

CREATE TABLE ad_report(
    date DATE,
    impression INTEGER,
    click INTEGER,
    conversion INTEGER,
    PRIMARY KEY(date)
);

ADテクらしく演習用データも広告レポートを利用し、テーブル定義はシンプルですが簡易的にご説明します。

date：実行日を格納
impression：広告の表示回数
click：広告のクリック数
conversion：何かしらのアクションを起こした数

以下のデータを流します。

INSERT INTO ad_report(date, impression, click, conversion) VALUES ('2015/03/01',15425,77,1), ('2015/03/02',513531,5135,26), ('2015/03/03',531518,6910,21), ('2015/03/04',412412,2062,19), ('2015/03/05',352262,3170,16), ('2015/03/06',751531,9018,26), ('2015/03/07',85913,773,4), ('2015/03/08',19510,78,1), ('2015/03/09',651351,12376,111), ('2015/03/10',815151,16303,83), ('2015/03/11',421341,3792,7), ('2015/03/12',395135,3161,26), ('2015/03/13',215151,2582,9), ('2015/03/14',95151,285,1), ('2015/03/15',105135,946,6), ('2015/03/16',531531,1063,2), ('2015/03/17',481591,6742,33), ('2015/03/18',901513,9015,86), ('2015/03/19',851513,7664,40), ('2015/03/20',513572,5649,20), ('2015/03/21',75151,676,3), ('2015/03/22',21515,22,0), ('2015/03/23',515315,5050,36), ('2015/03/24',718151,5458,45), ('2015/03/25',901414,13521,129), ('2015/03/26',715191,15019,150), ('2015/03/27',215135,1936,19), ('2015/03/28',51510,52,5), ('2015/03/29',51531,129,1), ('2015/03/30',515315,11337,81), ('2015/03/31',1153151,26522,236);

これで、DBの準備は完了です。

Rで行う場合には、以下のようなcsvファイルを準備します。
ファイル名はad_report.csvで保存します。

date, impression, click, conversion
2015/03/01,15425,77,1
・
・
省略
・
・
2015/03/31,1153151,26522,236

CSVのロード

ad_report <- read.csv("ad_report.csv")

DBとRそれぞれの準備ができました。

▼合計、平均、最小値、最大値

今回はimpressionの値で利用してみましょう。
まずはSQLでは

SELECT
    SUM(impression),
    AVG(impression),
    MIN(impression),
    MAX(impression)
FROM
    ad_report;

Rでは

data.frame(sum=sum(ad_report$impression),
avg=mean(ad_report$impression),
max=max(ad_report$impression),
min=min(ad_report$impression))

DBと同じように出すには、Rでは一工夫必要でした。
少しここから細かな分析の実装に行きましょう。

▼単純移動平均

「単純移動平均とは」
たとえば5日分で単純移動平均を実行する場合は

03/31の場合は03/31,03/30,03/29,03/28,03/27の平均値
03/30の場合は03/30,03/29,03/28,03/27,03/26の平均値
03/29の場合は03/29,03/28,03/27,03/26,03/25の平均値
・
・

という具合に、計算する手法です。
傾向を見るのに便利だったりします。

前回同様impressionにて5日分の移動平均を算出し、
また、基本的な考えでは(おそらく)、過去に向かって平均を出していくので、
日付の降順にてソートを行います。

まずはSQLでは

SELECT
    date,
    AVG(impression) OVER W1 AS average
FROM
    ad_report
WINDOW
    W1 AS (ORDER BY date DESC ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING)
ORDER BY date DESC;

WINDOW関数を利用することで移動平均を求めることが可能になります。
Rでは以下になります。

filter(ad_report$impression, rep(1,5)) / 5

計算は合いますが、あまり自信がないので他に良い方法あれば教えてほしいと思います。

RとDBで異なる点は、Rでは移動平均が足りない日、今回で言えば3/1日のデータは、無効となるがDBでは、3/1日だけの平均が利用され計算されますのでご注意ください。

▼相関係数

よく目にする相関係数です。
呼び出し方としては単純です。
clickとimpressionの相関関係を見てみましょう。

SQLでは

SELECT CORR(click, impression) FROM ad_report;

Rでは以下になります。

cor(ad_report$impression, ad_report$click, method="pearson")

両方ともシンプルです。
Rの場合は相関係数の求め方に種類を選べるのですが、DBと合うようにすると「pearson」を利用する必要があるみたいです。

また引数の第一と第二でそれぞれx軸,y軸が異なります。
SQLのCORRはy軸,x軸。
Rのcorはx軸,y軸の順番になります。

▼ 最小二乗法

最小二乗法とはExcelでいう近似直線を描くy=ax+bの方程式を算出します。
aの傾き、bの切片が簡単な計算で求められます。

先程の相関係数に基づいた近似直線の方程式を求めましょう。

SQL

SELECT
    regr_slope(click, impression) AS a,
    regr_intercept(click, impression) AS b
FROM ad_report;

R

regr<-lsfit(ad_report$impression, ad_report$click)
regr$coefficients

R上では、

Intercept X<br>-1.622729e+03 1.668633e-02

このように結果が出ます。
Interceptがb(切片),Xがa(傾き)になります。

これを応用すればy=e^(ax+b)などの非線形でも対応ができます。

▼ 加重移動平均(WMA)

移動平均に重みをつけて計算する手法です。
重みは、時系列で並んでいるデータが新しいデータほど重要度が高く、古いデータほど重要度が低くするのが良く利用される手法です。

今回はよく利用される新しい日付の方が重みが大きく古い順ほど重みが少ないと仮定して考えましょう。
単純移動平均と同様に5日分のimpression数で計算すると

03/31の場合は(03/31のimpression * 5 + 03/30のimpression * 4 + 03/29のimpression * 3 + 03/28のimpression * 2,03/27のimpression * 1) / (5!)<br>03/30の場合は(03/30のimpression * 5 + 03/29のimpression * 4 + 03/28のimpression * 3 + 03/27のimpression * 2,03/26のimpression * 1) / (5!)<br>※5!(5の階乗)=5*4*3*2*1

これをSQLにすると

SELECT
    date,
    ( nth_value(impression, 1) OVER W1 * 5
    + nth_value(impression, 2) OVER W1 * 4
    + nth_value(impression, 3) OVER W1 * 3
    + nth_value(impression, 4) OVER W1 * 2
    + nth_value(impression, 5) OVER W1 * 1)::NUMERIC
    / (5 + 4 + 3 + 2 + 1)::NUMERIC
FROM
    ad_report
WINDOW
    W1 AS (ORDER BY date DESC ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING)
ORDER BY date DESC;

少し複雑ですが求められました。
キャストしないと、小数点以下がなくなるのでキャストを忘れないようにしましょう。

計算内で使われているnth_valueは、WINDOWでまとめた対象のn番目を取得することができるので、これを利用した形になります。

今回は5日分を利用していますが、10日分とかになるとその分記述する必要があるので
何とかしたいですが、良い方法が思い浮かばず断念しました。

WINDOW関数を使い内部的に計算式を記述していく感じなのでスマートではないのですが
通常のSQLで組むよりだいぶ楽な気がします。
Rは分析手段なので、DBできる方法を模索すれば、エンジニアが行う分析も面白くなるのではないかと思います。

ちなみにテストは、Excelで同様の計算を行い確認しました。
小さなデータの場合はExcelが楽でした(笑)

Rでも加重移動平均を出そうと思いましたが、プログラムする必要がありそうだったので諦めました。という訳で今回は、SQLの方にプチ軍配！(私が知らないだけですが）

以上、分析の触り程度ですが、今回はここまでとなります。