经过查询确认,starrocks暂时不支持sequence日期序列函数,但是数值型的序列函数array_generate()。
select data_dt from unnest(sequence(cast(‘2024-09-01 as date’),cast(‘2024-09-07’ as date),INTERVAL ‘1’ day)) t (data_dt);
下面是替换的逻辑,欢迎交流指正。
- date_diff
-- 计算开始日期和结束日期间隔天数
select date_diff('DAY','2024-09-01','2024-09-07');
执行结果
6
- array_generate
-- array_generate生成序列
select array_generate(0,date_diff('DAY','2024-09-01','2024-09-07'),1);
执行结果
[0,1,2,3,4,5,6]
- inner join unnest
-- inner join unnest行转列
select * from (select '2024-09-01' as data_dt) a inner join unnest(array_generate(0,date_diff('DAY','2024-09-01','2024-09-07'),1)) u (step);
执行结果
2024-09-01 0
2024-09-01 1
2024-09-01 2
2024-09-01 3
2024-09-01 4
2024-09-01 5
2024-09-01 6
- 完整sql
-- 完整sql
select to_date(date_add(data_dt, INTERVAL step day)) as data_dt from (select '2024-09-01' as data_dt) a inner join unnest(array_generate(0,date_diff('DAY','2024-09-01','2024-09-07'),1)) u (step);
执行结果
2024-09-01
2024-09-02
2024-09-03
2024-09-04
2024-09-05
2024-09-06
2024-09-07