Imagine we have the following table where 0 is a start time, and 1 is an end time of the same kind x process.

I would like get as result:

In time order the first three events should be pone in start, end, next start column because following the way 0 = start, 1= end, and so on.

i did in this way but there is an error regarding next_start

SELECT kind, start, end, next_start 
FROM(
    SELECT kind, F.time as start, S.time as end, max(F.time) as next_start 
    from Table F
    INNER JOIN Table S
        ON F.kind = S.kind
    WHERE end>start
        AND next_start> end
    GROUP BY kind, start, end
)
order by next_start

CodePudding user response：

We can try to use SUM window function to get each start and end time grouping number, then use MIN and MAX aggregate function to get the start and end time.

Final, we can try to use LEAD window function to get next_start column.

Query 1:

SELECT kind,
       "start",
       "end",
       LEAD("start") OVER(ORDER BY "start") next_start
    FROM (
     SELECT kind,
           MIN("time") "start",
           MAX("time") "end"
    FROM (
        SELECT t1.*,
               SUM(CASE WHEN way = 0 THEN 1 ELSE 0 END) OVER(ORDER BY "time") grp
        FROM "Table" t1 
    ) t1
    GROUP BY kind,grp
) t1

Results:

| KIND |                 start |                   end |            NEXT_START |
|------|-----------------------|-----------------------|-----------------------|
|    x | 2022-06-15 12:12:34.0 | 2022-06-15 12:12:55.0 | 2022-06-15 12:15:22.0 |
|    x | 2022-06-15 12:15:22.0 | 2022-06-15 12:15:59.0 | 2022-06-15 12:16:07.0 |
|    x | 2022-06-15 12:16:07.0 | 2022-06-15 12:16:42.0 |                (null) |