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

# 目的

1. 移動平均とはなにか知る
1. なぜ移動平均が有用なのか知る
1. 移動平均を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)$ について

```math
\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](http://dev.classmethod.jp/etc/sql-moving-average/)

(本当は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からデータを取り込みます。

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

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

```psql
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で移動平均値を算出

```psql
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](https://www.amazon.co.jp/dp/4916092910)