| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Analytical Functions Question
"Yuri G." <y-u-r-i-g_at_verizon.net> a écrit dans le message de
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.
>
I know this is not what you're searching but this may help you. (I only have a 8.1.7 so no analytical function, no timestamp)
SQL> -- N partition period, M sum window, O display period SQL> def M = 10 SQL> def N = 60 SQL> def O = 300 SQL> create table test (tm date, val number(10));
Table created.
SQL> -- Insert 30 random rows over the last 2 hours SQL> exec dbms_random.seed (to_number(to_char(sysdate,'J')));
PL/SQL procedure successfully completed.
SQL> insert into test (
2 select sysdate-dbms_random.value/12, round(dbms_random.value*1000)
3 from dba_objects
4 where rownum <= 30 );
30 rows created.
SQL> commit;
Commit complete.
SQL> select * from test order by tm;
TM VAL ------------------- ---------- 02/11/2004 05:31:42 467 02/11/2004 05:44:15 663 02/11/2004 05:47:21 844 02/11/2004 06:06:06 312 02/11/2004 06:08:12 643 02/11/2004 06:12:25 756 02/11/2004 06:19:46 710 02/11/2004 06:24:13 781 02/11/2004 06:30:17 116 02/11/2004 06:34:34 18 02/11/2004 06:36:14 553 02/11/2004 06:39:13 203 02/11/2004 06:40:33 8 02/11/2004 06:42:14 626 02/11/2004 06:46:53 875 02/11/2004 06:47:18 24 02/11/2004 06:50:19 313 02/11/2004 06:52:38 635 02/11/2004 06:53:15 698 02/11/2004 06:54:51 375 02/11/2004 06:55:59 645 02/11/2004 07:02:06 855 02/11/2004 07:02:13 405 02/11/2004 07:08:54 547 02/11/2004 07:16:18 581 02/11/2004 07:18:43 206 02/11/2004 07:19:34 363 02/11/2004 07:22:52 683 02/11/2004 07:23:17 32 02/11/2004 07:24:22 157
30 rows selected.
SQL> col period format a19 heading "Time" SQL> col val1 format 9G999G999G999 heading "Sum last &M mn" SQL> col val2 format 9G999G999G999 heading "Sum last &N mn" SQL> select cnt.period+&M/1440 period, 2 nvl(sm.val,0) val1, 3 nvl(sum(test.val),0) val2 4 from ( select trunc(sysdate,'HH')+2/24-&M*rownum/1440 period 5 from (Select 1 from dual group by cube(1,2,3,4,5)) 6 where rownum <= &O/&M 7 order by rownum desc ) cnt, 8 ( select trunc((to_number(to_char(tm,'J'))*86400+to_number(to_char(tm,'SSSSS')))/(&M*60)) 9 period, 10 sum(val) val 11 from test 12 group by trunc((to_number(to_char(tm,'J'))*86400 13 +to_number(to_char(tm,'SSSSS')))/(&M*60)) ) sm, 14 test 15 where to_date(floor(sm.period(+)*&M/1440),'J')+mod(sm.period(+)*&M,1440)/1440 16 = cnt.period
Time Sum last 10 mn Sum last 60 mn ------------------- -------------- -------------- 02/11/2004 04:10:00 0 0 02/11/2004 04:20:00 0 0 02/11/2004 04:30:00 0 0 02/11/2004 04:40:00 0 0 02/11/2004 04:50:00 0 0 02/11/2004 05:00:00 0 0 02/11/2004 05:10:00 0 0 02/11/2004 05:20:00 0 0 02/11/2004 05:30:00 0 0 02/11/2004 05:40:00 467 467 02/11/2004 05:50:00 1,507 1,974 02/11/2004 06:00:00 0 1,974 02/11/2004 06:10:00 955 2,929 02/11/2004 06:20:00 1,466 4,395 02/11/2004 06:30:00 781 5,176 02/11/2004 06:40:00 890 5,599 02/11/2004 06:50:00 1,533 5,625 02/11/2004 07:00:00 2,666 8,291 02/11/2004 07:10:00 1,807 9,143 02/11/2004 07:20:00 1,150 8,827 02/11/2004 07:30:00 872 8,918 02/11/2004 07:40:00 0 8,028 02/11/2004 07:50:00 0 6,495 02/11/2004 08:00:00 0 3,829 02/11/2004 08:10:00 0 2,022 02/11/2004 08:20:00 0 872 02/11/2004 08:30:00 0 0 02/11/2004 08:40:00 0 0 02/11/2004 08:50:00 0 0 02/11/2004 09:00:00 0 0
30 rows selected.
Regards
Michel Cadot
Received on Wed Feb 11 2004 - 00:44:50 CST
 |
 |