Analytics.. can it help here?

Okay, I'm new to the whole analytics thing... but getting my feet wet.

Here's the complex problem. I have a table that has date, table_name, and % changes in the table for that day (gathered from dba_tab_modifications each day). I'll post the sql down below.

What I want to know is which tables will hit the 10% (.10) mark... and on what day. Here's my issue. Assume the table does 8% on day 1, 2, 3 & 4 . I want the report to say:

Code:

---

TABLE_OWNER TABLE_NAME    MONCHG    TUECHG    WEDCHG    THURCHG
----------- ---------- ---------- ---------- ---------- ----------
SCOTT      DEPT                        .16                  .16
SCOTT      EMP              1.94      1.94      1.94      1.94

---

Here's what the report says now:

Code:

---


TABLE_OWNER TABLE_NAME    MONCHG    TUECHG    WEDCHG    THURCHG
----------- ---------- ---------- ---------- ---------- ----------
SCOTT      DEPT                        .16        .24        .32
SCOTT      EMP              1.94      1.94      1.94      1.94

---

I have it doing the cummulative calculation now, but I don't know how to get it to "reset" the cummulative counter to 0 after I hit 10%.

Here's my code:
To create the table:

Code:

---

create table our_tab_modifications (
 dt                    date,
 TABLE_OWNER            VARCHAR2(30),
 TABLE_NAME            VARCHAR2(30),
 DELTA                        NUMBER)
;

insert into our_tab_modifications  values (to_date('2007-04-02','YYYY-MM-DD'),'SCOTT','EMP',1.94);
insert into our_tab_modifications  values (to_date('2007-04-03','YYYY-MM-DD'),'SCOTT','EMP',1.94);
insert into our_tab_modifications  values (to_date('2007-04-04','YYYY-MM-DD'),'SCOTT','EMP',1.94);
insert into our_tab_modifications  values (to_date('2007-04-05','YYYY-MM-DD'),'SCOTT','EMP',1.94);
insert into our_tab_modifications  values (to_date('2007-04-02','YYYY-MM-DD'),'SCOTT','DEPT',0.08);
insert into our_tab_modifications  values (to_date('2007-04-03','YYYY-MM-DD'),'SCOTT','DEPT',0.08);
insert into our_tab_modifications  values (to_date('2007-04-04','YYYY-MM-DD'),'SCOTT','DEPT',0.08);
insert into our_tab_modifications  values (to_date('2007-04-05','YYYY-MM-DD'),'SCOTT','DEPT',0.08);

---

Intermediate Query:

Code:

---

with deltas as (
  select  dt, table_owner, table_name,
          trim(to_char(dt, 'Day')) Weekday,
          delta
  from    our_tab_modifications
  where  dt > trunc(sysdate) - 5
    and  delta > 0
  )
select    dt, table_owner, table_name, weekday, delta,
          sum(delta) over (partition by table_owner, table_name
                            order by dt RANGE UNBOUNDED PRECEDING) Cummulative
from    deltas
;

DT        TABLE_OWNE TABLE_NAME WEEKDAY        DELTA CUMMULATIVE
--------- ---------- ---------- --------- ---------- -----------
02-APR-07 SCOTT      DEPT      Monday          .08        .08
03-APR-07 SCOTT      DEPT      Tuesday          .08        .16
04-APR-07 SCOTT      DEPT      Wednesday        .08        .24
05-APR-07 SCOTT      DEPT      Thursday        .08        .32
02-APR-07 SCOTT      EMP        Monday          1.94        1.94
03-APR-07 SCOTT      EMP        Tuesday        1.94        3.88
04-APR-07 SCOTT      EMP        Wednesday      1.94        5.82
05-APR-07 SCOTT      EMP        Thursday        1.94        7.76

8 rows selected.

---

Final Query

Code:

---

with deltas as (
  select  dt, table_owner, table_name,
          trim(to_char(dt, 'Day')) Weekday,
          delta
  from    our_tab_modifications
  where  dt > trunc(sysdate) - 5
    and  delta > 0
  )
,
mid_point  as (
select    dt, table_owner, table_name, weekday, delta,
          sum(delta) over (partition by table_owner, table_name
                            order by dt RANGE UNBOUNDED PRECEDING) Cummulative
from    deltas
)
,
summary as (
select  table_owner, table_name, weekday,
        case when delta > .1 then delta
            when Cummulative > .1 then cummulative
            else null
        end change
from mid_point
where cummulative > .1
)
select  table_owner, table_name,
        sum(case when Weekday = 'Monday' then change else null end) MonChg,
        sum(case when Weekday = 'Tuesday' then change else null end) TueChg,
        sum(case when Weekday = 'Wednesday' then change else null end) WedChg,
        sum(case when Weekday = 'Thursday' then change else null end) ThurChg
