Bigquery(SQL)で祝日判定する

なにこれ

平日・休日・祝日毎に違いがあるかを確認するために、Bigquery(SQL)で祝日判定をしたいです。

そのためのクエリを考えます。

方向性

Bigquery(StandardSQL)にもSQLにも祝日判定してくれる便利な関数はないです。

そこで以下の2つが考えられますが、今回は2を実現させるBigqueryを提示します。

1. 祝日を詰め込んだテーブルを作る
2. 祝日を出力する一時的なテーブルを作る

方法

愚直に祝日を配列として書き連ね、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