ISOWEEKのような週番号単位の集計ではなく、例えば水曜日〜翌火曜日までの7日間を1週間単位として集計をしたい場合。

結論、BigQueryだと以下のクエリでtrancするのが一番ラク。

DATE_TRUNC(ymd, WEEK(WEDNESDAY))

以降は最初に書いた備忘。

select
  ymd
-- 前水曜〜火曜まで
, DATE_SUB(ymd, INTERVAL 3 DAY) as ymd_minus_3day
, EXTRACT(DAYOFWEEK FROM DATE_SUB(ymd, INTERVAL 3 DAY)) as day_of_week_of_ymd_minus_3day
, DATE_ADD( ymd, INTERVAL 1 - EXTRACT(DAYOFWEEK FROM DATE_SUB(ymd, INTERVAL 3 DAY)) DAY ) AS week_start_date

わかりやすく途中集計であるymd_minus_3day,day_of_week_of_ymd_minus_3dayも出力しているが、日別の元データに対してweek_start_dateでgroup byして集計すれば、特定曜日始まりで週単位の集計をすることが可能。

出力結果

上記の例は水曜日始まりのなので3dayになっているが、木曜日始まりなら4day、火曜日始まりなら2dayにすればよい。

軽くロジックの中身にふれると、BigQueryの

EXTRACT(DAYOFWEEK FROM date_part)

では、日曜日始まりの数を1〜7で返す関数である。

要件として、上記例の8/25水曜日始まりの7日間について確認してみると、

• 8/25水曜日→そのままの日付がweek_start_dateに入ってほしい
• 8/26木曜日→1日前の8/25がweek_start_dateに入ってほしい
• 8/27金曜日→2日前の8/25がweek_start_dateに入ってほしい

、、、というように、木曜日ならば1日前、金曜日ならば2日前の日付を取得すれば良いことがわかる。