中野智文のブログ

データ・マエショリストのメモ

BigQuery で、ある特定の期間の日を全て列挙する

背景

BigQuery で、ある特定の期間の日を全て列挙したくなった。

方法

generate_date_array を使う。

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#generate_date_array

例:'2018-01-01' から '2018-12-31' までの期間を array に。

SELECT
  GENERATE_DATE_ARRAY('2018-01-01', '2018-12-31') dt_list

例:'2018-01-01' から '2018-01-31' と '2018-06-01'から'2018-06-30' までの期間を array に。

WITH
  date_range AS (
  SELECT
    DATE '2018-01-01' AS start_date,
    DATE '2018-01-31' AS end_date
  UNION ALL
  SELECT
    DATE '2018-06-01' AS start_date,
    DATE '2018-06-30' AS end_date )
SELECT
  ARRAY_CONCAT_AGG(GENERATE_DATE_ARRAY(start_date, end_date)) dt_list
FROM
  date_range

例:'2018-01-01' から '2018-01-31' と '2018-01-15'から'2018-02-15' までの期間をかぶりを考慮しつつ 展開

WITH
  date_range AS (
  SELECT
    DATE '2018-01-01' AS start_date,
    DATE '2018-01-31' AS end_date
  UNION ALL
  SELECT
    DATE '2018-01-15' AS start_date,
    DATE '2018-02-15' AS end_date ),
  date_list AS (
  SELECT
    ARRAY_CONCAT_AGG(GENERATE_DATE_ARRAY(start_date, end_date)) dt_list
  FROM
    date_range )
SELECT
  DISTINCT dt
FROM
  date_list
JOIN
  UNNEST(dt_list) AS dt

例:'2018-01-01' から '2018-01-31' と '2018-01-15'から'2018-02-15' までの期間をかぶりを考慮しつつ またarrayに

WITH
  date_range AS (
  SELECT
    DATE '2018-01-01' AS start_date,
    DATE '2018-01-31' AS end_date
  UNION ALL
  SELECT
    DATE '2018-01-15' AS start_date,
    DATE '2018-02-15' AS end_date ),
  date_list AS (
  SELECT
    ARRAY_CONCAT_AGG(GENERATE_DATE_ARRAY(start_date, end_date)) dt_list
  FROM
    date_range )
SELECT
  ARRAY(
  SELECT
    DISTINCT dt
  FROM
    UNNEST(dt_list) dt) AS dt_list
FROM
  date_list