Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    USA
    Posts
    2

    Red face Unanswered: Analytical Functions Question

    Hi,

    I'm trying to come up with a solution to seemingly simple database query,
    which I'm sure could be done with Oracle9 analytical functions, but
    somehow the solution is elusive:

    I have a table of the following structure:

    create table values (
    ...
    val NUMERIC(10),
    tm TIMESTAMP
    ...
    );

    (only relevant fields are shown)

    Records are constantly inserted into table with tm equal SYSDATE at the
    time of insertion.

    I need a query which will produce SUM(val) over time period of last N
    minutes partitioned by M minutes with total for each period of M as well
    as running total.
    For example, if N = 60 min and M = 10 min it will be SUM(val) for every 10
    minute interval within last hour as well as running total of this sums.

    Could anybody point me to an example or tutorial for using Oracle
    analytical functions. - This is not a homework. I'm sure that this is
    easily done with analytical functions, but I can't get the time window
    right and always get sum for 10 minutes relative to current point instead
    of interval between the fixed values. Oracle documentation is a little
    obscure with regards to analytical functions and all the examples are
    about the running totals of salaries and such.

    Thanks,

    Yuri.

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: Analytical Functions Question

    Originally posted by yurigo
    Hi,

    I'm trying to come up with a solution to seemingly simple database query,
    which I'm sure could be done with Oracle9 analytical functions, but
    somehow the solution is elusive:

    I have a table of the following structure:

    create table values (
    ...
    val NUMERIC(10),
    tm TIMESTAMP
    ...
    );

    (only relevant fields are shown)

    Records are constantly inserted into table with tm equal SYSDATE at the
    time of insertion.

    I need a query which will produce SUM(val) over time period of last N
    minutes partitioned by M minutes with total for each period of M as well
    as running total.
    For example, if N = 60 min and M = 10 min it will be SUM(val) for every 10
    minute interval within last hour as well as running total of this sums.

    Could anybody point me to an example or tutorial for using Oracle
    analytical functions. - This is not a homework. I'm sure that this is
    easily done with analytical functions, but I can't get the time window
    right and always get sum for 10 minutes relative to current point instead
    of interval between the fixed values. Oracle documentation is a little
    obscure with regards to analytical functions and all the examples are
    about the running totals of salaries and such.

    Thanks,

    Yuri.
    You can simple assign an id to each record based on distance to sysdate,
    by using mod(,:m) function in a subquery then group the resultset of the subquery by rollup(id). Records with a certain interval will be assigned the same id.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Analytical Functions Question

    I just posted a solution on comp.databases.oracle, but since that won't become visible until the next new moon I'll paste it here:
    PHP Code:
    SQLselect from valuet;

    TM                          VAL
    -------------------- ----------
    05-FEB-2004 14:14:43          1
    05
    -FEB-2004 14:15:43          2
    05
    -FEB-2004 14:16:43          3
    05
    -FEB-2004 14:17:43          4
    05
    -FEB-2004 14:18:43          5
    05
    -FEB-2004 14:19:43          6
    05
    -FEB-2004 14:20:43          7
    05
    -FEB-2004 14:21:43          8
    05
    -FEB-2004 14:22:43          9
    05
    -FEB-2004 14:23:43         10
    05
    -FEB-2004 14:24:43         11
    05
    -FEB-2004 14:25:43         12
    05
    -FEB-2004 14:26:43         13
    05
    -FEB-2004 14:27:43         14

    14 rows selected
    .

    SQLselect trunc(tm) + floor((tm-trunc(tm))*24*60/10)/24/60*10sum(val)
      
    2  from valuet
      3  where tm 
    >= sysdate-1/24 -- Last 60 minutes
      4  group by trunc
    (tm) + floor((tm-trunc(tm))*24*60/10)/24/60*10;

    TRUNC(TM)+FLOOR((TM-   SUM(VAL)
    -------------------- ----------
    05-FEB-2004 14:10:00         21
    05
    -FEB-2004 14:20:00         84 

  4. #4
    Join Date
    Feb 2004
    Location
    USA
    Posts
    2
    Thanks for your help - it works like a charm and without analytical functions too.

    Yuri

Posting Permissions

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