【データ基盤構築/BigQuery】WINDOW関数で日付の範囲を指定する場合は、ORDER BY句でUNIX_DATE()関数を使用する

今回の課題

WINDOW関数を使用して、移動合計の値を抽出しようとしたところ、
下記のエラーが発生してしまった。

エラー内容

ORDER BY key must be numeric in a RANGE-based window with OFFSET PRECEDING or OFFSET FOLLOWING boundaries, but has type DATE at[52:56]

ORDER BY句は数値を指定しなければいけないが、
DATE型を指定してしまっていることによってエラーが発生している様子。

使用したクエリ

エラーの通り、WINDOW関数内のORDER BY句で、数値を指定してしまっていた。

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

解決したクエリ

dateにUNIX_DATE関数をかけて、1970-01-01から〇日間というかたちでデータを取得し、
それらをorder by句で昇順で取得するようにすることで解決することができた。

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
    `テーブル名`

UNIX_DATE()関数とは

1970-01-01からの日数を返す関数。

（例）

• クエリ
  SELECT UNIX_DATE(DATE "2008-12-25") AS days_from_epoch;

• 出力値

days_from_epoch
14238

※参考：UNIX_DATE（公式ドキュメント）