How to create running timeline on the fly?

I want to create a dataset that captures a running timeline of events based on avg_time and num_events. The idea is to start at 00:00:00.00000 and create a timeline by adding avg_time in milliseconds to the previous time for num_events number of times.

Sample Data:

id    avg_time   num_events
1     43         3
2     23         2
3     59         1

Desired Output:

id    start_time         end_time
1     00:00:00.00000     00:00:00.04300
1     00:00:00.04300     00:00:00.08600
1     00:00:00.08600     00:00:00.12900
2     00:00:00.00000     00:00:00.02300
2     00:00:00.02300     00:00:00.04600
3     00:00:00.00000     00:00:00.05900

Honestly can't wrap my head around how I can do this. Maybe need to use while loops or recursive queries but is there a simpler way? Not sure if window functions are even applicable.

My attempt:

SELECT 
   id
 , CASE WHEN -- logic to identify first event
    THEN CAST('00:00:00.00000') AS start_time
   ELSE -- LAG() to capture previous row end_time somehow?
   END AS start_time
 , TIME_ADD(--prev_start_time, INTERVAL avg_time MILLISECOND)
FROM my_table

CodePudding user response：

Try below query:

with sample_data as (
  select 1 as id, 43 as avg_time, 3 as num_events, 
  union all select 2 as id, 23 as avg_time, 2 as num_events,
  union all select 3 as id, 59 as avg_time, 1 as num_events
),
t1 as (
select 
*,
sum(avg_time) over (partition by id order by avg_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as end_time,
from sample_data, 
unnest(generate_array(1,num_events)) as arr 
)

select 
id,
avg_time,
TIME_ADD(TIME(0, 0, 0),interval lag(end_time) over (partition by id order by avg_time) millisecond) as start_time,
time_add(TIME(0, 0, 0), interval end_time millisecond) as end_time
 from t1

Output: