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.
5  5991 
"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_numb er(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_numb er(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
"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*6 0/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*6 0/10)/24/60*10;
TRUNC(TM)+FLOOR ((TM- SUM(VAL)
-------------------- ----------
05-FEB-2004 14:10:00 21
05-FEB-2004 14:20:00 84 ak************@ yahoo.com (AK) wrote in message news:<46******* *************** ****@posting.go ogle.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.
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.val ue/12,
round(dbms_rand om.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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
I'm a long-time software engineer who's thinking about changing
careers, hence the psychology course. I ended up wanting to study
people like me (not surprising I guess).
|
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 important
changes, so in this thread I would like to focus on that issue alone.
I have edited the Proposed Syntax example below to take out the
changes unecessary to this discussion. I left in the change of
"instance variable" syntax (...
|
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 char* formatter, ...);
Then, I want to call it as so:
|
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 fun()
{
|
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 Analytical function count of parttime by dept
Ex :
dept 10 prttime - Y -count 1
| | |
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? or is it a call taken by
compiler?
Second, i see that it is same as what Macro's used to do for c, if so
what is the advantage for going in for inline functions than to Macros?
|
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 functions in common implementations?
And where is the 'this' ptr tucked away at for POD structs with member
functions?
John
|
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 values like this
first row (1+r1)*1000
secondrow ((1+r1)+(1+r2))*1000
|
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 table of the following structure:
create table values (
....
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
