今回は、日次PV数の７日間移動平均を算出する方法について紹介する。

SQLクエリの概要

集計とともに移動平均を用いることで、曜日などの周期的な変動や、突発的な変動などのバラツキを吸収して、PV数のトレンドを確認することができる。

今回はウィンドウ関数（AVG）を用いて計算していく。

また、PV数の集計方法に関してはこちらの記事で詳しく解説している。

ウィンドウ関数（AVG）

実際にSQLクエリのサンプルを見ていこう。

SELECT
  event_date,
  COUNT(*) AS PV,
  ROUND(AVG(COUNT(*)) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS seven_day_rolling_avg
FROM
`technogram-354804.analytics_291973511.events_intraday_202209*`
WHERE
  event_name = 'page_view'
GROUP BY
  event_date
ORDER BY
  event_date
;

※過去7日分のデータを取得できない最初の6日間は、厳密には7日移動平均ではなく、遡れる分のみで平均しているので注意。

BigQueryでの集計結果

BigQueryでの集計結果は以下のようになった。