Performing math on time series data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Performing math on time series data

Author

Topic

blitzclipse
Starting Member

3 Posts

Posted - 2011-09-20 : 09:43:10

For example, I have the following table:

value Name datatime
-------------- ------------------------------ --------------------
3.97266 RB2_O2_INST 20-SEP-11 07:43:39.7
3.91113 RB2_O2_INST 20-SEP-11 07:32:39.3
3.59473 RB2_O2_INST 20-SEP-11 07:26:39.2
3.49805 RB2_O2_INST 20-SEP-11 07:23:39.1
3.64941 RB2_O2_INST 20-SEP-11 07:22:39.1
3.9541 RB2_O2_INST 20-SEP-11 07:21:39.0
4.05176 RB2_O2_INST 20-SEP-11 07:20:39.0
0.46875 RB2_NOX_INST 20-SEP-11 07:20:39.0
0.71875 RB2_NOX_INST 20-SEP-11 07:49:40.0

I want to create a result that is equal to the average of the following equation:(RB1_NOX_INST * 20.9)/(20.9-rb1_o2_inst) making sure that all data is evaluated correctly based on the timestamp.

Please make suggestions.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2011-09-20 : 10:02:02

first suggestion is to provide the exact expected result from your sample data. what you want so far makes no sense because there are no RB1_NOX_INST or rb1_o2_inst values.
do you want them grouped? what do you mean by " evaluated correctly based on the timestamp"?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!

SQL Server MVP

blitzclipse
Starting Member

3 Posts

Posted - 2011-09-20 : 10:22:04

If you look at the table, the column name has the names RB1_NOX_INST and rb1_o2_inst.

value,, Name,, datatime
-------------- ------------------------------ --------------------
3.97266,, RB2_O2_INST,, 20-SEP-11 07:43:39.7
3.91113,, RB2_O2_INST ,, 20-SEP-11 07:32:39.3
3.59473,, RB2_O2_INST,, 20-SEP-11 07:26:39.2
3.49805,, RB2_O2_INST,, 20-SEP-11 07:23:39.1
3.64941,, RB2_O2_INST,, 20-SEP-11 07:22:39.1
3.9541,, RB2_O2_INST,, 20-SEP-11 07:21:39.0
4.05176,, RB2_O2_INST,, 20-SEP-11 07:20:39.0
0.46875,, RB2_NOX_INST,, 20-SEP-11 07:20:39.0
0.71875,, RB2_NOX_INST,, 20-SEP-11 07:49:40.0

value2,, datatime
0.46875 * 4.05176,, 20-SEP-11 07:20:39.0
0.46875 * 3.9541,, 20-SEP-11 07:21:39.0
0.46875 * 3.64941,, 20-SEP-11 07:22:39.1
0.46875 * 3.49805,, 20-SEP-11 07:23:39.1
0.46875 * 3.59473,, 20-SEP-11 07:26:39.2
0.46875 * 3.91113,, 20-SEP-11 07:32:39.3
0.46875 * 3.97266,, 20-SEP-11 07:43:39.7
0.71875 * 3.97266,, 20-SEP-11 07:49:40.0

Then perform a time weighted average on the data in value2. I separated the columns with ",,", so everyone can see the values for the names easier.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2011-09-20 : 11:10:00

you don't have RB1_NOX_INST and rb1_o2_inst in there. you do have RB2_NOX_INST and rb2_o2_inst so i'm assuming you mean that.

and i still don't see how you got your results based on your data. is the order wrong?

again, what do you mean by "evaluated correctly based on the timestamp"? evaluated correctly how? we can't really read minds here.
for example in your first row of desired results you have
0.46875 * 4.05176,, 20-SEP-11 07:20:39.0
the 0.46875 is a value in the row in your data, but why is it repetead all the way down except for the last value?
i could imagine this being the case if your timestamps matched but they don't.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!

SQL Server MVP

blitzclipse
Starting Member

3 Posts

Posted - 2011-09-20 : 15:18:50

Sorry for the Typo rb1 should be rb2.

I don't understand how you can't visualize what I am asking based on the data provided. I will try to explain better.

1. You have two streams of data coming in: RB2_O2_INST and RB2_NOX_INST. These are raw values from an instrument in the field, so they will virtually never have the same timestamp.

2. When you have data like this coming in for analysis, you always use the most recent value for either name (tag) going into an equation.

In example, let’s say I have 50 values for RB2_O2_INST between 7:20:39 and 7:49:39. The corresponding value for RB2_NOX_INST would be 0.46875 for all 50 values according to the timestamp. At 7:49:40, you have a new value for RB2_NOX_INST (0.71875), so the corresponding value for RB2_O2_INST would be 3.97266 because it hasn't received an updated value; therefore, it must still be within its dead band or compression.

3. The final part to the problem is to provide a time weighted average which is calculated based on how long each result value lasted. The last value would be assumed to be the same up to the current timestamp.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources