CODEWITHSUNDEEP
sqloracle-databaseoracle11g
JohnHC
JohnHC

Reputation: 11195

Oracle aggregate specific groups based on sequence

I have a table T:

Entity  type  starttime    sequence  duration
1       A     2017010101   1         12
1       A     2017010102   2         11
1       A     2017010103   3         3
1       A     2017010104   4         1
1       A     2017010105   1         19
1       A     2017010106   2         18
2       A     2017010101   1         18
2       A     2017010102   1         100
3       A     2017010101   1         120

I need to aggregate the data so that each run of sequence has a total duration and the first starttime:

Entity  type  starttime    sequence  duration
1       A     2017010101   1         27
1       A     2017010105   1         37
2       A     2017010101   1         18
2       A     2017010102   1         100
3       A     2017010101   1         120

I believe this is a gaps-and-islands problem, but I can't quite figure it out...

I have tried to use a lead() over (partition by entity order by sequence) but this keeps grabbing the next run of sequence.

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You don't need row_number() for this. You can just subtract the sequence from the starttime -- assuming starttime is a date. The difference is constant for each group of sequential values:

select entity, type, min(starttime) as starttime, 
       min(sequence) as sequence, sum(duration) as duration
from t
group by entity, type, (starttime - sequence)
order by entity, grp;

If starttime is a string then you need row_number() as Ponder suggests. If starttime is a number, then this works within a single month, but you probably want row_number().

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

If sequence has no gaps then you can use row_number() and subtract sequence to create temporary column grp used next for aggregation:

select entity, type, min(starttime) starttime, 
       min(sequence) sequence, sum(duration) duration
  from (select t.*, 
               row_number() over (partition by entity order by starttime) - sequence grp 
          from t)
  group by entity, type, grp
  order by entity, grp

Test:

with t(entity, type, starttime, sequence, duration) as (
    select 1, 'A', 2017010101, 1,  12 from dual union all
    select 1, 'A', 2017010102, 2,  11 from dual union all
    select 1, 'A', 2017010103, 3,   3 from dual union all
    select 1, 'A', 2017010104, 4,   1 from dual union all
    select 1, 'A', 2017010105, 1,  19 from dual union all
    select 1, 'A', 2017010106, 2,  18 from dual union all
    select 2, 'A', 2017010101, 1,  18 from dual union all
    select 2, 'A', 2017010102, 1, 100 from dual union all
    select 3, 'A', 2017010101, 1, 120 from dual )
select entity, type, min(starttime) starttime, 
       min(sequence) sequence, sum(duration) duration
  from (select t.*, 
               row_number() over (partition by entity order by starttime) - sequence grp 
          from t)
  group by entity, type, grp
  order by entity, grp



    ENTITY TYPE  STARTTIME   SEQUENCE   DURATION
---------- ---- ---------- ---------- ----------
         1 A    2017010101          1         27
         1 A    2017010105          1         37
         2 A    2017010101          1         18
         2 A    2017010102          1        100
         3 A    2017010101          1        120

Upvotes: 2

Related Questions