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: