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
Cumulative values between 2 times
Moderator: General Moderators
Re: Cumulative values between 2 times
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.
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
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? )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. ???
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
I will ignore all but the last sentence there.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.
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
Simple example query: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
Code: Select all
set @temp = 0;
select
@temp := @temp + info.value as cumulative,
from
infoMaybe you'll need to create denormalized (probably) table with generated data.Weiry wrote:There are over 800,000 records in the database...
There are 10 types of people in this world, those who understand binary and those who don't