Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Analytical Functions Question

Analytical Functions Question

From: Yuri G. <y-u-r-i-g_at_verizon.net>
Date: Thu, 05 Feb 2004 06:51:50 GMT
Message-Id: <pan.2004.02.05.06.46.32.304323@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. Received on Thu Feb 05 2004 - 00:51:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US