【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 というテーブルとして扱うのが混乱が少ないと思う。

StanによるMCMCサンプリング/素振り/ベータ分布

MCMC サンプリングの練習をしよう。
Stan を使う。実行環境は Google Colab 、よってローカル環境でのセットアップは一切不要。

colab.research.google.com

ここにノートブックがある。

モデル定義

シンプルにただのベータ分布からサンプリングするだけをやってみよう。

data {
    real<lower=0> alpha;
    real<lower=0> beta;
}
parameters {
    real<lower=0, upper=1> theta;
}
model {
    theta ~ beta(alpha, beta);
}

やってることは だけ。この をサンプリングして評価すればいい。

サンプリング実行

モデルをコンパイルしてサンプリングを実行しよう。

beta_model = CmdStanModel(stan_file=str(model_path))

fit = beta_model.sample(
    data={"alpha": 2.0, "beta": 5.0},
    chains=8, parallel_chains=8,
    iter_warmup=1000, iter_sampling=10000,
    seed=20250815
)

として からサンプリングする。
8並列で10,000個ずつサンプルを取る。最初の1,000個はバーンインとして捨てる。最終的に80,000個のサンプルが得られる。

サンプリング結果

得られたサンプルでヒストグラムを描いてみよう。

サンプルのヒストグラムとベータ分布の理論式を重ねたもの

いい感じにフィットして見える。

サンプルから統計量を推測する

得られたサンプルから標本平均, 標本標準偏差を計算すると、真の平均や真の標準偏差を推測できる。

summ = fit.summary()
print(summ.loc[["theta"], ["Mean", "StdDev", "R_hat", "ESS_bulk", "ESS_tail"]])

結果は、

	Mean (平均)	StdDev (標準偏差)	R_hat	ESS_bulk	ESS_tail
theta	0.284623	0.159601	1.00022	26463.6	25729.0

となった。

ベータ分布の平均と標準偏差の理論値は、

なのでここに を代入して比較すると、

	平均	標準偏差
理論値	0.285714	0.159718
標本からの推定	0.284623	0.159601

まぁまぁ良さそう。

まとめ

Google Colab は便利。Stan の文法に慣れていきたい。

私からは以上です。

本の虫

主人公が目を覚ますと、見知らぬ図書館にいた。高い天井、果てしなく続く本棚、淡い光が照らす無限の廊下。どこまでも同じ景色が続いているようで、方向感覚すら曖昧になる。自分がなぜここにいるのか、記憶は全く戻らない。

不安に駆られたまま彷徨っていると、年老いた学者風の男と出会った。彼は優しく微笑みながら語った。「おや、新しい訪問者かな？ここに迷い込んだ者は数えきれないほどいるが、脱出できた者は一人として知らない。私はもう何年も彷徨っているが、出口は存在しないと確信しているよ。ここは死後の世界に違いない。君も私も、生前の世界を離れてここに囚われたのだろう」

彼と別れさらに奥へ進むと、若い女性に出会った。彼女は本棚を指差しながら熱心に語り始めた。 「この図書館は驚異的よ。見渡す限りの本棚には、あらゆる言語で書かれた書物が無限に収められているわ。英語もあれば、ラテン語、日本語――見たこともない文字がびっしり並ぶ書物まである。あなたも何か探してみた？この図書館に必ずある。でも、それを実際に手に取るのは星の数ほどある砂の中から一粒の宝石を見つけるようなもの」

また少し進むと、興奮気味な青年に遭遇した。彼は目を輝かせながら図書館の秘密を囁いた。 「知ってるか？この図書館には、ある人間の人生すべてを記録した本があるんだ。生まれてから死ぬまでの行動、思考、感情までもが詳細に記されているんだ。そいつは誰かって？誰でもさ。あらゆる人間の運命が本になっていて、この図書館のあちこちに置かれているんだ」

主人公は再び一人で彷徨った。彼らの言うことは本当だろうか。ただ目の前に広がる無数の本だけは紛れもない現実で、そのどれもに何かしらの文章が詳細に書き綴られているのは確かだった。

何日も経ったある日、疲れ切った彼の前に、ヴィクターという中年の男が現れた。ヴィクターもまた、この図書館からの脱出方法を探しているという。

「脱出できる可能性があるとすれば、それは『目録』を見つけることだろう」ヴィクターは確信めいた口調で言った。「この図書館に存在する全ての本の目録さえ手に入れば、どんな情報でも瞬時に見つけ出せるはずだ。この図書館の見取り図の場所もわかるし、出口までの順路も分かる。しかし問題は、この無限の本棚から目録を探し当てなきゃいけないってことだ」

主人公は首をかしげ、懐疑的な態度を取った。 「目録？本当にあるのか？存在していたとして、見つけられるのか」

ヴィクターは異様な熱を込めて笑った。「いや、絶対にある。私はそれを確信しているんだ。必ず存在する。どこかに、必ず」

主人公は半信半疑だったが、ヴィクターの熱意に惹かれ、彼と共に図書館を探索し始めた。数日経ち、主人公はヴィクターが何かをいつも大切そうに抱えていることに気づいた。ヴィクターに訊ねる「君がいつも持ち歩いているそれは何だ？」

ヴィクターは一瞬躊躇したが、やがて重苦しく答えた。 「これは……偶然にも見つけてしまった私自身の人生が記された本だ。初めの数ページを読んだだけで、間違いなく本物だと確信したよ。私の運命すべてが書かれている。だが、恐ろしくてそれ以上読み進めることはできなかった。もし私がここを出ることの叶わない運命だったら？そんなことを知ってしまったら、私は絶望に耐えられない。だから誰にも読まれないように、こうして布で包んで肌身離さず持っている」

長い探索に疲れ切ったある日、主人公はヴィクターに提案した。「君のその本を読めば、出口への道筋も書かれているのではないか？君が目録を見つけると言うなら。いつどこで目録を手にするか書かれているはず。それさえ分かれば、明日にはここを脱出できるかもしれない」

ヴィクターはしばらく黙り込んだ。沈黙の時間が流れ、ため息を漏らした。

「君の言うとおりだ。私にもそれは分かっていた。これ以上恐れていても仕方ない……だが今日は休ませてくれ。明日、覚悟を決めて読むよ」

翌朝、主人公は衝撃的な光景を目にした。ヴィクターが天井からぶら下がり、既に息絶えていたのだ。ヴィクターは彼自身の運命を知ることを恐れたのか。主人公は彼の傍らに落ちていたその本を手に取った。半信半疑で本を開き、ページをめくり始める。

そこには確かにヴィクターという男の記録が書かれていた。最初はただの偶然かと思ったが、でたらめにしてはあまりにも詳細だった。図書館で主人公に出会うこと。その時の会話、ヴィクターの表情や仕草までが正確に書かれている。ページめくるたびにその本が紛れもない真実を記していることが主人公にも明確になっていった。

本の最後のページには、ヴィクターが自分の運命を恐れ、ついに自らの命を絶つシーンが書かれていた。

主人公は仲間と手掛かりを同時に失い、絶望して座り込んだ。目の前にはこれまでと同じように無限の本棚が広がっているだけだ。だがふと視線を上げると、丁寧に装丁された分厚い本が目に留まった。背には主人公の名前が記されている。主人公は一瞬戸惑い、震えながらその本を手に取った。そして最初のページをめくってみる。

1ページめ、見知らぬ図書館で目を覚ます主人公の様子が書かれていた。