在Snowflake中以分钟为单位分割start_time和endtime之间的时间持续时间

Question

我有一个表格，格式如下:

<表类> USER_ID START_TIME END_TIME tbody><<tr>AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.000

假设您想保留start_time和end_time之间的分钟间隔，并且假设end_time - start_time <= 24小时:

with 
t0 as
(select
'AAA001' as user_id,
'2020-04-04 09:04:27.000' as start_time,
'2020-04-04 09:08:34.000' as end_time),
t1 as
(select row_number() over(order by 0) as i
from table(generator(rowcount => 3600))) -- 3600 minutes in 24 hours
select
t0.user_id,
t0.start_time,
t0.end_time,
timeadd("minutes", i, t0.start_time) as minute_slot
from t1 cross join t0
where minute_slot <= end_time

基于这个答案的一个想法

与以下数据-

with data(USER_ID,START_TIME,END_TIME) as
(select * from values
('AAA001','2020-04-04 09:04:27.000'::timestamp,'2020-04-04 09:08:34.000'::timestamp),
('AAA002','2020-04-04 09:04:27.000'::timestamp,'2020-04-04 09:08:34.000'::timestamp)
),

第一种方法，与@Dave共享的方法一样，使用限定子句来限制(使用较大的生成器值)-

rn_cte as (
select row_number () over (partition by user_id order by 0) rn,user_id,
start_time,
end_time,
timeadd(minute,
rn,
start_time) new_time
from data,
table(generator(rowcount=>150))
qualify rn <= TIMESTAMPDIFF(minute, start_time,end_time)
)select user_id, start_time, end_time, new_time
from rn_cte;

或者，另一个获得虚拟行，避免由于generator子句中给定恒定值的限制而产生额外的行。

select user_id,
start_time,
end_time,
timeadd(minute, index, start_time) minute_slot from data,
table(split_to_table(repeat(',',TIMESTAMPDIFF(minute, start_time,end_time)-1),','));

都给出如下输出-

<表类>USER_IDSTART_TIMEEND_TIMENEW_TIMEtbody><<tr>AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:05:27.000AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:06:27.000AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:07:27.000AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:08:27.000AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:05:27.000AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:06:27.000AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:07:27.000AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:08:27.000