BigQuery超入門⑤

はじめに

こちらは BigQuery 超入門 ④ の続きです。

学ぶこと

• 「ウィンドウ関数とは」について
• 代表的なウィンドウ関数一覧とそれぞれの処理について

ウィンドウ関数とは

任意のフィールドでグループを作り、グループの元データを保持しながら集計する関数。
グループ化する範囲のことを「パーティション」という。
設定したパーティション内で集計する関数というイメージを持つと分かりやすい。
また「分析関数」とも呼ばれておりデータの分析には欠かせない関数である。

特徴としてはパーティションに含まれる複数のレコードが持つ値を単純に一つの値として集計せず、各レコードの値を保持しながら計算ができる。
これまで GROUP BY を使って集計していた関数を 1 レコードにまとめることなく適用できる。

通常の集計関数との違いを確認してみる。
例えばお店ごとに月の売り上げ金額をもつテーブルがある。

ID	store	amount	month
1	A 店	100	1
2	A 店	200	2
3	B 店	150	1
4	B 店	250	2
5	C 店	300	1

GROUP BY を使った集計関数だとグループ化した時に一つのレコードにまとめられてしまうので合計の売り上げ金額しか分からないので月毎の売り上げとの比較ができない。

SELECT
  store, SUM(amount) AS total_sales
FROM
    project_id.dataset.fsales
GROUP BY
    store

結果テーブル

store	total_sales
A 店	300
B 店	400
C 店	300

次にウィンドウ関数を確認してみる。
パーティションを store にすることで **store でグループ化して store 毎の売り上げも保持したまま合計の売上金額を集計できる。

SELECT
    id,
    store,
    amount,
    SUM(amount) OVER (PARTITION BY store) AS total_sales_per_store
FROM
    project_id.dataset.fsales

ID	store	amount	total_sales_per_store
1	A 店	100	300
2	A 店	200	300
3	B 店	150	400
4	B 店	250	400
5	C 店	300	300

違いを比較すると次のようなことがわかる。

比較項目	通常の集計関数 (GROUP BY)	ウィンドウ関数 (OVER(PARTITION BY))
データの粒度	集約されて 1 行になる	各行ごとに計算される
元のデータの保持	失われる	そのまま保持される
具体的な用途	店舗ごとの売上集計	各取引のデータを残しつつ、店舗ごとの合計も出す

分析に応じて使い分ける

番号付け関数

ランキング順による商品の分析やゴールデン導線分析などで使える。

関数名	処理内容
ROW_NUMBER()	各行に一意の連番を振る（同じ値があっても別の番号）
RANK()	指定範囲内で順位を振る（同じ値は同じ順位、次の順位は飛ぶ）
DENSE_RANK()	RANK() に似ているが、順位を詰めて振る（次の順位は飛ばない）
NTILE(n)	指定した n 個のグループに均等に分割し、各行にグループ番号を付与

ナビゲーション関数

CRM 分析や Web 解析などで使える分析関数。

関数名	処理内容
FIRST_VALUE(column)	指定範囲内の最初の値を取得
LAST_VALUE(column)	指定範囲内の最後の値を取得
NTH_VALUE(column, n)	指定範囲の n 番目 の値を取得
LAG(column, n)	指定カラムの n 行前 の値を取得（デフォルト n=1）
LEAD(column, n)	指定カラムの n 行後 の値を取得（デフォルト n=1）
PERCENTILE_CONT()	五数要約の指標を取得する

集計分析関数

元のレコードを保持したまま累計や平均などの値を取得できる。
ウィンドウ関数として使用する際は「集計関数」ではなく「集計分析関数」としての利用用途になる。

関数名	処理内容
SUM()	指定範囲の合計を計算する
AVG()	指定範囲の平均値を計算する
COUNT()	指定範囲の行数をカウントする
MAX()	指定範囲の最大値を取得する
MIN()	指定範囲の最小値を取得する

ウィンドウ関数の基本構文

PARTITION BY はパーティション（グループ化）として定義したいフィールド名を指定する。（省略した場合はテーブル全体がパーティションの範囲になる）
WINDOW フレーム は定義したパーティション内でもさらに「どのレコードを使うか」を「フレーム」として指定する。（省略可能な場合もある）
また OVER 句内の PARTITION BY,ORDER BY,WINDOWフレーム で指定した内容を「ウィンドウ」と呼ぶ。
ウィンドウはウィンドウ関数によって指定が必要なもの不要なものに分かれる。

