【データ基盤構築/BigQuery】WINDOW関数を使用して〇日間数値を出す場合、FRAME句ではRANGEを使った方が良い

今回の課題

WINDOW関数を使用して、
配信開始日から7日間, 30日間, 60日間時点のpv数を取得した際、
FRAME句でROWSを使用したことによって、不具合が発生した。

発生した不具合

下記のクエリだと、配信開始日から7日間, 30日間, 60日間時点での正しいpv数を取得することができなかった。

理由は、フレーム句でROWSを使用しているので、order by句のdateを昇順に並べて、
7行目, 30行目, 60行目時点でのpv数を集計して抽出してしまうため。

select
    date
    , page_title
    , release_date  -- 配信開始日
    , sum(page_views) over (partition by page_title order by unix_date(date) rows between 6 following) as pv_7days
    , sum(page_views) over (partition by page_title order by unix_date(date) rows between 29 following) as pv_30days
    , sum(page_views) over (partition by page_title order by unix_date(date) rows between 59 following) as pv_60days
from
    `テーブル名`

改善方法

フレーム句をRANGEに変更することで、release_dateから7日間, 30日間, 60日間時点でのpv数を取得することができる。

select
    date
    , page_title
    , release_date -- 配信開始日
    , sum(page_views) over (partition by page_title order by unix_date(date) range between unbounded preceding and 6 following) as pv_7days
    , sum(page_views) over (partition by page_title order by unix_date(date) range between unbounded preceding and 29 following) as pv_30days
    , sum(page_views) over (partition by page_title order by unix_date(date) range between unbounded preceding and 59 following) as pv_60days
from
    `テーブル名`
where
    date = release_date

※参考：分析関数（ウインドウ関数）をわかりやすく説明してみた

これで今回の課題を解決することができた。
ROWSとRANGEの違いは下記に記載した。

WINDOW関数のFRAME句について

WINDOW関数において、FRAME句の指定方法は2種類ある。

ROWS

OVER句のORDER BYで指定されたフレーム内の「行」を基準に動作する。
(例)1 PRECENDING AND CURRENT ROW→１行前と自分の行がフレームになる。

RANGE

OVER句のORDER BYで与えられたフレーム句の「値」を基準に動作する。
(例)1 PRECENDING AND CURRENT ROW→１つ前の値と自分の行がフレームになる。

今回のクエリで言うと、ROWSを使っても、全てのdateが存在していれば不具合は発生しないが、
dateに漏れがあると正しい数値が取得できない恐れがあった。
※参考：WINDOWフレーム句のROWSとRANGEの違い