単純な移動平均による季節調整法の練習 with PostgreSQL

目的

1. 移動平均とはなにか知る
2. なぜ移動平均が有用なのか知る
3. 移動平均をSQLで算出する

移動平均の概要

例: 売上は半分になったのに事業は成長している

下記の例を使います。

日付	曜日	売上
1月1日	月	1万円
1月2日	火	2万円
1月3日	水	3万円
1月4日	木	4万円
1月5日	金	5万円
1月6日	土	6万円
1月7日	日	7万円

日付	曜日	売上
1月8日	月	2万円
1月9日	火	3万円
1月10日	水	4万円
1月11日	木	5万円
1月12日	金	6万円
1月13日	土	7万円
1月14日	日	8万円

日付	曜日	売上
1月15日	月	3万円
1月16日	火	4万円
1月17日	水	5万円
1月18日	木	6万円
1月19日	金	7万円
1月20日	土	8万円
1月21日	日	9万円

日付	曜日	売上
1月22日	月	4万円
1月23日	火	5万円
1月24日	水	6万円
1月25日	木	7万円
1月26日	金	8万円
1月27日	土	9万円
1月28日	日	10万円
1月29日	月	5万円

さて今日は1月29日、売上が5万円だったとします。

これだけを見て「前日比で売上が半分になってしまった！この事業は危機的状況である」と言えるでしょうか。

• 月曜日火曜日は売上が少なくて、土日は売上が大きい （曜日要因）
• 1週間毎に売上のベースが少しずつ伸びている （成長トレンド）

この2点に気付くことが出来れば、1月29日の売上が5万円であることは正常値である、むしろ売上は半減しているのに、 事業は成長トレンドに乗っている と判断できます。
この「曜日要因」「成長トレンド」の2つのパラメータによって日々の売上が成り立っています。

このような非常にわかりやすい例であれば、わざわざ説明や計算をしなくても直感で理解することができます。
ここで移動平均を使うことで、事業が成長していることを定量的に、シンプルに説明することができます。

1週間の移動平均

日付	曜日	売上
1月1日	月	1万円
1月2日	火	2万円
1月3日	水	3万円
1月4日	木	4万円
1月5日	金	5万円
1月6日	土	6万円
1月7日	日	7万円

この1月第1週の7日間の売上の平均値は「4万円」です。

月曜日は平均から−3万円、火曜日は−2万円…日曜日は、平均＋3万円になっています。

日付	曜日	売上	平均値	平均値からの差異
1月1日	月	1万円	4万円	−3万円
1月2日	火	2万円	4万円	−2万円
1月3日	水	3万円	4万円	−1万円
1月4日	木	4万円	4万円	±0
1月5日	金	5万円	4万円	＋1万円
1月6日	土	6万円	4万円	＋2万円
1月7日	日	7万円	4万円	＋3万円

同様に、第2週についても見てみます。

日付	曜日	売上	平均値	平均値からの差異
1月8日	月	2万円	5万円	−3万円
1月9日	火	3万円	5万円	−2万円
1月10日	水	4万円	5万円	−1万円
1月11日	木	5万円	5万円	±0
1月12日	金	6万円	5万円	＋1万円
1月13日	土	7万円	5万円	＋2万円
1月14日	日	8万円	5万円	＋3万円

「平均値」が1万円伸び、「平均値からの差異」は一定です。

• 平均値が 成長トレンド
• 平均値からの差異が 曜日要因

と言えます。

このお話を一般化するために、 ある日を中心とした前後7日間の売上の平均値 を求めてみましょう。
これが 1週間の移動平均値 となります。

日付	曜日	売上	移動平均値
1月1日	月	1万円
1月2日	火	2万円
1月3日	水	3万円
1月4日	木	4万円	4万円
1月5日	金	5万円	4.14万円
1月6日	土	6万円	4.29万円
1月7日	日	7万円	4.43万円
1月8日	月	2万円	4.57万円
1月9日	火	3万円	4.71万円
1月10日	水	4万円	4.86万円
1月11日	木	5万円	5万円
1月12日	金	6万円
1月13日	土	7万円
1月14日	日	8万円

(前後が7日ない日はとりあえず除外しています)

1週間おきに、売上は曜日要因に沿って増えたり減ったりしていますが、 移動平均値は常に増加している ことがわかります。

