Results 1 to 4 of 4
Thread: Oracle analytic question....

121905, 07:01 #1Registered User
 Join Date
 Dec 2005
 Posts
 6
Unanswered: Oracle analytic question....
My problem is that an application is just adding changes to database,
if a value , at example 5 was added. Then no new row will be added until this value changing.
The problem here is, if I run a query to show the values for a specific time,
the result is not very usefull.
At example:
23:00 value 8
00:00 value 8
01:00 value 7
02:00 value 7
03:00 value 7
If I run now a queryfrom 00:00 to 03:00, I just reveive the following line as a result:
01:00 value 7
Caused that only the changes are added to the database
My goal is that I want have a result like:
00:00 value 8
01:00 value 7
02:00 value 7
03:00 value 7
Do you know how it is possible?

121905, 07:41 #2Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1I'm not sure I have fully understood what you are saying. You seem to be saying that you will only tell the database when the value changes, but you want the database to then tell you when the value was checked, whether it changed or not. How can it possibly know if you don't tell it?
Or do you mean that the query results should simply show every hour between the 2 you specify? In that case you need a table of hours to which you can join your values table. This can be a real table of hours or a pseudotable like this:
Code:select hour, (select v.value from values_table v where v.hour = x.latest_hour) latest_value from ( select h.hour, (select max(v.hour) from values_table v where v.hour <= h.hour ) as latest_hour from ( select :start_time+(rownum1)/24 as hour from all_objects where rownum <= (:end_time:start_time)*24+1 ) h ) x order by 1;
Last edited by andrewst; 121905 at 08:10.
Tony Andrews
http://tinyurl.com/tonyandrews

121905, 10:57 #3Registered User
 Join Date
 Dec 2005
 Posts
 6
Originally Posted by andrewst
01:00 count 6
03:00 count 7
If I want to create a query for 02:00 I won't receive a result, since the value has not changed at that time, but in fact the value is 6, you see?
I thought that is might be possible to use the lag funcion, but I don't know it exactly.

121905, 12:09 #4Registered User
 Join Date
 Jan 2005
 Location
 Green Bay
 Posts
 201
This Query is helpful for me to use analytics
This may help you find the solution you are looking for
If I understand what you are asking I think that the Rank funtion is what you are looking for.
I hope this helps
select proc_dt, team, assoc, sum(cnt) as Assoc_Cnt_Dt, sum(hrs) as Assoc_Hrs_Dt,
RANK() over (partition by proc_dt, team order by sum(cnt)/sum(hrs) desc) as Assoc_Rank_Dt,
DENSE_RANK() over (partition by proc_dt, team order by sum(cnt)/sum(hrs) desc) as Assoc_Dense_Rank_Dt,
row_number() over (partition by proc_dt, team order by sum(cnt)/sum(hrs) desc) as Assoc_RowNum_Dt,
sum(cnt)/sum(hrs) as Assoc_Rate_Dt,
sum(sum(cnt)) over (partition by assoc) / sum(sum(hrs)) over (partition by assoc) as Assoc_Rate_Ave,
percentile_cont(0.5) within group (order by sum(cnt)/sum(hrs)) over (partition by Assoc) as Assoc_Rate_Median,
sum(sum(cnt)) over (partition by assoc) as Assoc_Cnt_Tot,
sum(sum(hrs)) over (partition by assoc) as Assoc_Hrs_Tot,
sum(sum(cnt)) over (partition by proc_dt, team) as Team_Cnt_Dt,
sum(sum(hrs)) over (partition by Proc_dt,team) as Team_Hrs_Dt,
sum(sum(cnt)) over (partition by proc_dt,team) / sum(sum(hrs)) over (partition by proc_dt,team) as Team_Rate_Dt,
sum(sum(cnt)) over (partition by team) / sum(sum(hrs)) over (partition by team) as Team_Rate_Ave,
sum(sum(cnt)) over (partition by team) as Team_Cnt_Tot,
sum(sum(hrs)) over (partition by team) as Team_Hrs_Tot
from
(
select 2 as cnt, 5 as hrs, 'Team1' as team, 'Assoc 1' as assoc, '1nov2003' as proc_dt from dual
union select 8 as cnt, 4 as hrs, 'Team1' as team, 'Assoc 1' as assoc, '2nov2003' as proc_dt from dual
union select 1 as cnt, 1 as hrs, 'Team1' as team, 'Assoc 1' as assoc, '2nov2003' as proc_dt from dual
union select 4 as cnt, 3 as hrs, 'Team1' as team, 'Assoc 1' as assoc, '3nov2003' as proc_dt from dual
union select 4 as cnt, 2 as hrs, 'Team1' as team, 'Assoc 2' as assoc, '3nov2003' as proc_dt from dual
union select 6 as cnt, 4 as hrs, 'Team2' as team, 'Assoc 3' as assoc, '3nov2003' as proc_dt from dual
union select 8 as cnt, 2 as hrs, 'Team2' as team, 'Assoc 4' as d2, '3nov2003' as proc_dt from dual
union select 1 as cnt, 1 as hrs, 'Team2' as team, 'Assoc 4' as d2, '4nov2003' as proc_dt from dual
)
where team='Team1'
group by proc_dt, team, assoc
order by proc_dt, team, assoc