OraFAQ Forum: SQL & PL/SQL » How to calculate cumulative counters

Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate cumulative counters

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

How to calculate cumulative counters [message #226692]

Mon, 26 March 2007 03:55

dusoo
Messages: 41
Registered: March 2007

Member

Hi there,
I've started receiving data (every 15min) into few tables from which i have to create a graph. Problem is that those data that are comming are cumulative and the final value should be counted as NEW.value = NEW.value - previous.value for specific key (day/starttime/id) or as sum(for specific key). Sofar im putting those cumulative values into one temp table. From there i'm inserting the final new value into other temp table through the trigger. So, to count those values i need two tables per each main table. Now i've found out, that sometimes the final value is made from two different counters from diff tables. And that i found too bad, cos i have to create many temp tables..., so does anybody know about good work-around?

Thanks a lot !!!

example

step1: data -> table.tmp1, tmp2,...(cumulative values/counters)
step2: table.t1 (trigger that is inserting new value) -> final table MSC
step2_1: table.t2 -> table.t2_01 (new value) -> table.t2_02 (sum of the new value per key column) -> insert into final

tmp1
counter_1, starttime, mscid(key)
100,10.1.2007 0:00, 1
120,10.1.2007 0:15, 1
130,10.1.2007 0:30, 1

tmp1 -> final MSC
counter_x1, starttime, mscid(key)
0,10.1.2007 0:00, 1
20,10.1.2007 0:15, 1
10,10.1.2007 0:30, 1

tmp2
counter_132, starttime, mscid(key), region
110,10.1.2007 0:00, 1, A
110,10.1.2007 0:00, 1, B
130,10.1.2007 0:15, 1, A
130,10.1.2007 0:15, 1, B
140,10.1.2007 0:30, 1, A
140,10.1.2007 0:30, 1, B

tmp2_01 - final values after the trigger is used
counter_132, starttime, mscid(key), region
0,10.1.2007 0:00, 1, A
0,10.1.2007 0:00, 1, B
20,10.1.2007 0:15, 1, A
20,10.1.2007 0:15, 1, B
10,10.1.2007 0:30, 1, A
10,10.1.2007 0:30, 1, B

tmp3_01 - final values as sum per one mscid
counter_132, starttime, mscid(key)
0,10.1.2007 0:00, 1
40,10.1.2007 0:15, 1
20,10.1.2007 0:30, 1

final MSC
counter_x2, starttime, mscid(key)

counter_x2 = counter_x1 + temp3_01.counter_132

Report message to a moderator

Re: How to calculate cumulative counters [message #226699 is a reply to message #226692]

Mon, 26 March 2007 04:14

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

You can use analytical functions.

By
Vamsi

Report message to a moderator

Re: How to calculate cumulative counters [message #226702 is a reply to message #226699]

Mon, 26 March 2007 04:34

dusoo
Messages: 41
Registered: March 2007

Member

Hi, i know about those ones,
but problem is that i totaly dont have any idea if there is a way to use em when u have many key columns as well
example with analytic LAG function

select starttime, counter1, counter1-LAG(counter1,1) over (order by mscid)
from tmp1 where day = to_DatE('24.3.2007,'dd.mm.yyyy')

you are gonna get bad number in one specific time - where it finished counting per one mscid and started counting per other - i need there except having -2009835, a new.value - last value from day 23.3. for mscid RCP71BB.

24.3.2007 23:31:00 2764454 564 RCP11BA
24.3.2007 23:46:00 2764926 472 RCP11BA
24.3.2007 0:08:00 755091 -2009835 RCP71BB
24.3.2007 0:23:00 755296 205 RCP71BB

Thanks for helping !!

Report message to a moderator

Re: How to calculate cumulative counters [message #226710 is a reply to message #226702]

Mon, 26 March 2007 04:52

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Seems you have to have 'partition by mscid' also.

select starttime, counter1, counter1-LAG(counter1,1) over (partition by mscid order by mscid)
from tmp1 where day = to_DatE('24.3.2007,'dd.mm.yyyy')

Also you need to have other key columns in the partition, if you have any.

By
Vamsi

Report message to a moderator

Re: How to calculate cumulative counters [message #226719 is a reply to message #226710]

Mon, 26 March 2007 05:09

dusoo
Messages: 41
Registered: March 2007

Member

hi Vamsi

i've tried the forumla -> over (partition MSCID) and it worked like u said, well it worked for the specific situation, i'll try to test it and see what i get, but thanks for that help ! Smile

Report message to a moderator

Previous Topic:	what is the difference between database & schema??
Next Topic:	Logging stages of procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 00:05:50 CST 2024