SQLでバスケット分析と時系列分析を用いたデータ解析に挑戦する

ビジネスデータ解析のためのSQL入門 第2回

時系列分析

　時系列分析とは、時系列データを対象とした分析です。時系列データとは、売り上げや気温、アクティブユーザー数など、時間とともに値が変化するデータの集まりです。分析の現場ではこれら時系列の変化を捉える必要があります。

　例えば売り上げの変化の傾向から今後の売上推移を予測する、といった未来予測などに活用されます。

SQLサンプル／サンプル解説

　ここでも購買履歴のテーブルを使います。購買履歴で日付ごとに購買額を合計し、それに7日間の移動平均の算出値を付加して表示します。移動平均とは時系列データのある一定期間ごとの平均値を期間をずらしながら算出したものです。

　得たい結果のテーブルは以下の通りになります。

    date    | amount_price |    moving_average
------------+--------------+----------------------
 2017-03-29 |          500 | 500.0000000000000000
 2017-03-30 |         1100 | 800.0000000000000000
 2017-04-01 |          450 | 683.3333333333333333
 2017-04-02 |          450 | 625.0000000000000000
 2017-04-03 |          980 | 696.0000000000000000
 2017-04-04 |          500 | 663.3333333333333333
 2017-04-05 |          250 | 604.2857142857142857
 2017-04-06 |          250 | 568.5714285714285714
 2017-04-07 |          250 | 447.1428571428571429
 2017-04-08 |          250 | 418.5714285714285714
 2017-04-09 |          250 | 390.0000000000000000
 2017-04-10 |          750 | 357.1428571428571429
 2017-04-12 |          500 | 357.1428571428571429
 2017-04-13 |          500 | 392.8571428571428571
 2017-04-14 |         1760 | 608.5714285714285714

　また、上のテーブルをグラフ化したものを以下に示します。青の棒グラフが売り上げ、赤の折れ線グラフが移動平均を表しています。

日次売上と7日間移動平均の時系列データ

　このような結果の場合、日次のデータ推移についてはやや変化が大きすぎて傾向がつかみづらいですが、移動平均の値は一定期間（ここでは7日間分）で値がならされているため、より長いスパンでの変化の傾向を読み取りやすくなります。

　SQLは、以下の通りになります。

リスト4　時系列分析のSQL（timeseries.sql）

select
    date,
    sum(price) as amount_price,
    avg(sum(price)) over ( /*（1）*/
        order by date /*（1）-（a）*/
        rows between 6 preceding and current row /*（1）-（b）*/
    ) as moving_average
from sales_history
group by date
order by date
;

　ウィンドウ関数やフレーム句が使われています。順を追って確認していきます。

ウィンドウ関数

　上記のようにavg()がover句とともに用いられる場合はウィンドウ関数と呼ばれる関数になります。GROUP BYによる集計がグループをまとめる（行が減る）のに対し、ウィンドウ関数は行はそのままで算出結果の列が増えます。別途集計結果を列に追加できるため、分析で活躍します。PostgreSQLのほかOracle・SQL Serverといった主なDBMSに実装されています。MySQLには残念ながらウィンドウ関数は実装されておらず利用できません。

フレーム句

　フレーム句はrowsキーワードを用いて集計範囲（フレーム）を指定する機能になります。このフレーム句により集計する行の範囲を絞り込むことができます。

　フレーム句の構文は以下です。項目1、項目2で対象とする行を指定します。

rows between <項目1> and <項目2>

　ここで項目1、項目2には以下のキーワードを指定します。

フレーム句のキーワード

フレーム句の項目	意味
current row	現在の行
n preceding	n行前
n following	n行後
unbound preceding	前の行すべて
unbound following	後の行すべて

（1）ウィンドウ関数による移動平均の算出

　over句を用いてウィンドウ関数を使用します。そのため列が付加されたテーブルが得られます。

• （a）日付でソートします。
• （b）フレーム句で現在の行から6行前までの行を指定します。これにより「7日間のデータ」が範囲として絞り込まれ、行ごとに過去7日間の平均値が算出されます。

まとめ

　今回はバスケット分析、時系列分析をSQLで実行する方法を取り上げました。

　前回と同様、サブクエリの結果など途中経過を確認していけば理解しやすいと感じられたのではと思います。

　次回は、RFM分析に挑戦する予定です。