CodeZine(コードジン)

特集ページ一覧

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

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2017/07/19 14:00
目次

時系列分析

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

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

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日間移動平均の時系列データ

 このような結果の場合、日次のデータ推移についてはやや変化が大きすぎて傾向がつかみづらいですが、移動平均の値は一定期間(ここでは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分析に挑戦する予定です。



  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:ビジネスデータ解析のためのSQL入門

著者プロフィール

  • WINGSプロジェクト 西 潤史郎(ニシ ジュンシロウ)

    <WINGSプロジェクトについて> 有限会社 WINGSプロジェクトが運営する、テクニカル執筆コミュニティ(代表 山田祥寛)。主にWeb開発分野の書籍/記事執筆、翻訳、講演等を幅広く手がける。2018年11月時点での登録メンバは55名で、現在も執筆メンバを募集中。興味のある方は、どしどし応募頂きた...

  • 山田 祥寛(ヤマダ ヨシヒロ)

    静岡県榛原町生まれ。一橋大学経済学部卒業後、NECにてシステム企画業務に携わるが、2003年4月に念願かなってフリーライターに転身。Microsoft MVP for ASP/ASP.NET。執筆コミュニティ「WINGSプロジェクト」代表。 主な著書に「入門シリーズ(サーバサイドAjax/XM...

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5