BigQueryで連番の日付を生成する例

bigquery-date-sequence.sql

-- BigQueryで連番の日付を生成する例
-- Standard SQL を使用
-- 2019-04-01 から 2020-03-31 まで
SELECT
  DATE_ADD(start_date.start_date, INTERVAL n.n DAY) AS date
FROM (
  SELECT
    DATE('2019-04-01') AS start_date) AS start_date
LEFT JOIN (
  SELECT
    n
  FROM
    UNNEST(GENERATE_ARRAY(0, DATE_DIFF( DATE('2020-03-31'), DATE('2019-04-01'), DAY))) AS n
  ORDER BY
    n) AS n
ON
  1=1;

GENERATE_DATE_ARRAY で良かったorz

SELECT * FROM UNNEST(GENERATE_DATE_ARRAY(DATE('2019-04-01'), DATE('2020-03-31'))) AS date ORDER BY date