redashによる移動平均線の可視化について

こんにちは。 @smdmts です。今回は Redash Advent Calendar 2017 13日目の記事を書かせて頂きます。

移動平均線について

移動平均とは時系列データにおける一定期間における平均値を連続で出すことで、曜日や季節に起因する変動を排除することが可能となります。DELISH KITCHENのアプリ分析でも曜日変動によるユーザ行動を平滑化するために、各KPIの7日移動平均・30日移動平均を重要な指標として利用しています。日経平均などのテクニカル分析では25日線や75日線が利用されているのでご存じの方もいるかと思います。（詳細な移動平均線の解説はwikipediaをご覧下さい。）

そこで今回はredashで移動平均線の算出方法について解説してみたいと思います。

データセットの準備

今回はテストデータとして、気象庁のサイトから東京都の平均気温を利用させていただき、移動平均線算出の仕組みを解説したいと思います。embulkを利用してPostgreSQLにバルクロードしてみましょう。なおデータには一部加工が必要となるため、このembulkスクリプトおよびデータセットはgithubでも公開しています。スクリプトの実装とデータは参照して下さい。

run.sh （Embulk実行スクリプト）

embulk gem install embulk-output-postgresql
embulk run ./config.yml

data.csv（日付,気温のCSVファイル）

2016/12/5,12.7
2016/12/6,10.8
2016/12/7,7.5
2016/12/8,8.6
-- 以下続く

データ取り出し用SQL

redashで以下のSQLを発行してみましょう。Window関数を利用して移動平均値を算出します。なお今回はPostgreSQLを対象としていますが、BigQuery/Redshift/Presto/Hiveのようなビッグデータ系のSQLエンジンでも同様の集計処理を書くことが可能です。

SELECT
  date, -- 日付
  temperature, -- 気温
  -- 現在行から後方7レコードを取得するwindow関数を利用して加算平均を算出する
  avg(sum(temperature))
  OVER (
    ORDER BY date_trunc('day', date) ASC
    ROWS BETWEEN 7 PRECEDING AND CURRENT ROW ) 
  AS moving_average_7_day
FROM temperature
GROUP BY 1,2
ORDER BY date

上記SQLを実行すると、X軸（日付）、Y軸（気温と移動平均）となる3個のデータが抽出されます。

redashの設定

それでは、データを取り出した後に、「NEW VISUALIZATION」で描画してみます。

X軸とY軸の設定

データを取り出した後は、SQLで取り出したカラムのサジェストが効くようになるので、以下のようにX軸（date（日付）)、 Y軸（tempreture（気温）、moving_average_7day（7日移動平均)）を追加します。

redashにより描画されたグラフ

以下のように気温（青）、移動平均（赤）の線グラフが描画されます。移動平均線（赤）は平滑化されて滑らかな線となったことが見て取れるかと思います。可視化された気象データから、東京は8月末が一番暑く1月末が一番寒いと分かるのが面白いですね。（個人的な感覚では、7月が一番暑く、2月が一番寒いと思っていたのですが、直感と反する結果となりました。）