テック

BigQuery | 無からカレンダーを生成(GENERATE_DATE_ARRAY)

使い方

order_idorder_dateuser_idamount
12021-04-0186414900
22021-04-0129919100
32021-04-027181400
42021-04-047911800
52021-04-041134100
62021-04-0581811100
72021-04-0591621400

クエリ

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

結果

dateamount
2021-04-0134000
2021-04-021400
2021-04-030
2021-04-0415900
2021-04-0532500
2021-04-060
2021-04-070

解説

  • 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

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

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

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

関連リンク

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

ビッグデータ分析
この記事に関連する記事