Page 1 of 1

Cumulative values between 2 times

Posted: Wed Nov 17, 2010 5:34 pm
by Weiry
I have an interesting problem and im not sure what the most efficient way of doing this would be.

I have a table of data set out like the following:
[text]
node_id | poll_stamp | value
---------+------------------------+--------
77 | 2010-06-07 21:00:00+00 | 33.883
77 | 2010-06-07 21:15:00+00 | 35.244
77 | 2010-06-07 21:30:00+00 | 37.118[/text]

Data is entered into this table every 15 minutes, 24 hours a day, each recording a common node_id unique to the place the data is coming from.

From this data, i am generating a line graph using ZingChart.
There are 2 individual problems of the same major problem that i am facing.

1. Read cumulative values between 9am (day1) and 8:59am (day2). So from 9am, read cumulative values for the entire day, and then once 9am the following day comes around, reset the cumulative value.

2. The data which is being stored cannot be modified in any way, however because of the large amount of data being stored and accessed, it needs to be somewhat efficient and needs to minimize load.

The problem in trying to satisfy requirement #2, means that i cant run a query on each data point and select the sum up until that datetime.
This leaves me with the option of selecting all the data, then applying the changes as a PHP script.

I'm open to suggestions on how to get around this problem.

cheers.
Weiry

Re: Cumulative values between 2 times

Posted: Wed Nov 17, 2010 7:04 pm
by califdon
Either I am completely misunderstanding you or there is something illogical in your explanation. What would running a query have to do with modifying your data? If you are trying to obtain the sum of values between 2 times, a simple query using SUM() would give it to you. The only PHP involved would be to do whatever you need to do to get the data into your graphing package. ???

Considering the 3 example records you provided, are you sure that you really mean "cumulative"? Your example looks suspiciously like it's a constantly increasing value, in which case you most definitely should NOT sum up the cumulative values. But maybe that's just a poor example you chose.

Re: Cumulative values between 2 times

Posted: Wed Nov 17, 2010 8:02 pm
by Weiry
califdon wrote: If you are trying to obtain the sum of values between 2 times, a simple query using SUM() would give it to you. The only PHP involved would be to do whatever you need to do to get the data into your graphing package. ???
The SUM() is NOT between the 9am and 8:59am, the SUM() is the cumulative for each 15 minute interval until the following 9am and it must be cumulative of EACH 15 minute interval (otherwise how would you populate a time graph with a single value? )

time - value -- cumulative value
9:00 - 1.0 ---- 1.0
9:15 - 0.9 ---- 1.9
9:30 - 0.5 ---- 2.4
9:45 - 1.6 ---- 4.0

There are over 800,000 records in the database.
Assuming that you want me to do a SUM() between the current time and 9am for EACH 15 minute period up to a 24 hour period, this would include 95 individual SUM() queries for each individual 15 minute interval. So 800,000 queries is not exactly efficient (Requirement #2)

example of your SUM()
select sum(value),poll_stamp between 9:00 and 9:15
select sum(value),poll_stamp between 9:00 and 9:30
select sum(value),poll_stamp between 9:00 and 9:45
califdon wrote:Considering the 3 example records you provided, are you sure that you really mean "cumulative"? Your example looks suspiciously like it's a constantly increasing value, in which case you most definitely should NOT sum up the cumulative values. But maybe that's just a poor example you chose.
I will ignore all but the last sentence there.
The data being recorded is coming directly from sensors which measure a given value at a given time. The data itself does not record cumulatively. The example was supposed to be an example of how the data was stored rather than an example of actual data.

Re: Cumulative values between 2 times

Posted: Thu Nov 18, 2010 3:22 am
by VladSun
Weiry wrote:time - value -- cumulative value
9:00 - 1.0 ---- 1.0
9:15 - 0.9 ---- 1.9
9:30 - 0.5 ---- 2.4
9:45 - 1.6 ---- 4.0
Simple example query:

Code: Select all

set @temp = 0;
select 
	@temp := @temp + info.value as cumulative, 
from 
	info
Weiry wrote:There are over 800,000 records in the database...
Maybe you'll need to create denormalized (probably) table with generated data.