先日BigQueryにおいて、カレンダーマスタなど日付別のデータを使用せず、指定期間内の各曜日数を計算する必要があったので、備忘録として残しておく。

集計元が日付別のデータだったり、カレンダーテーブルをジョインして日付別に拡張できる場合には、case文で単純に0 or 1でSUMすればよい。

しかし、指定期間が長い場合などは、日数に応じてデータ件数が増えてしまうので、時間もコストもかかってしまうので望ましくない。

そこで利用したSQLがこちら

-- 指定期間における日曜日の数

, FLOOR( DATE_DIFF(end_date, start_date, DAY) + 1 / 7 ) 
 + CASE WHEN STRPOS( SUBSTR('12345671234567', EXTRACT( DAYOFWEEK FROM start_date) , mod(DATE_DIFF(end_date, start_date, DAY) + 1 , 7)) , '1' ) > 0 THEN 1 ELSE 0 END AS sun_num

　
-- 指定期間における月曜日の数
 , FLOOR( DATE_DIFF(end_date, start_date, DAY) + 1 / 7 ) 
 + CASE WHEN STRPOS( SUBSTR('12345671234567', EXTRACT( DAYOFWEEK FROM start_date) , mod(DATE_DIFF(end_date, start_date, DAY) + 1 , 7)) , '2' ) > 0 THEN 1 ELSE 0 END AS mon_num

-- 以下繰り返し

start_dateとend_dateには、それぞれ指定期間の開始日と終了日の日付が入る。

BigQueryはTIMESTUMP型で日付時間を保持するので、必要に応じてDATE関数でDATE型に変換して入る必要がある。

以下では、順を追ってクエリを解説していく。

開始日の曜日から始まる一週間の周期から7の倍数で曜日数を計算

仮に2018年5月2日（水）から2018年5月13日（日）を例に取ってみる。

（月・火曜日はそれぞれ１日、水～日曜日はそれぞれ２日ずつが正解）

まず

DATE_DIFF(end_date, start_date, DAY) + 1

によって、指定期間の日数を算出する。

日付の差分を取るだけだと1日分足りなくなるので、DATE_DIFF関数に加えて1を足している。

次にFLOOR関数は、引数に等しいか、それより小さい最大整数を返す。

そのため、指定期間の日数を7で割ってFLOOR関数に入れることで、指定期間において開始日の曜日から始まる丸々一週間の循環が、何周期あるかを算出する。

先の例でいうと、2018年5月2日から2018年5月13日は12日間であり、7で割った値は約1.71となる。