473,322 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Analytical Functions Question


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.
Jul 19 '05 #1
5 5963
"Yuri G." <y-*******@verizon.net> wrote in message news:<pa****************************@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
Jul 19 '05 #2
"Yuri G." <y-*******@verizon.net> wrote in message news:<pa****************************@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.


No analytical function needed, just some date arithmetic:

SQL> select * from valuet;

TM VAL
-------------------- ----------
05-FEB-2004 14:14:43 1
05-FEB-2004 14:15:43 2
05-FEB-2004 14:16:43 3
05-FEB-2004 14:17:43 4
05-FEB-2004 14:18:43 5
05-FEB-2004 14:19:43 6
05-FEB-2004 14:20:43 7
05-FEB-2004 14:21:43 8
05-FEB-2004 14:22:43 9
05-FEB-2004 14:23:43 10
05-FEB-2004 14:24:43 11
05-FEB-2004 14:25:43 12
05-FEB-2004 14:26:43 13
05-FEB-2004 14:27:43 14

14 rows selected.

SQL> select trunc(tm) + floor((tm-trunc(tm))*24*60/10)/24/60*10, sum(val)
2 from valuet
3 where tm >= sysdate-1/24 -- Last 60 minutes
4 group by trunc(tm) + floor((tm-trunc(tm))*24*60/10)/24/60*10;

TRUNC(TM)+FLOOR((TM- SUM(VAL)
-------------------- ----------
05-FEB-2004 14:10:00 21
05-FEB-2004 14:20:00 84
Jul 19 '05 #3
AK
Privet Yuri,

I'd create a permanent calendar table, containing a row for each minute, like this:
NUM TIME_FROM TIME_TO
1 02/04/2003 00:00:00 02/04/2003 00:00:01
2 02/04/2003 00:00:01 02/04/2003 00:00:02
....
60 02/04/2003 00:00:59 02/04/2003 00:01:00

and so on

That done, it would be easy to write the query you are asking about

here are DB2 links, the syntax seems to be the same:
http://www-106.ibm.com/developerwork.../0110lyle.html
http://www-106.ibm.com/developerwork...0401kuznetsov/
Jul 19 '05 #4
ak************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>...
Privet Yuri,

I'd create a permanent calendar table, containing a row for each minute, like this:
NUM TIME_FROM TIME_TO
1 02/04/2003 00:00:00 02/04/2003 00:00:01
2 02/04/2003 00:00:01 02/04/2003 00:00:02
...
60 02/04/2003 00:00:59 02/04/2003 00:01:00

and so on

That done, it would be easy to write the query you are asking about

here are DB2 links, the syntax seems to be the same:
http://www-106.ibm.com/developerwork.../0110lyle.html
http://www-106.ibm.com/developerwork...0401kuznetsov/

Utter drivel...
That is just about the worst advice I have ever seen posted- even I can see that.
Jul 19 '05 #5
VC
Hello Yuri,
"Yuri G." <y-*******@verizon.net> wrote in message news:<pa****************************@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
Jul 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: elena | last post by:
I have a test online that measures cognitive style. It determines whether the participant has an intuitive or analytical learning style. It takes about 5 minutes. It's here: http://www.elena.com...
99
by: David MacQuigg | last post by:
I'm not getting any feedback on the most important benefit in my proposed "Ideas for Python 3" thread - the unification of methods and functions. Perhaps it was buried among too many other less...
19
by: Ross A. Finlayson | last post by:
Hi, I hope you can help me understand the varargs facility. Say I am programming in ISO C including stdarg.h and I declare a function as so: void log_printf(const char* logfilename, const...
22
by: ypjofficial | last post by:
Is there any possibility of invoking the member functions of a class without creating an object (or even a pointer to ) of that class. eg. #include <iostream.h> class test { public: void...
2
by: Ajitha Devi | last post by:
My record set will look like this ENAme -Part Time- Dept qwe - Y - 10 fjj - N - 10 ghsd - N - 10 How to count using...
12
by: sam_cit | last post by:
Hi Everyone, I have few questions on inline functions, when i declare a function as inline, is it for sure that the compiler would replace the function call with the actual body of the function?...
13
by: JohnQ | last post by:
The implementation of classes with virtual functions is conceptually easy to understand: they use vtables. Which begs the question about POD structs: how are they associated with their member...
3
by: seenumahesh22 | last post by:
Hi, I have a doubt regarding a calculation by using analytical functions. ((1+r1)+(1+r2)+(1+r3)+....(1+r n) )*1000 where r1,r2,r3 are the values of a column in my table. I need to get the...
3
by: Yuri G. | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.