SQLで期間ごとにデータを取得したい

Question

期間テーブルとデータテーブルがあるときに、期間テーブルの期間に合わせてデータを取得したいです。
期間ごとにSQLを実行してunionすればできるのですが、実際には期間は多いためSQL1回で取得したいと考えています。
うまくJOINすることで可能だとは考えているのですが。。。

環境はBigQueryになります。

データイメージ

期間テーブル

name	start_date	end_date
kikan1	2000-01-01	2000-03-01
kikan2	2000-02-01	2000-03-01
kikan3	2000-01-01	2000-05-01

データテーブル

id	hiduke
1	2000-01-01
2	2000-01-02
3	2000-02-01
4	2000-04-01

求めたい結果

name	id	date
kikan1	1	2000-01-01
kikan1	2	2000-01-02
kikan1	3	2000-02-01
kikan2	3	2000-02-01
kikan3	1	2000-01-01
kikan3	2	2000-01-02
kikan3	3	2000-02-01
kikan3	4	2000-04-01

環境作成用SQL

CREATE TABLE kikan (
    name VARCHAR(10),
    start_date TIMESTAMP,
    end_date TIMESTAMP
);

INSERT INTO kikan VALUES
    ('kikan1', '2000-01-01', '2000-03-01'),
    ('kikan2', '2000-02-01', '2000-03-01'),
    ('kikan3', '2000-01-01', '2000-05-01')
;

CREATE TABLE data1 (
    id INT,
    hiduke TIMESTAMP
);

INSERT INTO data1 VALUES
    (1, '2000-01-01'),
    (2, '2000-01-02'),
    (3, '2000-02-01'),
    (4, '2000-04-01')
;

取得したい結果のイメージSQL

SELECT 'kikan1' AS name, id, hiduke FROM data1 WHERE hiduke between '2000-01-01' AND '2000-03-01'
UNION ALL
SELECT 'kikan2' AS name, id, hiduke FROM data1 WHERE hiduke between '2000-02-01' AND '2000-03-01'
UNION ALL
SELECT 'kikan3' AS name, id, hiduke FROM data1 WHERE hiduke between '2000-01-01' AND '2000-05-01';

もしくは

WITH
kikan1_t AS (SELECT name , start_date, end_date FROM kikan WHERE name = 'kikan1'),
kikan2_t AS (SELECT name , start_date, end_date FROM kikan WHERE name = 'kikan2'),
kikan3_t AS (SELECT name , start_date, end_date FROM kikan WHERE name = 'kikan3')

SELECT name, id, hiduke FROM kikan1_t inner join data1 on hiduke between kikan1_t.start_date AND kikan1_t.end_date
UNION ALL
SELECT name, id, hiduke FROM kikan2_t inner join data1 on hiduke between kikan2_t.start_date AND kikan2_t.end_date
UNION ALL
SELECT name, id, hiduke FROM kikan3_t inner join data1 on hiduke between kikan3_t.start_date AND kikan3_t.end_date;

Comments

[KOZ6.0]

BigQuery の SQL は知らないので、回答はしませんが、
data1.hiduke BETWEEN kikan.start_date AND kikan.end_date の条件で結合し、
kikan.name, data1.id 順に並び替えるだけでは？

Answer 1

KOZ6.0さんのコメントにより解決しました。
そのままjoinすればよかったのですね。助かりましたありがとうございます。

解決したSQL

SELECT
    name,
    id,
    hiduke
FROM kikan
INNER JOIN data1
ON hiduke BETWEEN kikan.start_date AND kikan.end_date
ORDER BY NAME;