Grouping the start time and end time based on the gaps.
My input:
| ID | start_time | end_time |
| ------------ | ------------ | ------------ |
| 1 | 08:00:00 | 08:05:00 |
| 1 | 08:05:00 | 09:55:00 |
| 1 | 09:55:00 | 10:15:00 |
| 1 | 10:15:00 | 11:03:00 |
| 1 | 11:03:00 | 12:01:00 |
| 1 | 13:02:00 | 13:44:00 |
| 1 | 14:44:00 | 14:55:00 |
| 1 | 14:55:00 | 15:03:00 |
| 1 | 15:03:00 | 17:00:00 |
Output:
ID | start_time | end_time | real_start | real_end |
---|---|---|---|---|
1 | 08:00:00 | 08:05:00 | 08:00:00 | 12:01:00 |
1 | 08:05:00 | 09:55:00 | 08:00:00 | 12:01:00 |
1 | 09:55:00 | 10:15:00 | 08:00:00 | 12:01:00 |
1 | 10:15:00 | 11:03:00 | 08:00:00 | 12:01:00 |
1 | 11:03:00 | 12:01:00 | 08:00:00 | 12:01:00 |
1 | 13:02:00 | 13:44:00 | 13:02:00 | 13:44:00 |
1 | 14:44:00 | 14:55:00 | 14:44:00 | 17:00:00 |
1 | 14:55:00 | 15:03:00 | 14:44:00 | 17:00:00 |
1 | 15:03:00 | 17:00:00 | 14:44:00 | 17:00:00 |
To find real start and end time ,I need to get only three rows start and end time should change, if any gaps.
ID | real_start | real_end |
---|---|---|
1 | 08:00:00 | 12:01:00 |
1 | 13:02:00 | 13:44:00 |
1 | 14:44:00 | 17:00:00 |
2条答案
按热度按时间kd3sttzy1#
You can use the
LAG()
function to compare start times with prior end times to detect a gap. You can then assign groups by counting the number of preceding gaps using a runningSUM()
. Once group numbers are assigned,MIN()
andMAX()
functions can be used to calculate the "real start" and "real end" for each group.The following uses CTEs (common table expressions to build up the above logic leading to the final select.
The same can be accomplished using nested selects:
See this db<>fiddle for a working example with some extra data to show independent calculations per ID.
Results:
| ID | real_start | real_end |
| ------------ | ------------ | ------------ |
| 1 | 08:00:00 | 12:01:00 |
| 1 | 13:02:00 | 13:44:00 |
| 1 | 14:44:00 | 17:00:00 |
| 2 | 08:00:00 | 08:45:00 |
| 2 | 09:00:00 | 10:00:00 |
jmo0nnb32#
Assuming your data is in
CREATE TABLE times (ID int, start_time time(7), end_time time(7))
and ID changes with each day, and you want your results inCREATE TABLE real_times (ID int, real_start time(7), real_end time(7))
then this is how you could do it: