I'm not sure what this would be called as searching for the term "moving average" is not producing what it is that I'm looking to do...
I have a table in MSSQL which contains a series of weather observations, these observations are taken at irregular intervals.
I want to create a stored procedure in SQL which will take a time as an input variable, and then select the nearest before and after observations, and calculate an approximation of the measurements at that input time and return these values.
I understand I could create an average of the 2 rows, but if I input a time of 07:24 and there is an observation at 07:25, the measurements are going to be closer to the recorded value than the average.
In my head, I think I know how to do it, but its messy: -
- Calculate difference between measurement times (minutes/int)
- Calculate difference between measurement values (int/float)
- Divide times/values to give a measurement per minute
- Calculate difference between earliest time and input variable (minutes/int)
- Multiply measurement per minute by input var difference (int/float)
- Add the value from the step above to the earliest time measurement variable to produce the returned value
So my concerns are:
- Is this too advanced or complex a function for SQL?
- If not, will it be a very long and messy stored procedure?
I'm not looking for somebody to do it for me, I want to learn, any pointers, advice, duplicate posts or sample code snippets would be greatly received and studied.
CodePudding user response：
I think what you want to do is a like a weighted average. This should be relatively straightforward to achieve in a stored proc or user defined function, though obviously will only work for numeric observations, not text ones.
First we get the observations directly before and after the specified time. Lets call these A and B.
Then we work out the number of seconds between the time of A and the specified time (lets call this X) and the number of seconds between the times of A and B (lets call this Y).
The calculation X / Y will give us a percentage of "distance" between A and the specified time, so (1 - X / Y) will give us the "closeness" to A which we can use as a weightings in the weighted average. The inverse applies to B.
So the final calculation is:
A x (1 - X / Y) B x (X / Y)
For example, if the specified time is 7:25, and the closest observations are 32 at 7:20 and 30 at 7:30, we have the following:
A=32,B=30,X=10,Y=20X / Y=1 - X / Y=0.5Output=31