こんにちは。研究開発部Architectグループにてデータエンジニアとしてデータ分析基盤の開発・運用をしている中村です。
今回はBigQuery上で利用可能な祝日対応したカレンダーテーブルを作成する方法についてご紹介いたします。

「カレンダーテーブルが欲しい」

例えば以下のような購買情報テーブル(orders)があったとして、日ごとの販売数を集計しようとするケースを考えます。

order_id	date	product_id	quantity
1	2023-08-01	001	20
2	2023-08-01	002	10
3	2023-08-01	003	10
4	2023-08-02	001	30
5	2023-08-02	003	10
6	2023-08-04	001	10
7	2023-08-04	003	10

単純に考えるとSQLは以下のようになります。

SELECT
  date,
  SUM(quantity) AS total_sales
FROM
  orders
GROUP BY
  date
ORDER BY
  date;

結果は以下のようになりました。

date	total_sales
2023-08-01	40
2023-08-02	40
2023-08-04	20

しかし、この集計結果では、2023-08-03 の合計値が取れないため、グラフとして出力する場合、連続した日付で出力されません。

これを回避するためには、以下のようにBigQueryのGENERATE_DATE_ARRAY関数を用いて連続した日付を生成したテンポラリテーブルに結合することで、連続する日付での集計データを作成することができます。

WITH
  calendar AS (
  SELECT
    date
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2023-08-01', '2023-08-04', INTERVAL 1 DAY)) AS date )
SELECT
  cal.date,
  COALESCE(SUM(o.quantity), 0) AS total_sales
FROM
  orders o
RIGHT JOIN
  calendar cal
ON
  o.date = cal.date
GROUP BY
  cal.date
ORDER BY
  cal.date;

結果は以下のようになりました。

date	total_sales
2023-08-01	40
2023-08-02	40
2023-08-03	0
2023-08-04	20

出力される内容としては良さそうです。
これは実際に弊社のデータアナリストが実施していたクエリに基づいた例ですが、都度こようなロジックを埋め込む必要があるため、クエリの可読性が落ちてしまうことや生成処理のオーバーヘッドがありました。
そこで、予め連続した日付を保持しておくテーブル(今回の例ではcalendar)を用意しておくことで、クエリを以下のようにシンプルに書くことができます。

CREATE TABLE
  calendar AS (
  SELECT
    date
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2023-01-01', '2023-12-31', INTERVAL 1 DAY)) AS date );

SELECT
  cal.date,
  COALESCE(SUM(o.quantity), 0) AS total_sales
FROM
  orders o
RIGHT JOIN
  calendar cal
ON
  o.date = cal.date
WHERE
  cal.date BETWEEN '2023-08-01' AND '2023-08-04'
GROUP BY
  cal.date
ORDER BY
  cal.date;

「土日祝日に対応したカレンダーテーブルが欲しい」

さて、本題です。
「データ分析を行う際に、土日祝日も考慮したい」という要望がありました。
確かに、土日祝日はプロダクトの利用状況も変化しますので、それを考慮することでより分析の質が向上できます。
土日の判定は EXTRACT(DAYOFWEEK FROM date) で良いのですが、国民の祝日は年によって変動することもありますので、別途取得方法を検討する必要があります。
これをなるべく低コストで実現する調査したところ、ちょうど2023/7にBigQueryの一般公開データセットに祝日データが追加されたとのことでしたので、こちらのデータについて調べてみました。

SELECT
  *
FROM
  `bigquery-public-data.ml_datasets.holidays_and_events_for_forecasting`
WHERE
  region = 'JP'
  AND primary_date BETWEEN '2023-01-01'
  AND '2023-12-31'
ORDER BY
  primary_date;

結果は以下のようになりました。

ml_datasetsのholidays_and_events_for_forecasting

一見すると良さそうに思えますが、「Valentine」や「US_DaylightSavingStart」など、国民の祝日以外も含まれてしまっています。
執筆時点(2023/9)ではまだPreview段階とのことでしたので、こちらの利用は断念しました。

次に、bqfuncで公開されている祝日判定UDFの利用を検討しました。 github.com

bqfuncはBQ FUNコミュニティの方が作成されたBigQueryルーティンをまとめたデータセットです。(Apache-2.0ライセンス) また、祝日判定UDFについては、こちらの記事を参考にしています。 qiita.com

こちらを用いて祝日のデータを取得しようと思います。データセットは東京リージョン版(holidays_in_japan__asia_northeast1)とUSリージョン版(holidays_in_japan__us)があるので、利用しているBigQueryのリージョンに合わせて選択しましょう。

WITH
  date_range AS (
  SELECT
    date
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2023-01-01', '2023-12-31')) AS date )
SELECT
  date,
  bqfunc.holidays_in_japan__asia_northeast1.holiday_name(date) AS holiday_name
FROM
  date_range
WHERE
  bqfunc.holidays_in_japan__asia_northeast1.holiday_name(date) IS NOT NULL
ORDER BY
  date;

結果は以下のようになりました。

holidays_and_events_for_forecastingのデータよりも正確に国民の祝日が取得できています。
他にも利用可能なデータソースはありそうですが、ひとまずはこちらでカレンダーテーブルを提供することにしました。

最終的なカレンダーテーブルのSQLは以下のようになっています。

WITH
  date_range AS (
    SELECT
      date
    FROM
      UNNEST(GENERATE_DATE_ARRAY('2023-01-01', '2023-12-31')) AS date
  )
SELECT
  date,
  CASE
    WHEN EXTRACT(DAYOFWEEK FROM date) = 1 THEN '日'
    WHEN EXTRACT(DAYOFWEEK FROM date) = 7 THEN '土'
    WHEN bqfunc.holidays_in_japan__asia_northeast1.holiday_name(date) IS NOT NULL THEN bqfunc.holidays_in_japan__asia_northeast1.holiday_name(date)
    ELSE NULL
  END AS holiday_name,
  CASE
    WHEN(EXTRACT(DAYOFWEEK FROM date) = 1
    OR EXTRACT(DAYOFWEEK FROM date) = 7
    OR bqfunc.holidays_in_japan__asia_northeast1.holiday_name(date) IS NOT NULL) THEN true
    ELSE false
  END AS is_holiday
FROM
  date_range;