同様にして、1月29日付近の移動平均値を求めてみます。

日付	曜日	売上	移動平均値
1月22日	月	4万円	6.57万円
1月23日	火	5万円	6.71万円
1月24日	水	6万円	6.86万円
1月25日	木	7万円	7万円
1月26日	金	8万円	7.14万円
1月27日	土	9万円	7.29万円
1月28日	日	10万円	7.43万円
1月29日	月	5万円	7.57万円

つまり1月29日に「売上が前日に半分になった」のではなく

• 曜日要因は一定であり、月曜日の売上が平均から3万円くらい低いのはいつもどおり
• 成長トレンドが7→8万円に向けて伸びており、 売上は継続して成長している

という見方ができます。

移動平均を利用することで「曜日要因を排除して、成長トレンドのみを抽出する」ことができます。(今回はきわめて単純な例を用いたので成長トレンドが綺麗に出ていますが、実際にはもう少し色々な要素も加味されます)

今回は7日間の移動平均を用いましたが、これを1ヶ月や四半期、1年間など適当に期間を広げることもできます。
このように移動平均を用いることで、曜日などの季節要因を除外してトレンドを抽出する方法を、 季節調整法 と言います。

当然ですが、曜日に依る変動を加味したい場合は移動平均値ではなく通常の原数値を確認します。

季節調整法の証明

移動平均で季節要因を除外できることを、数学的に証明してみます。

• 観測値 Observed: $O(t)$
• トレンド Trend: $T(t)$
• 季節要因 Seasonal: $S(t)$

$O(t) = T(t) + S(t)$ であり
ある $n$ が存在し、任意の $k$ に対して $\sum_{i=k+1}^{k+n} S(i)=0$ が成り立つ・・・①
とします。
（たとえば、先程の例だと平均値からの差異を連続7日分合計すると必ずゼロになるので、 $n$ は7日間となります）

このとき

$t = k+1$ を起点とした $O(t)$ の $n$ 期間の移動平均 $Y(k)$ について

\begin{align}
Y(k) &= \frac{\sum_{i=k+1}^{k+n}O(i)}{n}\\
&= \frac{\sum_{i=k+1}^{k+n}(T(i)+S(i))}{n}\\
&= \frac{\sum_{i=k+1}^{k+n}T(i)}{n} + \frac{\sum_{i=k+1}^{k+n}S(i)}{n}\\
&= \frac{\sum_{i=k+1}^{k+n}T(i)}{n}
\end{align}

となり、「①が正しいとき、 $O(t)$ の移動平均値 $Y(t)$ は、成長トレンドの平均値と同値となる」が得られました。
実際には $T(t)$ は「成長トレンドを含む幾つかの要素の複合値」なのですが、重要なのは 季節要因を除外している ことです。

つまり①が正しくなるよう適切な $n$ （1週間、1ヶ月、1年など）を設定すると、その移動平均値$Y(t)$は季節要因を除外した値になります。

PostgreSQLで移動平均

最後に、下記を参考に今回の例をSQLで算出してみます。
[SQL] 移動平均を算出 〜Window関数を使って変動傾向を掴む〜 ｜ Developers.IO

(本当はMySQLでやりたかったのですがあまり良いクエリが書けませんでした...)

準備

まず、サンプルとして今回のデータをCSVで用意します。

/tmp/sample_data.csv

"2016-01-01","月","1"
"2016-01-02","火","2"
"2016-01-03","水","3"
"2016-01-04","木","4"
"2016-01-05","金","5"
"2016-01-06","土","6"
"2016-01-07","日","7"
"2016-01-08","月","2"
"2016-01-09","火","3"
"2016-01-10","水","4"
"2016-01-11","木","5"
"2016-01-12","金","6"
"2016-01-13","土","7"
"2016-01-14","日","8"
"2016-01-15","月","3"
"2016-01-16","火","4"
"2016-01-17","水","5"
"2016-01-18","木","6"
"2016-01-19","金","7"
"2016-01-20","土","8"
"2016-01-21","日","9"
"2016-01-22","月","4"
"2016-01-23","火","5"
"2016-01-24","水","6"
"2016-01-25","木","7"
"2016-01-26","金","8"
"2016-01-27","土","9"
"2016-01-28","日","10"
"2016-01-29","月","5"

