どうも、hosigakiです！
皆さんは、「7日間移動平均」という言葉を知っていますか？
今日は、定点観測で便利な「7日間移動平均」の意味と、それの算出方法をご紹介します！
目次
7日間移動平均について
7日間移動平均とは？
何らかのKPIを定点観測する際、『7日間移動平均』という手法がよく使われます。
7日間移動平均とは、
過去7日間における1日当たりの平均の日別推移
のことを指します。
どんな時に使うの？
例えば、以下の日別ユーザー登録数を毎日ウォッチするとします。
toC向けのサービスの場合、土日の登録者数が多くなる傾向があるため、このようにギザギザなグラフになります。
いきなりですが、このグラフから、ユーザー登録者数は伸びていると言えそうでしょうか...？
これだとギザギザ過ぎて判断しづらいですよね。
そこで、7日間移動平均の出番です！
7日間移動平均の出し方は、以下になります。
1/7 → 過去7日間(1/1-1/7)の登録者数の一日平均 1/8 → 過去7日間(1/2-1/8)の登録者数の一日平均 1/9 → 過去7日間(1/3-1/9)の登録者数の一日平均 ... 1/31 → 過去7日間(1/25-1/31)の登録者数の一日平均
これにより、どの日も平均でならされるため、以下のようになめらかなグラフになります。
これを見ると、ユーザー数が少しずつ伸びてきていると言えそうですね！
クエリで7日間移動平均を算出してみよう！
クエリの書き方
さて、ここからが今日の本題です。
今回は、registrationsテーブルから、日別登録者の7日間移動平均をクエリで算出してみます。
先に抽出するためのクエリからお伝えすると、以下のようになります。
SELECT
days,
users,
AVG(users) OVER(ORDER BY days ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS users_7d_avg
FROM
registrations
ORDER BY
days ASC7日間移動平均の算出は、SELECT文の3行目、以下のクエリで行われています。
AVG(users) OVER(ORDER BY days ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS users_7d_avg何やら見慣れない構文が出てきましたね...。
これは「ウィンドウ関数」と呼ばれるものです！
ウィンドウ関数の解説
ウィンドウ関数とは、指定された範囲内での計算を行う関数のことです。
指定された範囲の事を「WINDOW（窓）」と呼ぶことから、このような名前がついています。
ウィンドウ関数を使うことで、
①テーブル内のレコードを任意の順に並べ、 ②各行ごとに範囲を指定し、 ③それぞれの範囲ごとに分析を行う
という操作ができます。
クエリの解説
さて、今回算出するのは、7日間移動平均でしたね。
先ほどのウィンドウ関数の①②③に、今回の流れを当てはめると、
①日付テーブル内のレコードを日付順に並べ、 ②各行ごとに、その行から過去7日分の行までを範囲として、 ③それぞれの範囲内の平均を計算する
という流れになります。
以下、先ほどの7日間移動平均を算出するクエリになります。
AVG(users) OVER(ORDER BY days ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS users_7d_avgこのクエリでは、
①ORDER BY days ASC でレコードを日付順に並べ、
②ROWS BETWEEN 6 PRECEDING AND CURRENT ROWで各行から過去7日分を範囲とし、
③AVG(users)でそれぞれの範囲内の平均を算出
という操作を行っています。
なお、ウィンドウ関数の構文では、
集約関数の後にOVER()を書き、その中に①と②を順に記載する必要がある
という点に注意しましょう！（集約関数についてはこちらで解説してます！）
クエリの解説（発展編）
今回は、以下のような抽出結果を理想としていました。
しかし実は、先ほどの以下のクエリですが、
AVG(users) OVER(ORDER BY days ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS users_7d_avgこちらを実行すると、以下のような抽出結果となってしまうんです...。
実際の抽出結果の1行目から6行目に注目すると、空白にしたかったのに、なぜか値が入っていますね。
これは、過去7日分のレコードがない場合でも、以下のように平均の算出が行われてしまっているからなんです。
1/1 → 過去1日間(1/1-1/1)の登録者数の一日平均 1/2 → 過去2日間(1/1-1/2)の登録者数の一日平均 1/3 → 過去3日間(1/1-1/3)の登録者数の一日平均 1/4 → 過去4日間(1/1-1/4)の登録者数の一日平均 1/5 → 過去5日間(1/1-1/5)の登録者数の一日平均 1/6 → 過去6日間(1/1-1/6)の登録者数の一日平均 ...（1/7以降は、過去7日間の登録者数の一日平均）
このような意図しない算出を防ぐ方法として有効なのが、CASE文です！
（CASE文については、こちらで詳しく解説しています。）
CASE
WHEN COUNT(users) OVER(ORDER BY days ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) <> 7 THEN NULL
ELSE AVG(users) OVER(ORDER BY days ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
END AS users_7avg上のクエリのように、CASE文を用いて
過去7日分のusersカラムの値が計7個ではない時 → 空白にする それ以外の時 → 7日間移動平均を算出
という条件分岐を行うことで、上記のような理想の抽出結果となります！
以上、7日間移動平均とクエリでの書き方でした！
ここまで読んでいただきありがとうございます！
また次の記事でお会いしましょう(^^)/