ウィンドウ関数名 OVER (
PARTITION BY フィールド名
ORDER BY フィールド名
WINDOWフレーム
)

WINDOW フレームについて理解する

WINDOW フレームについて、次のような 3 ヶ月の売り上げ平均（A~F）を算出する場合を例に理解してみる。

ID	store	amount	sale_date	moving_avg
1	A 店	100	2023-01-01	A
2	A 店	200	2023-02-01	B
3	A 店	150	2023-03-01	C
4	B 店	350	2023-01-01	D
5	B 店	400	2023-02-01	E
6	B 店	450	2023-03-01	F

この時 A~F は次のように求めることができる。

• A => 100
• B => (100+200)/2 = 150
• C => (100+200+150)/3 = 150
• D => 350
• E => (350+400)/2 = 375
• F => (350+400+450)/3 = 400

この時 AVG 関数はパーティション内のレコードの値を全て使わずに一部の値のみを使うことになる。
このようにパーティション内の全てのレコードの値を使わない場合にどのレコードを利用するのかを指定する役割を持つのが WINDOW フレームである。ここで指定するレコードは「フレーム」と呼ばれる。

ROWS を使ったフレームの指定

このフレームの上限・下限を指定する構文の一つが ROWS BETWEEN 句になる。

ROWS BETWEEN
パーティション内で利用するレコード（フレーム）の上限
AND
パーティション内で利用するレコード（フレーム）の下限

また上限と下限を指定するに次のようなキーワードを指定する必要がある。

• UNBOUNDED PRECENDING（パーティションの上限）
• X PRECENDING（現在の行から X 行だけ上）
• CURRENT ROW（現在の行）
• X FOLLOWING（現在の行から X 行だけ下）
• UNBOUNDED FOLLOWING（パーティションの下限）

例えばパーティション内で使用するレコードが 直前の一行 ~ 現在の行 であれば次のような指定になる。

ROWS BETWEEN 1 PRECENDING AND CURRENT ROW

最初に見た 3 ヶ月の売り上げ平均（A~F）を算出する場合だと一番レコードを使用するのは 3 ヶ月目のレコードで、平均を求めるためには前 2 行のレコードと現在の行のレコードの値を使う。そのため次のような指定になる。

// パーティション内で利用するフレームの上限が前2行
// パーティション内で利用するフレームの下限が現在の行
ROWS BETWEEN 1 PRECENDING AND CURRENT ROW

最終的には次のような SQL で求めることができる。

WITH sales AS (
    SELECT 1 AS id, 'A店' AS store, 100 AS amount, DATE '2023-01-01' AS sale_date UNION ALL
    SELECT 2, 'A店', 200, DATE '2023-02-01' UNION ALL
    SELECT 3, 'A店', 150, DATE '2023-03-01' UNION ALL
    SELECT 4, 'B店', 350, DATE '2023-01-01' UNION ALL
    SELECT 5, 'B店', 400, DATE '2023-02-01' UNION ALL
    SELECT 6, 'B店', 450, DATE '2023-03-01'
)

