この記事が解決できること
- 特定曜日の週の集計をするクエリがわかる
- UDF（ユーザー定義関数）化するクエリがわかる
はじめに
週単位の分析をする際、特定の曜日始まりで集計をする方法を紹介します。
BigQueryでEXTRACT関数を使って週を抽出すると、日曜日始まりの週数を返します。
しかし月曜日始まりの週や、もっとイレギュラーに水曜日始まりの週の分析をしたいかもしれません。この記事の中では、そんな時に使いたい簡単なクエリと、日付さえ与えれば週頭の日付を返してくれるUDF(ユーザー定義関数)も紹介します。
特定曜日でスタートする週を作成するクエリ
まずはクエリから紹介します。
DATE_ADD(_date, INTERVAL - EXTRACT(DAYOFWEEK FROM DATE_ADD(_date, INTERVAL -1 DAY)) +1 DAY) AS _week
これは週始まりを月曜日として、その週に属する日付に対して各週の月曜日の日付を付与します。
クエリを分解して説明します。
EXTRACT(DAYOFWEEK FROM DATE_ADD(_date, INTERVAL -1 DAY))
日付カラムからマイナス1日をして曜日ナンバーを抽出します。ここで大事なことは日付を算出することではなく、マイナス1日によって算出した日付の曜日ナンバーです。
本来、DAYOFWEEKは日曜日を起点に1から7の番号を返します。これをマイナス1日したことで日付カラムに対して、曜日の始まりが月曜日になるようにずらしました。
なので、火曜日を始まりとする場合はマイナス2日。水曜日を始まりとするならマイナス3日です。
DATE_ADD(_date, INTERVAL - EXTRACT(DAYOFWEEK FROM DATE_ADD(_date, INTERVAL -1 DAY)) +1 DAY)
最後に、日付カラムから「週ナンバー-1」した値だけ引き算をして完了です。
ここで作成したカラムを使用することで、特定の曜日を起点にした週単位の分析が可能になります。
おまけ：特定曜日でスタートする週を作成するUDF(ユーザー定義関数)
CREATE TEMP FUNCTION get_start_date(_date DATE) AS (
DATE_ADD(_date, INTERVAL - EXTRACT(DAYOFWEEK FROM DATE_ADD(_date, INTERVAL -1 DAY)) +1 DAY)
);
日付を与えるだけで、ここまで説明した処理をしてくれるユーザー定義関数を作成しました。クエリ冒頭に置いてことで機能してくれます。同じ処理が複数回発生する場合、ユーザー定義関数を作成しておくことで再利用性が高くなり、クエリもシンプルになります。必要な場面があれば是非ご活用ください。
まとめ
今回はBigQuery上で特定の曜日から週をスタートさせる方法を紹介しました。この記事ではSQLで操作する方法を解説しましたが、毎回SQL処理ができないケースもあると思います。例えばBIツールでデータは使用できるが、データソースに遡って加工ができないなどです。
そこで次回は、Looker Studioを使用して特定の曜日からスタートさせる方法を紹介します。
ここまでお読みいただきありがとうございました！
