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.

"Yuri G." <y-u-r-i-g (AT) verizon (DOT) net> wrote

Hi, Yuri

You want only running totals of the sums, right?
You do not want to see every record with running totals.
So then you first want to use an ordinary group by to create your
sums, and then use analytics to create your running totals.

Somewhat like this :


select
tm10min,
sum_value,
sum(sum_value) over (order by tm10min rows unbounded preceding)
total
from
(
select
trunc(tm,'HH24')+trunc(to_number(to_char(tm,'MI')) ,-1)/(24*60)
tm10min,
sum(val) sum_value
from values
where tm between <whatever period you want>
group by trunc(tm,'HH24')+trunc(to_number(to_char(tm,'MI')) ,-1)/(24*60)
) s1


An inner select uses normal group by to get the sums.
The outer select uses analytics to create running totals.


KiBeHa

"Yuri G." <y-u-r-i-g (AT) verizon (DOT) net> wrote

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

Privet Yuri,

I'd create a permanent calendar table, containing a row for each minute, like this:
NUM TIME_FROM TIME_TO
1 02/04/2003 00:00:00 02/04/2003 00:00:01
2 02/04/2003 00:00:01 02/04/2003 00:00:02
....
60 02/04/2003 00:00:59 02/04/2003 00:01:00

and so on

That done, it would be easy to write the query you are asking about

here are DB2 links, the syntax seems to be the same:
http://www-106.ibm.com/developerwork.../0110lyle.html
http://www-106.ibm.com/developerwork...0401kuznetsov/

ak_tiredofspam (AT) yahoo (DOT) com (AK) wrote in message news:<46e627da.0402050649.5a9ecaaf (AT) posting (DOT) google.com>...

Utter drivel...
That is just about the worst advice I have ever seen posted- even I can see that.

Hello Yuri,


"Yuri G." <y-u-r-i-g (AT) verizon (DOT) net> wrote


You can do it like this:

SQL> create table test (tm date, val int);
SQL> insert into test select sysdate-dbms_random.value/12,
round(dbms_random.value*1000) from all_objects where rownum <= 20;

20 rows created.

SQL> select * from test;

TM VAL
------------------- ----------
2004-02-11 12:33:38 685
2004-02-11 12:41:47 36
2004-02-11 12:04:26 885
2004-02-11 11:13:59 343
2004-02-11 12:49:04 610
2004-02-11 11:53:43 332
2004-02-11 11:55:26 625
2004-02-11 11:19:26 536
2004-02-11 11:19:47 269
2004-02-11 10:57:58 39
2004-02-11 11:13:55 126
2004-02-11 12:22:28 64
2004-02-11 12:06:35 948
2004-02-11 10:51:20 424
2004-02-11 12:06:54 80
2004-02-11 12:40:25 491
2004-02-11 12:50:54 600
2004-02-11 12:48:07 417
2004-02-11 11:00:48 763
2004-02-11 11:46:36 685

20 rows selected.

SQL>
SQL> select
2 tm,
3 val,
4 trunc(tm, 'hh24') + (trunc(to_char(tm,'mi')/10)*10)/24/60
ten_min,
5 sum(val) over (partition by trunc(tm, 'hh24') +
(trunc(to_char(tm,'mi')/10)*10)/24/60) ten_min_sum,
6 sum(val) over (order by tm) running_sum
7 from test order by tm;

TM VAL TEN_MIN TEN_MIN_SUM
RUNNING_SUM
------------------- ---------- ------------------- -----------
-----------
2004-02-11 10:51:20 424 2004-02-11 10:50:00 463
424
2004-02-11 10:57:58 39 2004-02-11 10:50:00 463
463
2004-02-11 11:00:48 763 2004-02-11 11:00:00 763
1226
2004-02-11 11:13:55 126 2004-02-11 11:10:00 1274
1352
2004-02-11 11:13:59 343 2004-02-11 11:10:00 1274
1695
2004-02-11 11:19:26 536 2004-02-11 11:10:00 1274
2231
2004-02-11 11:19:47 269 2004-02-11 11:10:00 1274
2500
2004-02-11 11:46:36 685 2004-02-11 11:40:00 685
3185
2004-02-11 11:53:43 332 2004-02-11 11:50:00 957
3517
2004-02-11 11:55:26 625 2004-02-11 11:50:00 957
4142
2004-02-11 12:04:26 885 2004-02-11 12:00:00 1913
5027
2004-02-11 12:06:35 948 2004-02-11 12:00:00 1913
5975
2004-02-11 12:06:54 80 2004-02-11 12:00:00 1913
6055
2004-02-11 12:22:28 64 2004-02-11 12:20:00 64
6119
2004-02-11 12:33:38 685 2004-02-11 12:30:00 685
6804
2004-02-11 12:40:25 491 2004-02-11 12:40:00 1554
7295
2004-02-11 12:41:47 36 2004-02-11 12:40:00 1554
7331
2004-02-11 12:48:07 417 2004-02-11 12:40:00 1554
7748
2004-02-11 12:49:04 610 2004-02-11 12:40:00 1554
8358
2004-02-11 12:50:54 600 2004-02-11 12:50:00 600
8958

20 rows selected.



..... or :


SQL> select ten_min, ten_min_sum, sum(ten_min_sum) over(order by
ten_min) runnin
g_sum from (
2 select distinct
3 trunc(tm, 'hh24') + (trunc(to_char(tm,'mi')/10)*10)/24/60
ten_min,
4 sum(val) over (partition by trunc(tm, 'hh24') +
(trunc(to_char(tm,'mi')
/10)*10)/24/60) ten_min_sum
5 from test)
6 order by 1;

TEN_MIN TEN_MIN_SUM RUNNING_SUM
------------------- ----------- -----------
2004-02-11 10:50:00 463 463
2004-02-11 11:00:00 763 1226
2004-02-11 11:10:00 1274 2500
2004-02-11 11:40:00 685 3185
2004-02-11 11:50:00 957 4142
2004-02-11 12:00:00 1913 6055
2004-02-11 12:20:00 64 6119
2004-02-11 12:30:00 685 6804
2004-02-11 12:40:00 1554 8358
2004-02-11 12:50:00 600 8958

10 rows selected.

SQL>
....depending on what you mean by the running sum...

VC