なにこれ
平日・休日・祝日毎に違いがあるかを確認するために、Bigquery(SQL)で祝日判定をしたいです。
そのためのクエリを考えます。
方向性
Bigquery(StandardSQL)にもSQLにも祝日判定してくれる便利な関数はないです。
そこで以下の2つが考えられますが、今回は2を実現させるBigqueryを提示します。
- 祝日を詰め込んだテーブルを作る
- 祝日を出力する一時的なテーブルを作る
方法
愚直に祝日を配列として書き連ね、UNNEST関数で展開します。
祝日に限らず、なにかデフォルトでデータを準備するというのに有用な方法です。
2017年の祝日リストテーブル
SELECT holiday FROM UNNEST([ DATE('2017-01-01'), DATE('2017-01-02'), DATE('2017-01-03'), DATE('2017-01-09'), DATE('2017-02-11'), DATE('2017-03-20'), DATE('2017-04-29'), DATE('2017-05-03'), DATE('2017-05-04'), DATE('2017-05-05'), DATE('2017-07-17'), DATE('2017-08-11'), DATE('2017-09-18'), DATE('2017-09-23'), DATE('2017-10-09'), DATE('2017-11-03'), DATE('2017-11-23'), DATE('2017-12-23') ]) as holiday
ordersテーブルのcreated_atが2017年の祝日に該当するか判定する
WITH holidays AS ( SELECT holiday FROM UNNEST([ DATE('2017-01-01'), DATE('2017-01-02'), DATE('2017-01-03'), DATE('2017-01-09'), DATE('2017-02-11'), DATE('2017-03-20'), DATE('2017-04-29'), DATE('2017-05-03'), DATE('2017-05-04'), DATE('2017-05-05'), DATE('2017-07-17'), DATE('2017-08-11'), DATE('2017-09-18'), DATE('2017-09-23'), DATE('2017-10-09'), DATE('2017-11-03'), DATE('2017-11-23'), DATE('2017-12-23') ]) as holiday ) SELECT -- 日曜日は問答無用で休日に -- 祝日判定後、祝日でない土曜日は休日にする CASE WHEN EXTRACT(DAYOFWEEK FROM created_at) = 1 THEN '休日' WHEN DATE(created_at) IN (SELECT * FROM holidays) THEN '祝日' WHEN EXTRACT(DAYOFWEEK FROM created_at) = 7 THEN '休日' ELSE '平日' END AS day_type FROM orders