2015-07-24

SUM over time -Elements

A while ago wrote how to calculate sum over time. During overlapping time intervals the resulting sum is produced there. But how about if one needs the elements that the sum is generated from?
2015-07-25 2015-07-26 1
2015-07-26 2015-07-28 2
2015-07-27 2015-07-30 3
The testdata is generated in td view.
with td as (
 select trunc(sysdate) + mod(level, 4) validFrom
      , trunc(sysdate) + mod(level, 8) * 2 validTill
      , level a
   from dual
connect by level < 4
), b as (
  select row_number()over(order by case r when 1 then validFrom else validTill end,r desc) rn
       , a
       , r
   , case r when 1 then validFrom else validTill end validFrom
   , sum(case r when 1 then a else -a end) over (order by case r when 1 then validFrom else validTill end,r desc) sumover
   , sum(case r when 1 then 1 else -1 end) over (order by case r when 1 then validFrom else validTill end,r desc) cntover
   from td, (
  select rownum r from dual connect by level < 3
  )
),c (rn,a,r,validfrom,sumover,cntover,vals) as (
 select rn,a,r,validfrom,sumover,cntover,mdsys.SDO_NUMTAB(a) from b where rn = 1
union all
select b.rn,b.a,b.r,b.validFrom,b.sumover,b.cntover,case b.r when 1 then c.vals multiset union mdsys.SDO_NUMTAB(b.a) else c.vals multiset except mdsys.SDO_NUMTAB(b.a) end
  from b,c where b.rn = c.rn+1 
),d as (select max(cntover)over(partition by validFrom) mx,a,r,validFrom,sumover,cntover,(select count(*) from table(vals)) n,vals, count(*)over(partition by validFrom) cn 
  from c 
  where cntover=(select count(*) from table(vals))
) 
select validFrom
     , nvl(lead(validFrom) over (order by validFrom)
         , to_date('22000101','yyyymmdd')) validTill
     , sumover
     , (select listagg(column_value,'+')within group(order by column_value) from table(vals)) vals
  from d
 where cntover=mx
order by validFrom;

2015-07-25 2015-07-26 1 1
2015-07-26 2015-07-27 2 2
2015-07-27 2015-07-28 5 2+3
2015-07-28 2015-07-30 3 3
2015-07-30 2200-01-01 0 

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.