Re: Analytical Functions Question

From: VC <boston103_at_hotmail.com>
Date: 11 Feb 2004 10:01:12 -0800
Message-ID: <31e0625e.0402111001.618b863f@posting.google.com>

Hello Yuri,

"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.
>

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>
> Thanks,
>
> Yuri.

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

VC Received on Wed Feb 11 2004 - 12:01:12 CST