from summary
group by table_owner, table_name
order by table_owner, table_name


TABLE_OWNER TABLE_NAME    MONCHG    TUECHG    WEDCHG    THURCHG
----------- ---------- ---------- ---------- ---------- ----------
SCOTT      DEPT                        .16        .24        .32
SCOTT      EMP              1.94      1.94      1.94      1.94

---

Again, what I want:

Code:

---

TABLE_OWNER TABLE_NAME    MONCHG    TUECHG    WEDCHG    THURCHG
----------- ---------- ---------- ---------- ---------- ----------
SCOTT      DEPT                        .16                  .16
SCOTT      EMP              1.94      1.94      1.94      1.94

---

I'm sure some analytic function will help here... just not sure what.

Any help is appreciated!

Thanks!
Jodie

Well, I saw that there were a lot of views on this.. but no posts. So I thought I'd post my solution. It's ugly... but it works. It uses MULTIPLE in_line views with analytics. I also did some stuff where I keep track of analyze times to get a report of what the analyze TIME would be for each day. If anyone is interested, PM me and I'll send it to you.

Code:

---

col table_owner for a15
set lines 120
with deltas as
(select  dt, table_owner, table_name,
        trim(to_char(dt, 'Day')) Weekday,
        delta
  from  our_tab_modifications
 where  dt > trunc(sysdate-to_char(sysdate,'D')+1)
),
totals as
(
select deltas.*,
        sum(delta) over(partition by table_owner, table_name order by dt) Total
from deltas
),
mon_tues as
( select totals.*,
        case weekday when 'Monday' then delta end Mon,
        case weekday when 'Tuesday' then
                (case when delta >=.1 then delta else total end )
        end Tues
from totals
),
weds as
( select mon_tues.*,
        case weekday when 'Wednesday' then
                (case when lag(Tues) over (partition by table_owner, table_name order by dt) >=.1 then delta
                            when lag(Tues) over (partition by table_owner, table_name order by dt) is null the
n delta
                      else delta + lag(Tues) over (partition by table_owner, table_name order by dt)
                end )
        end Weds
from mon_tues
),
thurs as
( select weds.*,
        case weekday when 'Thursday' then
                (case when lag(Weds) over (partition by table_owner, table_name order by dt) >= .1 then delta
                            when lag(Weds) over (partition by table_owner, table_name order by dt) is null the
n delta
                      else delta + lag(Weds) over (partition by table_owner, table_name order by dt)
                end )
        end Thurs
from Weds
),
fri as
( select Thurs.*,
        case weekday when 'Friday' then
                (case when lag(Thurs) over (partition by table_owner, table_name order by dt) >= .1 then delta
                            when lag(Thurs) over (partition by table_owner, table_name order by dt) is null th
en delta
                      else delta + lag(Thurs) over (partition by table_owner, table_name order by dt)
                end )
        end Fri
from Thurs
),
Sat as
(select Fri.*,
        case weekday when 'Saturday' then
                (case when lag(Fri) over (partition by table_owner, table_name order by dt) >= .1 then delta
                            when lag(Fri) over (partition by table_owner, table_name order by dt) is null then
 delta
                      else delta + lag(Fri) over (partition by table_owner, table_name order by dt)
                end )
        end Sat
from Fri
),
summary as
(select Sat.*
from Sat
where Mon >= .1
  or Tues >= .1
  or Weds >= .1
  or Thurs >= .1
  or Fri >= .1
  or Sat >= .1
)
select table_owner, table_name,
        sum(case when Weekday = 'Monday' then Mon else null end ) Monday,
        sum(case when Weekday = 'Tuesday' then Tues else null end ) Tuesday,
        sum(case when Weekday = 'Wednesday' then Weds else null end ) Wednesday,
        sum(case when Weekday = 'Thursday' then Thurs else null end ) Thursday,
        sum(case when Weekday = 'Friday' then Fri else null end ) Friday,
        sum(case when Weekday = 'Saturday' then Sat else null end ) Saturday
from Summary
group by table_owner, table_name
order by table_owner, table_name;

---

Thanks!
Jodie