Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> comp.databases.oracle -> 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. Received on Thu Feb 05 2004 - 00:51:50 CST