BigQuery | 移動平均を用いて日別の推移を調べる

やりかた

売上が上昇傾向にあるのか下降傾向にあるのか判断しやすくするために、直近７日間の売上を平均した「７日移動平均」を集計しレポートを作成します。

ordersテーブル

order_id	order_date	user_id	amount
1	2021-04-01	864	14900
2	2021-04-01	299	19100
3	2021-04-01	933	9800
4	2021-04-01	383	6500
5	2021-04-01	226	4300
6	2021-04-01	851	17300
7	2021-04-01	682	22400
8	2021-04-01	221	1800
9	2021-04-01	899	15200
10	2021-04-01	151	17500
11	2021-04-02	718	1400
12	2021-04-02	241	26900
…	…	…	…

クエリ

WITH calendar AS (
  -- 無からカレンダー生成
  SELECT
    _date
  FROM
    unnest(
      GENERATE_DATE_ARRAY(
        '2021-04-01',
        CURRENT_DATE('Asia/Tokyo')
      )
    ) _date
)

, aggregate_amount_by_day AS (
  -- 日別の売上サマリ集計
  SELECT
    order_date,
    SUM(amount) total_amount,
  FROM
    `dataset.orders`
  GROUP BY 
    order_date
)

SELECT
  order_date,
  total_amount,
  -- 直近７日間の平均
  CASE
    WHEN 7 = COUNT(*) OVER (ORDER BY c._date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
  THEN
    AVG(total_amount) OVER (ORDER BY c._date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
  END seven_day_avg_strict
FROM
  calendar c INNER JOIN aggregate_amount_by_day a on c._date = a.order_date

結果

order_date	total_amount	seven_day_avg_strict
2021-04-01	128800
2021-04-02	157800
2021-04-03	109100
2021-04-04	203100
2021-04-05	234100
2021-04-06	253500
2021-04-07	233600	188,571.4
2021-04-08	62200	179,057.1
2021-04-09	154400	178,571.4
2021-04-10	146500	183,914.3
2021-04-11	111900	170,885.7
2021-04-12	209900	167,428.6
…	…	…