Gaps and Islands with continuous ranges from previous row-CodePudding

I have the following set of Data

SET	START	END	QTY
A	1	10	10
A	11	20	10
A	21	30	10
B	51	60	10
B	61	70	10
B	81	90	10
B	91	100	10
C	101	200	100
C	201	300	100
C	401	500	100

And wanted to have the following result:

SET	START	END	TOTAL_QTY
A	1	30	30
B	51	70	20
B	81	100	20
C	101	300	200
C	401	500	100

So it will check previous "End" range and if it's a continuous from the previous "Start" range then it will be grouped into one "Start - End" ranges with the Sum of Qty. I don't know how this can be achieved with Oracle SQL, can anyone help?

CodePudding user response：

select   "SET"
         ,min("START") as "START"
         ,max("END")   as "END"
         ,sum(QTY)     as  QTY
from     (
         select   t.*
                  ,count(mrk) over(partition by "SET" order by "START") as grp
         from    (
                 select  t.*
                        ,case when "START" - lag("END") over(partition by "SET" order by "START") > 1 then 1 end as mrk
                 from    t
                 ) t
         ) t

group by "SET", grp

SET	START	END	QTY
A	1	30	30
B	51	70	20
B	81	100	20
C	101	300	200
C	401	500	100

Fiddle

SET	START	END	QTY
A	1	10	10
A	11	20	10
A	21	30	10
B	51	60	10
B	61	70	10
B	81	90	10
B	91	100	10
C	101	200	100
C	201	300	100
C	401	500	100

SET	START	END	QTY
A	1	10	10
A	11	20	10
A	21	30	10
B	51	60	10
B	61	70	10
B	81	90	10
B	91	100	10
C	101	200	100
C	201	300	100
C	401	500	100

SET	START	END	QTY
A	1	10	10
A	11	20	10
A	21	30	10
B	51	60	10
B	61	70	10
B	81	90	10
B	91	100	10
C	101	200	100
C	201	300	100
C	401	500	100