SELECT
    id,
    store,
    amount,
    sale_date,
    AVG(amount) OVER (
        PARTITION BY store
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM
    project_id.dataset.sales
ORDER BY
    store, sale_date

ID	store	amount	sale_date	moving_avg
1	A 店	100	2023-01-01	100
2	A 店	200	2023-02-01	150
3	A 店	150	2023-03-01	150
4	B 店	350	2023-01-01	350
5	B 店	400	2023-02-01	375
6	B 店	450	2023-03-01	400

ちなみに WINDOW フレームを省略した場合のデフォルトは次のようになる。
（フレームの上限から現在の行までが範囲）

ROWS BETWEEN UNBOUNDED PRECENDING AND CURRENT ROW

RANGE を使ったフレームの指定

ROWS 以外にも RANGE を使ってフレームの指定ができる。
ROWS がフレーム内の「行」を基準に動作するのに対して RANGE はフレーム内の「値」を基準に動作する。

ROWS では次のような場合「一行前 ~ 現在の行」という範囲を表すが、

ROWS BETWEEN 1 PRECENDING AND CURRENT ROW

RANGE の場合は次のような場合「現在の値から -2 した値 ~ 現在の値」という値の範囲の指定になる。
例えば現在の行の値が「5」だとすると「3」「4」「5」の値をもつレコードが対象になる。
OVER 句の ORDER BY は数値である必要がある（ROWS は単純な行の数なのでこの制約はない）。

RANGE BETWEEN 2 PRECENDING AND CURRENT ROW

実際に違いを確認してみる。

WITH sales AS (
  SELECT 1 AS id, 100 AS amount,
  UNION ALL SELECT 2 AS id, 200 AS amount,
  UNION ALL SELECT 3 AS id, 200 AS amount,
  UNION ALL SELECT 4 AS id, 300 AS amount,
  UNION ALL SELECT 5 AS id, 400 AS amount,
)

SELECT
    id,
    amount,
    -- rows_sum は「一つ前の行」~「現在の行」が範囲なのでそれを足し算しているだけ
    SUM(amount) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS rows_sum,
    -- range_sum は「現在の値から100引いた値」~「現在の行の値」が範囲なので該当する値を全て足し算する
    SUM(amount) OVER (ORDER BY amount RANGE BETWEEN 100 PRECEDING AND CURRENT ROW) AS range_sum
FROM
    project_id.dataset.sales

rows_sum は単純に行の範囲なので理解しやすいと思う。
range_sum は例えば現在の値が「200」の場合は「100」~「200」の範囲に一致する全てのレコードの値が足し算されるようになる。

結果テーブルは次のようになる。

ID	amount	rows_sum	range_sum
1	100	100	100
2	200	300	500
3	200	400	500
4	300	500	700
5	400	700	700

ウィンドウ関数の結果に対する絞り込み

ウィンドウ関数の結果を絞り込む時に WHERE 句を使うことはできない。
解決方法の一つはサブクエリ。

SELECT
  *
FROM (
  SELECT
      id,
      store,
      amount,
      sale_date,
      AVG(amount) OVER (
          PARTITION BY store
          ORDER BY sale_date
          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
      ) AS moving_avg
  FROM
    project_id.dataset.sales
  ORDER BY
    store, sale_date
)
WHERE moving_avg > 150

もう一つは QUALIFY 句を使った絞り込みになる。
QUALIFY はウィンドウ関数の後に実行されるためサブクエリを使わずに絞り込みができる。

SELECT
    id,
    store,
    amount,
    sale_date,
    AVG(amount) OVER (
        PARTITION BY store
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM
    project_id.dataset.fsales
QUALIFY
    moving_avg > 150
ORDER BY
    store, sale_date

ここからは基本的な構文を確認してきたのでそれぞれのウィンドウ関数がどのように時に使えるのかを確認していく。

番号付け関数

ランキングを取得する

RANK 関数を使うとパーティション内でランキング順に表示できる。

WITH sales AS (
    SELECT 1 AS product_id, 'A店' AS store, 100 AS amount UNION ALL
    SELECT 2, 'A店', 200 UNION ALL
    SELECT 3, 'A店', 150 UNION ALL
    SELECT 4, 'B店', 350 UNION ALL
    SELECT 5, 'B店', 400 UNION ALL
    SELECT 6, 'B店', 450
)

SELECT
    id,
    store,
    amount,
    RANK() OVER (
        PARTITION BY store
        ORDER BY amount DESC
    ) AS rank
FROM
    project_id.dataset.sales
ORDER BY
    store, rank

お店毎に商品の売り上げがランキング順で表示されるようになる。

product_id	store	amount	rank
2	A 店	200	1
3	A 店	150	2
1	A 店	100	3
6	B 店	450	1
5	B 店	400	2
4	B 店	350	3

行番号を取得する

ROW_NUMBER 関数を使うと、パーティション内で順番に番号を降ってくれる。
RANK 関数とは違いランキングを付けるのではなくシンプルに順番通りに番号を振る。
そのため同じ値があってもスキップせずに番号を連番で振ってくれる。

次のようなテーブルがあっても 1,2,3 と振ってくれることを確認してみる。

employee_id	month	amount
1	2024-01-01	100
1	2024-02-01	100
1	2024-03-01	300
2	2024-01-01	250
2	2024-02-01	250
2	2024-03-01	350

SELECT
    employee_id,
    month,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY employee_id
        ORDER BY month
    ) AS row_num
FROM
    project_id.dataset.sales
ORDER BY
    employee_id, month;

結果テーブル

employee_id	month	amount	row_num
1	2024-01-01	100	1
1	2024-02-01	100	2
1	2024-03-01	300	3
2	2024-01-01	250	1
2	2024-02-01	250	2
2	2024-03-01	350	3

指定した n 個のグループに均等に分割し、各行にグループ番号を付与する

CRM 分析の一つに「デジル分析」というのがある。
デジル分析とは顧客を販売金額の大きい順に並べた上で同じ顧客数となるように 10 等分（語源はラテン語で「10 等分」という意味らしい）し、グループごとの販売金額合計や全体の占める割合を分析するもの。この分析することで優良顧客層を見つけ出したりでき、各グループの貢献度を知ることができる。
このデジル分析する時に最適なウィンドウ関数が NTILE 関数になる。

「2024 年毎のユーザーの合計購入金額」を表したテーブルで挙動を確認してみる。

user_id	sum_rev
1	19300
2	18000
3	17700
4	17200
5	15160
6	14400
7	14400
8	13000
9	12600
10	12400

まずは 10 人の user_id が存在する想定で NTILE 関数を適用した場合。

SELECT
    user_id,
    sum_rev,
    NTILE(10) OVER (ORDER BY sum_rev DESC) AS rev_group
FROM
    master
ORDER BY
    sum_rev DESC

10 人単位でグループ化するためシンプルに 1~10 のグループ番号がそれぞれ振り分けられる。

user_id	sum_rev	rev_group
1	19300	1
2	18000	2
3	17700	3
4	17200	4
5	15160	5
6	14400	6
7	14400	7
8	13000	8
9	12600	9
10	12400	10

次に 20 人の場合で確認してみる。

user_id	sum_rev
1	19300
2	18000
3	17700
4	17200
5	15160
6	14400
7	14400
8	13000
9	12600
10	12400
11	12200
12	12000
13	11400
14	10840
15	10600
16	10600
17	10320
18	10080
19	9800
20	9800

-- select * from master
SELECT
    user_id,
    sum_rev,
    NTILE(10) OVER (ORDER BY sum_rev DESC) AS rev_group
FROM
    master
ORDER BY
    sum_rev DESC

1~10 のグループ番号が振られるが「購入金額順」で番号が振り分けられている。
例えば user_id = 2 は先ほどグループ番号 2 だったが、今回はグループ番号 1 に割り振られている。

結果テーブル

user_id	sum_rev	rev_group
1	19300	1
2	18000	1
3	17700	2
4	17200	2
5	15160	3
6	14400	3
7	14400	4
8	13000	4
9	12600	5
10	12400	5
11	12200	6
12	12000	6
13	11400	7
14	10840	7
15	10600	8
16	10600	8
17	10320	9
18	10080	9
19	9800	10
20	9800	10

ナビゲーション関数

最初の値を取得する

FIRST_VALUE 関数を使うとパーティション内で並び替えた際に最も上のレコードの値を取得できる。
例えば「複数回購入している顧客の初回購入日」のような情報を取得できる。

WITH master AS (
    SELECT
        user_id,
        sum_rev,
        -- 顧客毎の初回購入日を取得
        FIRST_VALUE(purchase_date) OVER (
            PARTITION BY user_id
            ORDER BY purchase_date
        ) AS first_purchase_date,
    FROM
        project_id.dataset_id.customers
)

SELECT
    user_id,
    MAX(first_purchase_date) AS first_purchase_date
FROM
    master
GROUP BY
    user_id

次のように取得できる。

user_id	first_purchase_date
1	2023-09-24
2	2022-09-05
3	2021-04-27
4	2021-03-03
5	2023-09-08

NULL の取り扱い

幾つかのウィンドウ関数では NULL を無視できる IGNORE NULLS オプションを適用できる。
FIRST_VALUE 関数でも適用でき、NULL が存在する場合は NULL を拾わないように存在する値から取得できる。
例えば customer テーブルで「都道府県別に初めてユーザーが登録した日付」を取得する想定で確認してみる（確認しやすいように佐賀県で）。

SELECT
    prefecture,
    FIRST_VALUE(register_date) OVER (
        PARTITION BY prefecture
        ORDER BY register_date
    ) AS first_register_date
FROM
    project_id.dataset.customers
WHERE
    prefecture = '佐賀'

結果テーブルを見ると佐賀県の customer は 5 人登録されているが null になっている。

prefecture	register_date
佐賀	null
佐賀	null
佐賀	null
佐賀	null
佐賀	null

ただ実際に次のように日付が登録されている customer も存在している。
FIRST_VALUE 関数が NULL を拾ってしまったことが原因。

prefecture	register_date
佐賀	2023-01-01
佐賀	null
佐賀	2023-09-07
佐賀	2022-11-04
佐賀	2021-05-14

そのため IGNORE NULLS オプションを使うことで存在する値の中から一番最初の日付を拾うようになる。

SELECT
    prefecture,
    FIRST_VALUE(register_date IGNORE NULLS) OVER (
        PARTITION BY prefecture
        ORDER BY register_date
    ) AS first_register_date
FROM
    project_id.dataset_id.customers
WHERE
    prefecture = '佐賀'

結果テーブルでは次のように NULL が取り除かれた状態の日付を取得できた。

prefecture	register_date
佐賀	null
佐賀	2021-05-14
佐賀	2021-05-14
佐賀	2021-05-14
佐賀	2021-05-14

最後の値を取得する

LAST_VALUE 関数を使うとパーティション内に並び替えたうち最後のレコードの値を取得できる。
例えば顧客の最終購入日を取得したりできるようになる。

LAST_VALUE 関数で注意したい点は WINDOW フレームの指定方法について。
指定自体必須ではなくオプションだがデフォルトだと「パーティションの上端から現在の行」までが範囲になるためパーティション全体が範囲にならない。
そのため意図しない顧客の最終購入日を取得してしまう可能性がある。次のように「パーティションの上端から下端まで」を指定しておくことが必須になる。

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

WITH master AS (
    SELECT
        user_id,
        LAST_VALUE(date_time) OVER (
            PARTITION BY user_id
            ORDER BY date_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_purchase_date_time
    FROM
        project_id.dataset_id.sales
)

SELECT
    user_id,
    MAX(last_purchase_date_time) AS last_purchase_date_time
FROM
    master
GROUP BY
    user_id

次のように顧客毎に最終購入日を取得できた。

user_id	last_purchase_date_time
1	2021-07-24
2	2022-12-10
3	2023-06-23
4	2022-06-05
5	2022-02-04

任意の値を取得する

NTH_VALUE 関数を使うとパーティション内で並び替えた際に任意の順番の値を取得できる。
例えば初回と 2 回目の購入日を取得したその差分を知りたい時などに使える。

WITH master AS (
    SELECT
    user_id,
    -- 1回目の購入日を取得
    NTH_VALUE(order_date, 1) OVER(
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_purchase_date,
    -- 2回目の購入日を取得
    NTH_VALUE(order_date, 2) OVER(
      PARTITION BY user_id
      ORDER BY order_date
    ) AS secound_purchase_date,
  FROM
    project_id.dataset_id.sales
)

SELECT
    user_id,
    DATETIME_DIFF(
        MAX(secound_purchase_date),
        MAX(first_purchase_date),
        DAY
    ) AS diff
FROM
    master
GROUP BY
    user_id

次のように 1 回目と 2 回目の日付の差分を求めることができた。

user_id	diff
1	43

また FIRST_VALUE や LAST_VALUE, NTH_VALUE にも言えるのはパーティション内のレコード全てに適用したい値が取得されるため（GROUP BY ではないので）
最終的に集計したい場合は GROUP BY を使う必要がある。

現在のレコードの「直後」のレコードの値を取得する

LEAD 関数を使うと現在のレコードの「直後」のレコードの値を取得できる。

例えば次のようなテーブルが存在する場合に「次の月の売り上げ」を取得して比較ができたりする。

employee_id	month	amount
1	2024-01-01	100
1	2024-02-01	200
1	2024-03-01	300
1	2024-04-01	400
1	2024-05-01	500
1	2024-06-01	600

SELECT
    employee_id,
    month,
    amount,
    LEAD(amount) OVER (
        PARTITION BY employee_id
        ORDER BY month
    ) AS next_month_amount
FROM
    project_id.dataset.fsales
ORDER BY
    employee_id, month;

結果テーブルは次のようになる。次の行が存在しない場合は null が入るのは挙動として正しい。

employee_id	month	amount	next_month_amount
1	2024-01-01	100	300
1	2024-02-01	200	400
1	2024-03-01	300	500
1	2024-04-01	400	600
1	2024-05-01	500	null
1	2024-06-01	600	null

現在のレコードの「直前」のレコードの値を取得する

LAG 関数を使うことで実現できる。
こちらは前月比などで分析をしたいときに使えそう。
先ほどとは逆のパターンで試してみる。

SELECT
    employee_id,
    month,
    amount,
    LAG(amount) OVER (
        PARTITION BY employee_id
        ORDER BY month
    ) AS next_month_amount
FROM
    project_id.dataset.fsales
ORDER BY
    employee_id, month;

結果テーブルは次のようになる。前の行が存在しない場合は null が入るのは挙動として正しい。

employee_id	month	amount	next_month_amount
1	2024-01-01	100	null
1	2024-02-01	200	100
1	2024-03-01	300	200
1	2024-04-01	400	300
1	2024-05-01	500	400
1	2024-06-01	600	500

LEAD と LAG 関数のどちらとも第二引数を指定でき、指定した場合は「任意の行数後 or 行数前の値」という指定をできる。

集計分析関数

平均の取得

集計分析関数として AVG 関数を使うと移動平均を取得できる。
移動平均とは「時系列データにおいて、ある一定区間ごとの平均値を区間をずらしながら求める手法」とのこと。
通常の平均とは違い対象のレコードを「すらしながら」平均を求めるイメージ。

例として「3 項移動平均」という「1 レコード前」「現在のレコード」「1 レコード後」の 3 レコードの平均を求める手法で移動平均のイメージを掴んでみる。

employee_id	month	amount
1	2024-01-01	100
1	2024-02-01	200
1	2024-03-01	300
1	2024-04-01	150
1	2024-05-01	250
1	2024-06-01	350

SELECT
    employee_id,
    month,
    amount,
    FLOOR(AVG(amount) OVER (
        PARTITION BY employee_id
        ORDER BY month
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )) AS moving_avg_sales
FROM
    project_id.dataset.sales
ORDER BY
    employee_id, month;

結果テーブルは次のようになる。
注意点としては「どの行に対しても同じルール」で計算されるということ（足りないデータは考慮されない）。
そのため最初の行と最後の行は 1 レコードデータがない状態で計算されてしまう（2 レコードの平均になってしまっている）。

employee_id	month	amount	moving_avg_sales
1	2024-01-01	100	150.0
1	2024-02-01	200	200.0
1	2024-03-01	300	216.0
1	2024-04-01	150	233.0
1	2024-05-01	250	250.0
1	2024-06-01	350	300.0

これを回避したい場合は「計算対象のレコードが十分にある場合だけ表示する」というようなロジックを追加することになる。

SELECT
    employee_id,
    month,
    amount,
    CASE
      -- 3項移動平均を求めるために必要なレコード数を満たす場合のみ
      WHEN COUNT(amount) OVER (
        PARTITION BY employee_id
        ORDER BY month
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
      ) = 3
      THEN FLOOR(AVG(amount) OVER (
          PARTITION BY employee_id
          ORDER BY month
          ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
      ))
      END AS moving_avg_sales
FROM
    project_id.dataset.sales
ORDER BY
    employee_id, month;

結果は次のように計算対象のレコードが十分に満たない最初の行と最後の行は null になったことを確認できた。

employee_id	month	amount	moving_avg_sales
1	2024-01-01	100	null
1	2024-02-01	200	200.0
1	2024-03-01	300	216.0
1	2024-04-01	150	233.0
1	2024-05-01	250	250.0
1	2024-06-01	350	null

最後に

ここまで参考書籍を読みつつ手を動かしながら進めてきましたが今回で最後になります。
取り組んでみた感想として次のような実感を得ることができました。

• どんな関数が存在しているのかを知れたので引き出しが増えた
• クエリを書くときに意味を理解しながら書けるようになった
• 他の人が書いたクエリの意味を理解して読めるようになった

参考記事・書籍

BigQuery ではじめる SQL データ分析 GA4 & Search Console & Google フォーム対応