I got 2 tables. The first table has just dates. The second table has periods:
| period_id | period | start_date | end_date |
|---|---|---|---|
| 20240901 | 2024-09-01 00:00:00 | 2024-09-01 00:00:00 | 2024-09-15 00:00:00 |
| 20240916 | 2024-09-16 00:00:00 | 2024-09-16 00:00:00 | 2024-09-30 00:00:00 |
| 20241001 | 2024-10-01 00:00:00 | 2024-10-01 00:00:00 | 2024-10-15 00:00:00 |
| 20241016 | 2024-10-16 00:00:00 | 2024-10-16 00:00:00 | 2024-10-31 00:00:00 |
| 20241101 | 2024-11-01 00:00:00 | 2024-11-01 00:00:00 | 2024-11-15 00:00:00 |
| 20241116 | 2024-11-16 00:00:00 | 2024-11-16 00:00:00 | 2024-11-30 00:00:00 |
| 20241201 | 2024-12-01 00:00:00 | 2024-12-01 00:00:00 | 2024-12-15 00:00:00 |
| 20241216 | 2024-12-16 00:00:00 | 2024-12-16 00:00:00 | 2024-12-25 00:00:00 |
| 20241226 | 2024-12-26 00:00:00 | 2024-12-26 00:00:00 | 2025-01-15 00:00:00 |
| 20250116 | 2025-01-16 00:00:00 | 2025-01-16 00:00:00 | 2025-01-31 00:00:00 |
I got 2 tables. The first table has just dates. The second table has periods:
| period_id | period | start_date | end_date |
|---|---|---|---|
| 20240901 | 2024-09-01 00:00:00 | 2024-09-01 00:00:00 | 2024-09-15 00:00:00 |
| 20240916 | 2024-09-16 00:00:00 | 2024-09-16 00:00:00 | 2024-09-30 00:00:00 |
| 20241001 | 2024-10-01 00:00:00 | 2024-10-01 00:00:00 | 2024-10-15 00:00:00 |
| 20241016 | 2024-10-16 00:00:00 | 2024-10-16 00:00:00 | 2024-10-31 00:00:00 |
| 20241101 | 2024-11-01 00:00:00 | 2024-11-01 00:00:00 | 2024-11-15 00:00:00 |
| 20241116 | 2024-11-16 00:00:00 | 2024-11-16 00:00:00 | 2024-11-30 00:00:00 |
| 20241201 | 2024-12-01 00:00:00 | 2024-12-01 00:00:00 | 2024-12-15 00:00:00 |
| 20241216 | 2024-12-16 00:00:00 | 2024-12-16 00:00:00 | 2024-12-25 00:00:00 |
| 20241226 | 2024-12-26 00:00:00 | 2024-12-26 00:00:00 | 2025-01-15 00:00:00 |
| 20250116 | 2025-01-16 00:00:00 | 2025-01-16 00:00:00 | 2025-01-31 00:00:00 |
All I need add periods to the first table.
I've tried a query like this:
select t1.dates
p.period
from table_with_dates as t1
left join periods as p
on t1.dates between p.start_date and p.end_date
But it looks, like there's no 'between' type, joins in Clickhouse. I also know that I can add periods with subquery, but this query will work very slowly on big data.
So how to perform join like this?
Thanks for the answers!
I got Clickhouse version 24.3.14.35 (official build).
Share Improve this question edited Jan 21 at 19:15 Onur Doğan 2,05811 gold badges33 silver badges59 bronze badges asked Jan 20 at 18:26 John DoeJohn Doe 4023 silver badges12 bronze badges1 Answer
Reset to default 1use latest clickhouse-server:24.12 and use
select t1.dates
p.period
from table_with_dates as t1
left join periods as p
on t1.dates >= p.start_date and t1.dates <= p.end_date
SETTINGS allow_experimental_join_condition=1
if performance will not good, and your p table will fit in RAM (less 1-2 million rows), instead of join you could use
range dictionaries with dictGet
look details in documentation
https://clickhouse.com/docs/en/sql-reference/dictionaries#range_hashed
and query will look something like that:
select t1.dates, dictGet('db.dict','period',t1.dates, t1.dates) AS period
from table_with_dates as t1