無駄と文化

実用的ブログ

【SQL】期間を指定して日付を列挙する

BigQuery SQL

モチベーション

SQL を書いていると期間をもとに日付を列挙したくなることが稀によくある。書き方を見てみよう。

免責事項

本記事は BigQuery を前提にしています。紹介している generate_date_array 関数は MySQL/PostgreSQL には存在しない BigQuery 独自の機能 です。
ちなみに PostgreSQL では generate_series 関数を使って同様のことができるらしいです。

unnest(generate_date_array(...)) を使おう

手元に START_DATE, END_DATE の値があるとして、

select d
from unnest(generate_date_array(START_DATE, END_DATE)) as d

こう書くとよい。

 

試そう。

with
_dates as (
    select d
    from unnest(generate_date_array('2025-08-10', '2025-08-13'))
        as d
)

select *
from _dates
order by d asc

実行結果

2025-08-10 と 2025-08-13 を指定して、

d
1 2025-08-10
2 2025-08-11
3 2025-08-12
4 2025-08-13

が得られた

 

応用: 特定の曜日だけ列挙する

WHERE 句と組み合わせると条件を満たす日付だけを列挙できる。
例えば 2025年8月中の火曜, 金曜がほしいなら、

with
_dates as (
    select d
    from unnest(generate_date_array('2025-08-01', '2025-08-31'))
        as d
    where format_date('%a', d) in ('Tue', 'Fri')
)

select d, format_date('%a', d) as day_of_week
from _dates
order by d asc

こうだ。

WHERE 句に where format_date('%a', d) in ('Tue', 'Fri') を指定しているので火曜, 金曜だけが抽出される。

実行結果

2025-08-01 と 2025-08-31 を指定して、

d day_of_week
1 2025-08-01 Fri
2 2025-08-05 Tue
3 2025-08-08 Fri
4 2025-08-12 Tue
5 2025-08-15 Fri
6 2025-08-19 Tue
7 2025-08-22 Fri
8 2025-08-26 Tue
9 2025-08-29 Fri

が得られた。

 

起こっていることを理解しよう

GENERATE_DATE_ARRAY 関数 は開始日と終了日を渡すと ARRAY 型の値を返してくれる。

select generate_date_array('2025-08-10', '2025-08-13');
/*----------------------------------------------------------*
 | f0_                                                      |
 +----------------------------------------------------------+
 | ['2025-08-10', '2025-08-11', '2025-08-12', '2025-08-13'] |
 *----------------------------------------------------------*/

 

UNNEST 演算子 は ARRAY をフラット化して行の集合に変換してくれる。

select *
from unnest(generate_date_array('2025-08-10', '2025-08-13'));
/*--------------*
 | f0_          |
 +--------------+
 | '2025-08-10' |
 | '2025-08-11' |
 | '2025-08-12' |
 | '2025-08-13' |
 *--------------*/

N行×1列 のテーブル相当の結果が返るので、UNNEST はサブクエリが書ける場所でしか使えない。

unnest(...) が関数っぽく見えてしまうので、結果として1列のテーブルが返るのは相当混乱する。
実際のクエリでは CTE を使って、

with

_dates as (
    select d
    from unnest(generate_date_array('2025-08-10', '2025-08-13')) as d;
)

...

と書いてしまって。以降は _dates というテーブルとして扱うのが混乱が少ないと思う。