trino中的sequence生成日期序列函数在starrocks中如何实现？

经过查询确认，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);

下面是替换的逻辑，欢迎交流指正。

1. date_diff

-- 计算开始日期和结束日期间隔天数
select date_diff('DAY','2024-09-01','2024-09-07');

执行结果

6

2. 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]

3. 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

4. 完整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