[SOLVED] how to split time range rows to serials second level rows

how to split time range rows to serials second level rows

for example, in my clickhouse instance, there is 4 rows

uuid  |  time     | counter
333   | 13:13:13  | 13
333   | 13:13:15  | 133

3333  | 23:23:23  | 23
3333  | 23:23:25  | 233

how to split the 2 time range rows to serials second level rows, such as

uuid  |  time     | counter
333   | 13:13:13  | (133-13)/3
333   | 13:13:14  | (133-13)/3
333   | 13:13:15  | (133-13)/3

3333  | 23:23:23  | (233-23)/3
3333  | 23:23:23  | (233-23)/3
3333  | 23:23:25  | (233-23)/3

scan the clickhouse time serials function, but not found the solution.

Solution

Try this way:

WITH data AS
    (
        SELECT *
        FROM VALUES('uuid UInt32, time DateTime, counter UInt32', 
          (333,  '2000-01-01 13:13:13', 13), 
          (333,  '2000-01-01 13:13:15', 133), 
          (3333, '2000-01-01 23:23:23', 23), 
          (3333, '2000-01-01 23:23:25', 233))
    )
SELECT
    uuid,
    from_time + offset AS time,
    value AS counter
FROM
(
    SELECT
        uuid,
        min(time) AS from_time,
        (max(time) - from_time) + 1 AS lag_sec,
        (max(counter) - min(counter)) / lag_sec AS value
    FROM data
    GROUP BY
        uuid,
        toStartOfMinute(time)
)
ARRAY JOIN range(lag_sec) AS offset

/*
┌─uuid─┬────────────────time─┬─counter─┐
│  333 │ 2000-01-01 13:13:13 │      40 │
│  333 │ 2000-01-01 13:13:14 │      40 │
│  333 │ 2000-01-01 13:13:15 │      40 │
│ 3333 │ 2000-01-01 23:23:23 │      70 │
│ 3333 │ 2000-01-01 23:23:24 │      70 │
│ 3333 │ 2000-01-01 23:23:25 │      70 │
└──────┴─────────────────────┴─────────┘
*/