Analytics.. can it help here?

[jodie]

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:

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:

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:

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:

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

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:

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

[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.


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