概要
前回，前々回の記事でBigQueryで扱えるいくつかの関数を紹介しましたので，今回はそれらの関数を使って実際に時系列分析を行います。
また，今回はそれに加えてGoogle データポータルで分析結果を可視化してみようと思います。
以前の記事はこちら：
【BigQuery】LAG関数，LEAD関数の使い方
【BigQuery】FIRST_VALUE関数，LAST_VALUE関数の使い方とその違い
今回のやること一覧
1.時系列分析：LAG関数，LEAD関数，Window Frame句を使って移動平均を算出
2.データポータルで可視化してみる
使用テーブル：number_of_people_monthly
| date | day | number |
|---|---|---|
| 2019-04-01 | Monday | 200 |
| 2019-04-02 | Tuesday | 10000 |
| 2019-04-03 | Wednesday | 2000 |
| 2019-04-04 | Thursday | 4000 |
| 2019-04-05 | Friday | 500 |
| 2019-04-06 | Saturday | 600 |
| ........................ | ........................ | ........................ |
| 2019-04-25 | Thursday | 630 |
| 2019-04-26 | Friday | 338 |
| 2019-04-27 | Saturday | 924 |
| 2019-04-28 | Sunday | 914 |
| 2019-04-29 | Monday | 546 |
| 2019-04-30 | Tuesday | 324 |
1.時系列分析：LAG関数，LEAD関数，Window Frame句を使って移動平均を算出
時系列データを可視化する際に，単純にグラフ化しただけでは，いくつかのデータの大きな変動によってグラフの形がゆがみ，全体の傾向が掴みにくくなってしまうことがあります。
そんな時には移動平均を用いると，特定のデータの影響を弱めた，滑らかで全体の傾向が捉えやすいグラフが作成できます。
移動平均は，以下の計算式で算出します。iが分析対象となるデータの番号だとすると...
※t = 2n+1（奇数の場合 今回は偶数は割愛しますが，どうなるか考えてみて下さい）
Y(t) = \frac{\sum_{k=i-(t-1)/2}^{i+(t-1)/2}a_k}{t}
こんな感じ。
3区間の移動平均であれば，
Y(3) = \frac{\sum_{k=i-1}^{i+1}a_k}{3}
（「対象の1つ前のデータ」+「対象のデータ」+「対象の1つ後のデータ」）/3という感じです。
「1つ前のデータ」と「1つ後のデータ」はLAG関数，LEAD関数を用いて簡単に出すことができましたね。
それでは，実際にクエリを書いてみましょう。まずは前述の3区間の移動平均を算出してみます。
SELECT
t1.*,
--3つのカラムの合計値を3でわり，3区間の移動平均を算出（小数点は2位まで表示）
ROUND(((yesterday + number + tomorrow) * 1.0 / 3), 2) AS threedays_average
FROM
(
SELECT
date,
day,
--LAG関数，LEAD関数で前日，翌日のデータを取得，numberの両隣に作成
LAG (number) OVER (ORDER BY date) AS yesterday,
number,
LEAD (number) OVER (ORDER BY date) AS tomorrow
FROM
`qiita.number_of_people_monthly`
) AS t1;
実行結果は…
こんな感じです。
「threedays_average」が3区間の移動平均です。カラム「date」には，2019-04-01の前日と2019-04-30の翌日のデータが無いので，その2行の「threedays_average」はnullになっていますが，それ以外の行では3日分の平均が算出されているのが分かります。
次は，7区間である7日間の移動平均も算出してみましょう。必要なデータは「3日前～当日～3日後」の7日分ですが，これはLAG関数，LEAD関数で出すと少し面倒ですね。
今度は，Window Frame句を使いましょう。Window Frame句を使えば，処理する行の範囲が指定できますので，ここを3行前から3行後まで，に指定します。
実際にクエリを書いてみます。
SELECT
t1.*,
ROUND (((yesterday + number + tomorrow) * 1.0 / 3), 2) AS threedays_average,
--CASE文を使って，前後併せて7日分のデータが取れる行にだけ移動平均を出す様に指定
--指定しないと初日や末日はその日も含めた4日分のデータだけを対象にその後の計算を実行してしまうので注意
--Window Frame句で前後の3行までの範囲で処理をする様に指定
CASE WHEN COUNT (*) OVER (ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) = 7
THEN ROUND(SUM (number) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
*1.0/ 7, 2)
--初日や末日など，正しく出せない日にはnullを
ELSE NULL END AS sevendays_average
FROM
(
SELECT
date,
day,
LAG (number) OVER (ORDER BY date) AS yesterday,
number,
LEAD (number) OVER (ORDER BY date) AS tomorrow
FROM
`qiita.number_of_people_monthly`
) AS t1;
実行結果は...
うまくいっていますね。
「sevendays_average」には7区間の移動平均が，算出できる対象の行にのみ入っていて，それ以外はnullになっています。
2.データポータルで可視化してみる
いくつかの手法で移動平均を算出してみましたが，やっぱり実際にグラフを見てみないとよくわかりませんね。
というわけで，BigQueryでクエリ結果を出したらそのままGoogle データポータル（旧Google Data Studio）を開いてグラフを作成してみましょう。
下の画像の「データポータルで調べる」をクリックすると別タブでGoogle データポータルが起動します。
時系列グラフを選択，指標には「number」，「threedays_average」，「sevendays_average」を指定，期間のディメンションには「date」を指定して…
今回のグラフは以下の様になります。
縦軸が人数（人），横軸が「date」の日付になっています。
それぞれ黄がnumberの単純なグラフ，緑が3区間の移動平均，青が7区間の移動平均です。
緑，青のグラフを見ると，移動平均を指標に用いたことで元のグラフの大きな変動が均されて，滑らかなグラフになっているのが分かります。
また，黄と青を見比べると，元のデータでは一定周期の増減が繰り返されていますが，7区間の移動平均を見れば，結局全体としては大きな変動なし，ということがすぐに分かりますね。
まとめ
今回，活用した移動平均は，選択した区間によって，グラフの形が大きく変わりますので，データから何を捉えたいのかによって臨機応変に使えるようにするといいですね。
また，色々と集計，加工をしても，可視化できないとやっぱり分かりづらいです。
Google データポータルは，BigQueryの結果からボタン一つ押すだけですぐに開くことが出来るので，とても簡単でおすすめです。
参考サイト，文献
Rによる時系列分析入門
https://support.google.com/datastudio/answer/6283323?hl=ja
https://support.google.com/datastudio/answer/7398001?hl=ja