BigQuery で、ある特定の期間の日を全て列挙する
背景
BigQuery で、ある特定の期間の日を全て列挙したくなった。
方法
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