Results 1 to 4 of 4
  1. #1
    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?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'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 pseudo-table 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+(rownum-1)/24 as hour
        from all_objects
        where rownum <= (:end_time-:start_time)*24+1
      ) h
    ) x
    order by 1;
    * Edit: I suppose if I had noticed the title of your post was "Oracle Analytic Question" I might have had more idea what you were looking for!
    Last edited by andrewst; 12-19-05 at 08:10.

  3. #3
    Join Date
    Dec 2005
    Posts
    6
    Quote Originally Posted by andrewst
    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?
    Yes the application is just adding a row to the table if a count value has changed, if the count value doesn't change no row will be added.

    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.

  4. #4
    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, '1-nov-2003' as proc_dt from dual
    union select 8 as cnt, 4 as hrs, 'Team1' as team, 'Assoc 1' as assoc, '2-nov-2003' as proc_dt from dual
    union select 1 as cnt, 1 as hrs, 'Team1' as team, 'Assoc 1' as assoc, '2-nov-2003' as proc_dt from dual
    union select 4 as cnt, 3 as hrs, 'Team1' as team, 'Assoc 1' as assoc, '3-nov-2003' as proc_dt from dual
    union select 4 as cnt, 2 as hrs, 'Team1' as team, 'Assoc 2' as assoc, '3-nov-2003' as proc_dt from dual
    union select 6 as cnt, 4 as hrs, 'Team2' as team, 'Assoc 3' as assoc, '3-nov-2003' as proc_dt from dual
    union select 8 as cnt, 2 as hrs, 'Team2' as team, 'Assoc 4' as d2, '3-nov-2003' as proc_dt from dual
    union select 1 as cnt, 1 as hrs, 'Team2' as team, 'Assoc 4' as d2, '4-nov-2003' as proc_dt from dual
    )
    where team='Team1'
    group by proc_dt, team, assoc
    order by proc_dt, team, assoc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •