Re: Analytical Functions Question

"Yuri G." <y-u-r-i-g_at_verizon.net> wrote in message news:<pan.2004.02.05.06.46.32.304323_at_verizon.net>...
> 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.

No analytical function needed, just some date arithmetic:

SQL> select * 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.

SQL> select trunc(tm) + floor((tm-trunc(tm))*24*60/10)/24/60*10, sum(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