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
No comments:
Post a Comment