テーブルを定義し、CSVからデータを取り込みます。

CREATE TABLE sales(
  id       SERIAL PRIMARY KEY,
  date     DATE,
  day      VARCHAR(20),
  observed INT
);

COPY sales (date, day, observed) FROM '/tmp/sample_data.csv' WITH CSV;

postgres=# SELECT * FROM sales;
 id |    date    | day | observed 
----+------------+-----+----------
  1 | 2016-01-01 | 月  |        1
  2 | 2016-01-02 | 火  |        2
  3 | 2016-01-03 | 水  |        3
  4 | 2016-01-04 | 木  |        4
  5 | 2016-01-05 | 金  |        5
  6 | 2016-01-06 | 土  |        6
  7 | 2016-01-07 | 日  |        7
  8 | 2016-01-08 | 月  |        2
  9 | 2016-01-09 | 火  |        3
 10 | 2016-01-10 | 水  |        4
 11 | 2016-01-11 | 木  |        5
 12 | 2016-01-12 | 金  |        6
 13 | 2016-01-13 | 土  |        7
 14 | 2016-01-14 | 日  |        8
 15 | 2016-01-15 | 月  |        3
 16 | 2016-01-16 | 火  |        4
 17 | 2016-01-17 | 水  |        5
 18 | 2016-01-18 | 木  |        6
 19 | 2016-01-19 | 金  |        7
 20 | 2016-01-20 | 土  |        8
 21 | 2016-01-21 | 日  |        9
 22 | 2016-01-22 | 月  |        4
 23 | 2016-01-23 | 火  |        5
 24 | 2016-01-24 | 水  |        6
 25 | 2016-01-25 | 木  |        7
 26 | 2016-01-26 | 金  |        8
 27 | 2016-01-27 | 土  |        9
 28 | 2016-01-28 | 日  |       10
 29 | 2016-01-29 | 月  |        5

PostgreSQLで移動平均値を算出

SELECT
  date,
  day,
  observed,
  AVG(observed) OVER (
    ORDER BY date ASC
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS moving_average
FROM sales
ORDER BY date ASC;

    date    | day | observed |   moving_average   
------------+-----+----------+--------------------
 2016-01-01 | 月  |        1 | 2.5000000000000000
 2016-01-02 | 火  |        2 | 3.0000000000000000
 2016-01-03 | 水  |        3 | 3.5000000000000000
 2016-01-04 | 木  |        4 | 4.0000000000000000
 2016-01-05 | 金  |        5 | 4.1428571428571429
 2016-01-06 | 土  |        6 | 4.2857142857142857
 2016-01-07 | 日  |        7 | 4.4285714285714286
 2016-01-08 | 月  |        2 | 4.5714285714285714
 2016-01-09 | 火  |        3 | 4.7142857142857143
 2016-01-10 | 水  |        4 | 4.8571428571428571
 2016-01-11 | 木  |        5 | 5.0000000000000000
 2016-01-12 | 金  |        6 | 5.1428571428571429
 2016-01-13 | 土  |        7 | 5.2857142857142857
 2016-01-14 | 日  |        8 | 5.4285714285714286
 2016-01-15 | 月  |        3 | 5.5714285714285714
 2016-01-16 | 火  |        4 | 5.7142857142857143
 2016-01-17 | 水  |        5 | 5.8571428571428571
 2016-01-18 | 木  |        6 | 6.0000000000000000
 2016-01-19 | 金  |        7 | 6.1428571428571429
 2016-01-20 | 土  |        8 | 6.2857142857142857
 2016-01-21 | 日  |        9 | 6.4285714285714286
 2016-01-22 | 月  |        4 | 6.5714285714285714
 2016-01-23 | 火  |        5 | 6.7142857142857143
 2016-01-24 | 水  |        6 | 6.8571428571428571
 2016-01-25 | 木  |        7 | 7.0000000000000000
 2016-01-26 | 金  |        8 | 7.1428571428571429
 2016-01-27 | 土  |        9 | 7.5000000000000000
 2016-01-28 | 日  |       10 | 7.8000000000000000
 2016-01-29 | 月  |        5 | 8.0000000000000000

参考文献

• Rによる時系列分析入門 | 田中 孝文 | 本 | Amazon.co.jp