Re: Analytical Functions Question

"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>...
> create table values (
> ...
> val NUMERIC(10),
> tm TIMESTAMP
> ...
> );

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

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 Received on Thu Feb 05 2004 - 14:00:50 CET