我可以使用日期和日期间隔作为关键参数来连接Athena上的表吗？
像这样，
select *
from [table1] A
left join [table2] B on B.Date between A.Data_Begins and A.Data_Ends
没什么，只是想解决一个问题.
我可以使用日期和日期间隔作为关键参数来连接Athena上的表吗？
像这样，
select *
from [table1] A
left join [table2] B on B.Date between A.Data_Begins and A.Data_Ends
没什么，只是想解决一个问题.
雅典娜是基于Presto/Trino的，至少后者支持它，你自己试试吧:
-- sample data
with dataset(id, dt_start, dt_end) as (
values ('key1', date '2023-02-01', date '2023-02-10'),
('key3', date '2023-02-08', date '2023-03-10')
),
dataset2(num, dt) as (
values (1, date '2023-02-01'),
(2, date '2023-02-09'),
(3, date '2023-03-10'),
(4, date '2023-03-12')
)
-- query
select *
from dataset d
left join dataset2 d2 on d2.dt between d.dt_start and d.dt_end;
输出:
| id | dt_start | dt_end | num | dt |
|---|---|---|---|---|
| key1 | 2023-02-01 | 2023-02-10 | 1 | 2023-02-01 |
| key1 | 2023-02-01 | 2023-02-10 | 2 | 2023-02-09 |
| key3 | 2023-02-08 | 2023-03-10 | 2 | 2023-02-09 |
| key3 | 2023-02-08 | 2023-03-10 | 3 | 2023-03-10 |