Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Analytical Functions Question

Re: Analytical Functions Question

From: kibeha <kibeha_at_post6.tele.dk>
Date: 5 Feb 2004 05:00:50 -0800
Message-ID: <444b180d.0402050500.354a5be6@posting.google.com>


"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 - 07:00:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US