How to create timeline by adding same intervals x number of times?

I want to create a table with timeline data by repeatedly adding the same intervals for each id a certain # of times denoted by iterations (constant per id). My current table has the base repetition of this timeline.

Sample Input (iteration 1):

id    start_time         end_time           iterations
1     00:00:00.00000     00:00:00.05300     3
1     00:00:00.05300     00:00:00.07600     3

Desired Output:

id    start_time         end_time       
1     00:00:00.00000     00:00:00.05300
1     00:00:00.05300     00:00:00.07600
1     00:00:00.07600     00:00:00.12900
1     00:00:00.12900     00:00:00.15200 
1     00:00:00.15200     00:00:00.20500
1     00:00:00.20500     00:00:00.22800

Logic:

iteration 2: first end_time = 0.076 (0.053 - 0.000) = 0.129
iteration 2: second end_time = 0.129 (0.076 - 0.053) = 0.152
iteration 3: first end_time = 0.152 (0.053 - 0.000) = 0.205
iteration 3: second end_time = 0.205 (0.076 - 0.053) = 0.228

What I've Tried:

WITH cte AS (
SELECT
  *
  , SUM(TIME_DIFF(end_time, start_time, MILLISECOND)) 
      OVER (PARTITION BY id ORDER BY start_time 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS running_end_time
FROM my_table 
  , UNNEST(GENERATE_ARRAY(1, iterations))
)

SELECT
  id
  , CASE 
       WHEN MIN(aggregated_start_time) OVER (PARTITION BY id) = FIRST_VALUE 
         OVER (PARTITION BY id) THEN TIME(0, 0, 0)
       ELSE LAG(TIME(0, 0, 0), INTERVAL running_end_time MILLISECOND) 
         OVER (PARTITION BY id) END AS start_time
  , TIME_ADD(TIME(0, 0, 0), INTERVAL running_end_time MILLISECOND)
        AS end_time 
FROM cte

However, this doesn't work because although my cte creates the necessary number of records, it doesn't order them the right way which leads to adding the wrong intervals. Any help would really be appreciated!

CodePudding user response：

Consider below approach

select id, 
  time_add(start_time, interval repeat*step millisecond) as start_time,
  time_add(end_time, interval repeat*step millisecond) as end_time,
from (
  select *, 
    sum(time_diff(end_time, start_time, millisecond)) over(partition by id) step,
    max(end_time) over(partition by id) max_end_time
  from your_table
), unnest(generate_array(0, iterations - 1)) repeat

if applied to sample data in your question - output is

CodePudding user response：

Below query is based on my incomplete understading of your requiements focusing on implementing below logic:

first end_time = MAX(end_time) OVER (PARTITION BY id) (0.053 - 0.000) = 0.076 0.053 = 0.129
second end_time = MAX(end_time) OVER (PARTITION BY id) (0.053 - 0.000) (0.076 - 0.053)` = 0.129 0.023 = 0.152

WITH my_table AS (
  select 1 id, TIME '00:00:00.00000' start_time, TIME '00:00:00.05300' end_time, 2 iterations union all
  select 1, '00:00:00.05300', '00:00:00.07600', 2
),
my_table0 AS (
  SELECT *,
         TIME_DIFF(end_time, start_time, MILLISECOND) AS diff,
         MAX(end_time) OVER (PARTITION BY id) AS max_end_time,
    FROM my_table
)
SELECT id, start_time, end_time FROM my_table0
 UNION ALL
SELECT id,
       TIME_ADD(max_end_time, INTERVAL SUM(diff) OVER w - diff MILLISECOND) AS start_time,
       TIME_ADD(max_end_time, INTERVAL SUM(diff) OVER w MILLISECOND) AS end_time,
  FROM my_table0
WINDOW w AS (PARTITION BY id ORDER BY start_time)
 ORDER BY start_time
;

Query results:
 ----- ---- ----------------- ----------------- 
| Row | id |   start_time    |    end_time     |
 ----- ---- ----------------- ----------------- 
|   1 |  1 | 00:00:00        | 00:00:00.053000 |
|   2 |  1 | 00:00:00.053000 | 00:00:00.076000 |
|   3 |  1 | 00:00:00.076000 | 00:00:00.129000 |
|   4 |  1 | 00:00:00.129000 | 00:00:00.152000 |
 ----- ---- ----------------- -----------------