BigQuery | 無からカレンダーを生成（GENERATE_DATE_ARRAY）

使い方

order_id	order_date	user_id	amount
1	2021-04-01	864	14900
2	2021-04-01	299	19100
3	2021-04-02	718	1400
4	2021-04-04	79	11800
5	2021-04-04	113	4100
6	2021-04-05	818	11100
7	2021-04-05	916	21400

クエリ

SELECT
  date,
  IFNULL(SUM(amount), 0) amount
FROM
  UNNEST(GENERATE_DATE_ARRAY('2021-04-01', '2021-04-07')) date
LEFT JOIN
  dataset.orders
ON
  date = orders.order_date
GROUP BY
  date
ORDER BY
  date

結果

date	amount
2021-04-01	34000
2021-04-02	1400
2021-04-03	0
2021-04-04	15900
2021-04-05	32500
2021-04-06	0
2021-04-07	0

解説

• GENERATE_DATE_ARRAY で抜けのないカレンダー生成
• 期間を引数に指定する。例では 2021-04-01〜2021-04-07

Tips

週次、月次のカレンダーを生成することもできる。

クエリ

CREATE TEMPORARY FUNCTION BEGIN_OF_MONTH() AS (
    DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH)
);

SELECT
  date
FROM
  UNNEST(
    GENERATE_DATE_ARRAY(
      DATE_SUB(BEGIN_OF_MONTH(), INTERVAL 1 YEAR),
      DATE_SUB(BEGIN_OF_MONTH(), INTERVAL 1 MONTH),
      INTERVAL 1 MONTH
    )
  ) date

date
2020-07-01
2020-08-01
2020-09-01
2020-10-01
2020-11-01
2020-12-01
2021-01-01
2021-02-01
2021-03-01
2021-04-01
2021-05-01
2021-06-01

ポイントは第３引数に日付の幅を指定できるところ。

指定できる引数の種類は DAY、WEEK、MONTH、QUARTER または YEAR。

上記例では、一ヶ月ごとの日付を取得するために INTERVAL 1 MONTH を指定している。

関連リンク

• GENERATE_DATE_ARRAY – BigQuery 公式ドキュメント –

より実務的なクエリを身につけるには「ビッグデータ分析・活用のためのSQLレシピ」がおすすめです。

ビッグデータ分析

ビッグデータ分析・活用のためのSQLレシピ

created by Rinker

• Amazon

図解即戦力　ビッグデータ分析のシステムと開発がこれ1冊でしっかりわかる教科書

created by Rinker

技術評論社

• Amazon