Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate cumulative counters
How to calculate cumulative counters [message #226692] Mon, 26 March 2007 03:55 Go to next message
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
Re: How to calculate cumulative counters [message #226699 is a reply to message #226692] Mon, 26 March 2007 04:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
You can use analytical functions.

By
Vamsi
Re: How to calculate cumulative counters [message #226702 is a reply to message #226699] Mon, 26 March 2007 04:34 Go to previous messageGo to next message
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 !!
Re: How to calculate cumulative counters [message #226710 is a reply to message #226702] Mon, 26 March 2007 04:52 Go to previous messageGo to next message
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
Re: How to calculate cumulative counters [message #226719 is a reply to message #226710] Mon, 26 March 2007 05:09 Go to previous message
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
Previous Topic: what is the difference between database & schema??
Next Topic: Logging stages of procedure
Goto Forum:
  


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