Oracle aggregate specific groups based on sequence

Question

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.

Don't have an oracle connection to hand to test the write of it, but a recursive CTE / Connect By (depending on Oracle version, 11gr1 / r2), select the rows with sequence = 1, grouped by entity as the anchor and then recurse based on sequence = sequence + 1 and date = date +1 summing the values as you go along. In your data above I think the line 2 A 2017010102 1 100 is meant to be 2 A 2017010102 2 100 — Andrew
– Andrew, Commented Oct 25, 2017 at 9:17
@Andrew That line was wrong, but for another reason... Edit made — JohnHC
– JohnHC, Commented Oct 25, 2017 at 9:25

Ponder Stibbons · Accepted Answer · 2017-10-25 09:28:57Z

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

Gordon Linoff · Accepted Answer · 2017-10-25 10:48:15Z

0

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().

answered Oct 25, 2017 at 10:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle aggregate specific groups based on sequence

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related