BigQueryで指定期間内の各曜日数を計算する
先日BigQueryにおいて、カレンダーマスタなど日付別のデータを使用せず、指定期間内の各曜日数を計算する必要があったので、備忘録として残しておく。
集計元が日付別のデータだったり、カレンダーテーブルをジョインして日付別に拡張できる場合には、case文で単純に0 or 1でSUMすればよい。
しかし、指定期間が長い場合などは、日数に応じてデータ件数が増えてしまうので、時間もコストもかかってしまうので望ましくない。
そこで利用したSQLがこちら
start_dateとend_dateには、それぞれ指定期間の開始日と終了日の日付が入る。
BigQueryはTIMESTUMP型で日付時間を保持するので、必要に応じてDATE関数でDATE型に変換して入る必要がある。
以下では、順を追ってクエリを解説していく。
開始日の曜日から始まる一週間の周期から7の倍数で曜日数を計算
仮に2018年5月2日（水）から2018年5月13日（日）を例に取ってみる。
（月・火曜日はそれぞれ１日、水～日曜日はそれぞれ２日ずつが正解）
まず
によって、指定期間の日数を算出する。
日付の差分を取るだけだと1日分足りなくなるので、DATE_DIFF関数に加えて1を足している。
次にFLOOR関数は、引数に等しいか、それより小さい最大整数を返す。
そのため、指定期間の日数を7で割ってFLOOR関数に入れることで、指定期間において開始日の曜日から始まる丸々一週間の循環が、何周期あるかを算出する。
先の例でいうと、2018年5月2日から2018年5月13日は12日間であり、7で割った値は約1.71となる。
この12日間のうち、開始日である水曜日から始まる一週間は1周期であるので、まずは各曜日が1回以上は出現していることを判断する。
（仮に期間が14日間であれば、FLOOR関数は2を返すので、各曜日が2回ずつ出現したと判断できる。）
開始日の曜日から始まる一週間の周期からはみ出した日数の計算
続いて、開始日の曜日から始まる一週間の周期からはみ出してしまった計算をする。
SUBSTR関数は、引数に入れた文字列について、指定桁数から開始し、指定文字数分を切り取って返す。
では、水曜日を示す4が指定桁数となり、 Mod関数では、12日間を7で割った余りである、5文字分だけ切り取る。
（そのため最大でも7）
今回の文字列には、‘12345671234567’が入っており、切り取った結果文字列は‘45671’となる。
STRPOS関数は、引数に入れた文字列について、指定文字の出現する位置を返す。
（出現しなければ0を返す）
今回の例では、‘45671’が一週間の周期からはみ出してしまった各曜日のリストになり、case文で日曜日なら’1’、月曜日なら’2’がリストに含まれているか確認して、含まれていれば前述の曜日周期に1を追加している。
そのため、’45671’に含まれる水～日曜日には、1日ずつ曜日のカウントが足されることになる。
以上をまとめると、2018年5月2日（水）から2018年5月13日（日）には、水曜日から丸々一週間の周期が1回あり、加えてる水～日曜日が1日ずつある。
これらを曜日ごとに判定することで、指定期間内の各曜日数を計算することが可能。