OraFAQ Forum: SQL & PL/SQL » repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repeat

Home » SQL & PL/SQL » SQL & PL/SQL » repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repeat

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repeat [message #161323]

Fri, 03 March 2006 04:46

jazzist
Messages: 11
Registered: March 2006

Junior Member

Hi,

I am using oracle personal 10g, and toad client, both within windows.

I have a table..

DATE NUMBER NUMBER2

I would like to use PL/SQL to produce an output showing the average of NUMBER grouped by NUMBER2 within a date period. This should then loop calculating the average within the same date limits, of the subsequent same length date period. Looping for a set number of times. E.g. within a 10 minute period find the average for each 1 minute window (I have a date entry for every second).

Any ideas?

I have tried setting a timestamp outside a for loop, which sets the time to start the calculations at. I then have a for loop, for i in 1..10 (i.e. 10 loops 10 minutes), inside the for loop i do.. where date > timestamp + ((i/24)/60), and date < timestamp + (((i+1)/24)/60). i.e. 1 minute start and 2 minute end is the window, then repeat at 2 min start, 3 min end.

my code executes for a few seconds but then returns no data found at line 12. line 12 is the select line...

if change the for loop, e.g. for i in 3..10 i get a buffer overflow 2000 warning. I don't see how ten lines can exceed the buffer. The same happens if I change the i in the where line to integers instead of i.

Anyone got any ideas of a more elegant way to do this? Or why I might be getting these errors?

Many thanks.

[Updated on: Fri, 03 March 2006 04:50]

Report message to a moderator

Re: repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repe [message #161326 is a reply to message #161323]

Fri, 03 March 2006 04:59

Frank
Messages: 7901
Registered: March 2000

Senior Member

Please provide some sample data and required results.
If you use code-tags in your reply, you can create an easy-to-read example of what you want to achieve.

Report message to a moderator

Re: repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repe [message #161327 is a reply to message #161323]

Fri, 03 March 2006 05:13

jazzist
Messages: 11
Registered: March 2006

Junior Member

Thanks for your reply. Apologies for not including some code.

Here is a sample of the data:

CALC_TIME	COUNT	CHANNEL	FRAMES	BYTES

08/02/2006 22:56:45	542	10	19	2004
08/02/2006 22:56:46	543	10	22	2145
08/02/2006 22:56:47	544	10	15	1370
08/02/2006 22:56:48	545	10	20	1743
08/02/2006 22:56:49	546	10	17	1910

This is the table create code:

CREATE TABLE TETHEREAL_OUTPUT
(
  CALC_TIME  DATE,
  COUNT      NUMBER,
  CHANNEL    NUMBER,
  FRAMES     NUMBER,
  BYTES      NUMBER
)

This code gets me the result I want, but only does it once, in plain SQL (not PL/SQL):

select avg(bytes) AS average,
	   min(bytes) AS min,
	   max(bytes) AS max,
	   channel AS wlan_channel
from TETHEREAL_OUTPUT
where CALC_time > to_date('2006-02-08 22:48:11', 'YYYY-MM-DD HH24:MI:SS')
and CALC_time < to_date('2006-02-8 23:58:11', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY channel
ORDER BY  channel;

An example output of this code is:

AVERAGE	MIN	MAX	WLAN_CHANNEL

4140.21487603306	1536	8851	1
681.428571428571	459	833	6
1189.33333333333	1173	1221	7
1553.89612676056	598	2853	10
8810.1843575419	6869	10018	11
4323.63305785124	1763	6721	12

I would like to run the same calculation, but do it multiple times. For example, between two date periods, find the average, min, max for a specific channel [i.e. group by channel] (and also for all channels together, i.e. not grouping by channel). Between these periods the average should be calculated multiple times, e.g. say I specifiy a ten minute window, I should also be ables to specifiy the window size within this, e.g. calculate the average for every 2 minutes within a 20 minutes window.

This is the non-working PL/SQL code as described in my first post:

DECLARE
       v_timestamp TETHEREAL_OUTPUT.CALC_TIME%type;
       v_min TETHEREAL_OUTPUT.BYTES%type;
       v_max TETHEREAL_OUTPUT.BYTES%type;
       v_avg TETHEREAL_OUTPUT.BYTES%type;
       v_channel TETHEREAL_OUTPUT.CHANNEL%type;
BEGIN
       v_timestamp := to_date('2006-02-10 12:30:14', 'YYYY-MM-DD HH24:MI:SS');
	   
FOR i IN 1..10 LOOP

   select avg(BYTES), min(BYTES), max(BYTES), CHANNEL
   into v_avg, v_min, v_max, v_channel
   from TETHEREAL_OUTPUT
   where CALC_time > v_timestamp + ((i/24)/60)
   and CALC_time < v_timestamp + (((i+1)/24)/60)
   GROUP BY channel;
   DBMS_OUTPUT.put_line(v_avg || v_min || v_max || v_channel);
END LOOP;

END;
/

Regards.

Report message to a moderator

Re: repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repe [message #161332 is a reply to message #161327]

Fri, 03 March 2006 05:40

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Don't have Oracle at home, so this syntax will be pure guess-work.

First, we need a function that will group rows into a 10 minute time period.

SUBSTR(TO_CHAR(CALC_TIME, 'YYYYMMDDHH24MI'),1,11)

should do the trick.

Now, put it in a basic SQL to give you the groupings by 10 minute period and channel:

select avg(bytes) AS average,
       min(bytes) AS min,
       max(bytes) AS max,
       SUBSTR(TO_CHAR(CALC_TIME, 'YYYYMMDDHH24MI'),1,11) AS grp
       channel AS wlan_channel
from   TETHEREAL_OUTPUT
where  CALC_time > :from
and    CALC_time < :to
GROUP BY SUBSTR(TO_CHAR(CALC_TIME, 'YYYYMMDDHH24MI'),1,11), channel
ORDER BY  4,5;

And to get the all-channels total line, use the ROLLUP clause:

select avg(bytes) AS average,
       min(bytes) AS min,
       max(bytes) AS max,
       SUBSTR(TO_CHAR(CALC_TIME, 'YYYYMMDDHH24MI'),1,11) AS grp
       channel AS wlan_channel
from   TETHEREAL_OUTPUT
where  CALC_time > :from
and    CALC_time < :to
GROUP BY SUBSTR(TO_CHAR(CALC_TIME, 'YYYYMMDDHH24MI'),1,11), ROLLUP(channel)
ORDER BY  4,5;

Again, apologies for the untested syntax.

_____________
Ross Leishman

Report message to a moderator

Re: repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repe [message #163838 is a reply to message #161323]

Mon, 20 March 2006 05:33

jazzist
Messages: 11
Registered: March 2006

Junior Member

belated thanks, that worked great.

I have since bought the SQL Cookbook and the SQL Pocket Guide books, which are proving useful!

Smile

Report message to a moderator

Re: repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repe [message #164398 is a reply to message #161323]

Thu, 23 March 2006 05:28

jazzist
Messages: 11
Registered: March 2006

Junior Member

No Message Body

[Updated on: Thu, 23 March 2006 05:59]

Report message to a moderator

Re: repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repe [message #186819 is a reply to message #161332]

Wed, 09 August 2006 13:02

jazzist
Messages: 11
Registered: March 2006

Junior Member

rleishman wrote on Fri, 03 March 2006 05:40

Don't have Oracle at home, so this syntax will be pure guess-work.

First, we need a function that will group rows into a 10 minute time period.

SUBSTR(TO_CHAR(CALC_TIME, 'YYYYMMDDHH24MI'),1,11)

should do the trick.

Is it possible to modify this so that the rows are grouped into a arbitrary time period?

It's simple to choose tens, e.g. for hours:

SUBSTR(TO_CHAR(CALC_TIME, 'YYYYMMDDHH24'),1,10)

but.. what about, say, 2 or 3 seconds?

Thanks.

edit: found this http://www.orafaq.com/forum/m/28892/83243/?srch=group+intervals#msg_28892 which looks similar.. Smile

[Updated on: Wed, 09 August 2006 13:19]

Report message to a moderator

Previous Topic:	DBMS_LOB.SUBSTR
Next Topic:	exluding records based on date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 01:28:03 CST 2024