BigQueryレシピ

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

やりかた

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

ordersテーブル

order_idorder_dateuser_idamount
12021-04-0186414900
22021-04-0129919100
32021-04-019339800
42021-04-013836500
52021-04-012264300
62021-04-0185117300
72021-04-0168222400
82021-04-012211800
92021-04-0189915200
102021-04-0115117500
112021-04-027181400
122021-04-0224126900

クエリ

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,
  -- 直近7日間の平均
  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_datetotal_amountseven_day_avg_strict
2021-04-01128800
2021-04-02157800
2021-04-03109100
2021-04-04203100
2021-04-05234100
2021-04-06253500
2021-04-07233600188,571.4
2021-04-0862200179,057.1
2021-04-09154400178,571.4
2021-04-10146500183,914.3
2021-04-11111900170,885.7
2021-04-12209900167,428.6

解説

以下の手順で取得しています。

  1. 日別に売上を集計
  2. 直近7日間の売上平均を集計
  3. 7日分のデータが取得できない最初の6日間のデータは集計から除外

ポイントはWINDOW関数

WINDOW関数とは、部分的に切り出した範囲で集約関数を適用できるもので、その範囲を窓に例えられることからWINDOW関数と呼ばれています(たぶん)。分析関数とも呼ばれています。

今回のクエリでは以下の部分が該当します。

AVG(SUM(amount)) OVER (ORDER BY order_date ROWSBETWEEN 6 PRECEDING AND CURRENT

直近7日間の売上平均を集計するために、日付の昇順にソートして現在行から6行前の間の平均を求めています。

基本形
ROWS BETWEEN [start] AND [end] または
RANGE 〃

[start], [end]
CURRENT ROW(現在行)
n PRECEDING(n行前)
n FOLLOWING(n行後)
UNBOUNDED PRECEDING(前の行全て)
UNBOUNDED FOLLOWING(後の行全て)

関連リンク

より実務的なクエリを身につけるには「ビッグデータ分析・活用のためのSQLレシピ」がおすすめです。

ビッグデータ分析
created by Rinker
¥4,180
(2022/09/26 16:59:15時点 Amazon調べ-詳細)

created by Rinker
技術評論社
¥2,398
(2022/09/26 18:09:01時点 Amazon調べ-詳細)