add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (
main_id int,
start_time int,
end_time int);
insert into t values
(1,1467695616,1467695676),
(2,1467695677,1467695683),
(3,1467782122,1467782182),
(4,1467782181,1467782238),
(5,1472329347,1472329374),
(6,1472329375,1472329553),
(7,1472329554,1472329733),
(8,1472329734,1472329764),
(9,1472329949,1472330078),
(10,1472330275,1472330453),
(11,1472330454,1472330479);
set @latest = 0;
set @group = 0;
select
@group := case when abs(start_time - @latest) <= 2
then @group
else @group + 1
end "group",
main_id,
start_time,
@latest := end_time "end_time"
from t
order by end_time,start_time
group main_id start_time end_time
1 1 1467695616 1467695676
1 2 1467695677 1467695683
2 3 1467782122 1467782182
2 4 1467782181 1467782238
3 5 1472329347 1472329374
3 6 1472329375 1472329553
3 7 1472329554 1472329733
3 8 1472329734 1472329764
4 9 1472329949 1472330078
5 10 1472330275 1472330453
5 11 1472330454 1472330479
with CTE as
(select
case when abs(start_time - lag(end_time) over(order by end_time, start_time)) > 2 then 1 else 0 end diff_previous,
main_id,
start_time,
end_time
from t
order by end_time,start_time
)
select
1 + sum(diff_previous) over(order by end_time) as "group",
main_id,
start_time,
end_time
from CTE
order by end_time, start_time
group main_id start_time end_time
1 1 1467695616 1467695676
1 2 1467695677 1467695683
2 3 1467782122 1467782182
2 4 1467782181 1467782238
3 5 1472329347 1472329374
3 6 1472329375 1472329553
3 7 1472329554 1472329733
3 8 1472329734 1472329764
4 9 1472329949 1472330078
5 10 1472330275 1472330453
5 11 1472330454 1472330479