Answered by:
Vector Average function in TSQL
Question

Answers

Yep.
DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME); insert into @windDir(recordedDateTime,degrees) values ('20150405 12:00 PM',359), ('20150405 12:10 PM',0), ('20150405 12:20 PM',357), ('20150405 12:30 PM',355), ('20150405 12:40 PM',347), ('20150405 12:50 PM',339) select * from @windDir; ;with q as ( select degrees * PI()/180. radians, recordedDateTime from @windDir ), q2 as ( select x=avg(sin(radians)), y=avg(cos(radians)) from q ), q3 as ( select case when x>=0 and y>=0 then 0 + atan(x/y) NE quadrant when x>=0 and y<0 then Pi()  atan(x/y) SE quadrant when x<0 and y<0 then Pi() + atan(x/y) SW quadrant when x<0 and y>=0 then 2*PI()  atan(x/y) end avgRadians NW quadrant from q2 ) select avgRadians * 180./PI() avgDegrees from q3
outputs
avgDegrees  352.851709391681
David Marked as answer by Eric__ZhangModerator Monday, April 20, 2015 1:09 AM
All replies

Sounds like this is what you're after
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page 

How do you calculate "Vector Average of the Wind direction"?
David

It's looking like you need to determine the "side" of the circle you need to be on by looking at the difference between your min and your max. If it's over 180 degrees, we need to go the other way. Using that, we can have a case statement make the changes needed:
DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME) INSERT INTO @windDir (degrees, recordedDateTime) VALUES (350, '20150407 08:00:00'),(350, '20150407 08:05:00'),(350, '20150407 08:10:00'),(350, '20150407 08:15:00'), (5 , '20150407 08:20:00'),(5 , '20150407 08:25:00'),(350, '20150407 08:30:00'),(5 , '20150407 08:35:00'), (5 , '20150407 08:40:00'),(5 , '20150407 08:45:00'),(350, '20150407 08:50:00'),(5 , '20150407 08:55:00'), (15 , '20150407 09:00:00'),(15 , '20150407 09:05:00'),(15 , '20150407 09:10:00'),(15 , '20150407 09:15:00'), (5 , '20150407 09:20:00'),(5 , '20150407 09:25:00'),(15 , '20150407 09:30:00'),(5 , '20150407 09:35:00'), (5 , '20150407 09:40:00'),(5 , '20150407 09:45:00'),(15 , '20150407 09:50:00'),(5 , '20150407 09:55:00') SELECT CAST(recordedDateTime AS DATE) AS date, DATEPART(HOUR,recordedDateTime) AS hour, CASE WHEN ABS(MAX(degrees)  MIN(degrees)) > 180 THEN CASE WHEN AVG(CASE WHEN (degrees > 180) THEN degrees360 ELSE degrees END) < 0 THEN 360+AVG(CASE WHEN (degrees > 180) THEN degrees360 ELSE degrees END) ELSE AVG(CASE WHEN (degrees > 180) THEN degrees360 ELSE degrees END) END ELSE AVG(degrees) END FROM @windDir GROUP BY CAST(recordedDateTime AS DATE), DATEPART(HOUR,recordedDateTime)
In future, when you ask a question, please provide some example DDL, data and an expected output. This will help us to help you. When you provide example data, please do so in a table (as I have done at the top of this example).
Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.
 Edited by Patrick Hurst Tuesday, April 7, 2015 2:22 PM

Patrick, that won't work in every case. For example if you load @windDir with
;With Numbers As (Select 0 As Number Union All Select Number + 1 From Numbers Where Number < 270) INSERT INTO @windDir (degrees, recordedDateTime) Select Number, DateAdd(second, Number, '20150404T17:00:00') From Numbers Option(MaxRecursion 0)
That is, one entry for every degree from 0 thru 270. Then the average should be 135. Your query returns 15.44.
A straight forward method is to get the SIN and COS of each entry, average those values, then get the ARCTAN of the result. So,
;With cte As (Select CAST(recordedDateTime AS DATE) AS date, DATEPART(HOUR,recordedDateTime) AS hour, (180./PI()) * ATN2(AVG(SIN(degrees*PI()/180.)), AVG(COS(degrees*PI()/180.))) As Result FROM @windDir GROUP BY CAST(recordedDateTime AS DATE), DATEPART(HOUR,recordedDateTime)) Select date, hour, Case When result < 0 Then result + 360 Else result End As result From cte;
Note, the SIN, COS, and ATN2 functions expect the input/output to be in radians, not degrees. So the input values are multiplied by PI()/180. to convert to radians and the final result is multiplied by 180./PI() to convert back to degrees.
Tom
 Edited by Tom Cooper Tuesday, April 7, 2015 5:06 PM

Tom, you are correct. Up to 180 degrees in start and end, it works. The further passed that, the more it breaks down.
For testing purposes, I modified your numbers cte:
;With Numbers As (Select 1 As Number Union All Select Number + 1 From Numbers Where Number < 270) INSERT INTO @windDir (degrees, recordedDateTime) Select CASE WHEN Number < 0 then number + 360 else number end, DateAdd(second, CASE WHEN Number< 0 then number + 360 else number end, '20150404T17:00:10') From Numbers Option(MaxRecursion 0)
works well to test the top ends (27090)
Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.
 Edited by Patrick Hurst Tuesday, April 7, 2015 5:01 PM

I think that's very close.
With D in degrees clockwise from north, the point (x,y) : x= avg(sin(D)), y= avg(cos(D)) is on the unit circle in the average direction. The only thing you need to change is the signs of the x and y before taking the atan() and then offsetting it from either PI() or 0.
eg
DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME); With Numbers As (Select 1 As Number Union All Select Number + 1 From Numbers Where Number < 80) INSERT INTO @windDir (degrees, recordedDateTime) Select CASE WHEN Number < 0 then number + 360 else number end, DateAdd(second, CASE WHEN Number< 0 then number + 360 else number end, '20150404T17:00:10') From Numbers Option(MaxRecursion 0) select * from @windDir; ;with q as ( select degrees * PI()/180. radians, recordedDateTime from @windDir ), q2 as ( select x=avg(sin(radians)), y=avg(cos(radians)) from q ), q3 as ( select case when x>=0 and y>=0 then 0 + atan(x/y) NE quadrant when x>=0 and y<0 then Pi()  atan(x/y) SE quadrant when x<0 and y<0 then Pi() + atan(x/y) SW quadrant when x<0 and y>=0 then 0  atan(x/y) end avgRadians NW quadrant from q2 ) select avgRadians * 180./PI() avgDegrees from q3
You can simplify this if you want, but computers don't care and it's clearer if you don't.
David
David http://blogs.msdn.com/b/dbrowne/
 Edited by davidbaxterbrowneMicrosoft employee Tuesday, April 7, 2015 7:19 PM

Thanks all for your response. Apologies for not keeping the data. Below is my data for between 12:00PM to 12:50 PM
DateTime WindSpeed WindDirection
====== ======= =========
5/04/0215 12:00:00 PM 6 359
5/04/2015 12:10:00 PM 5.5 0
5/04/2015 12:20:00 PM 5.1 357
5/04/2015 12:30:00 PM 4.4 355
5/04/2015 12:40:00 PM 4.7 347
5/04/2015 12:50:00 PM 5.3 339
If i do the normal hourly average I will get 292 which is not correct for Wind Direction. I should get 353 instead of 292.
thanks

Yep.
DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME); insert into @windDir(recordedDateTime,degrees) values ('20150405 12:00 PM',359), ('20150405 12:10 PM',0), ('20150405 12:20 PM',357), ('20150405 12:30 PM',355), ('20150405 12:40 PM',347), ('20150405 12:50 PM',339) select * from @windDir; ;with q as ( select degrees * PI()/180. radians, recordedDateTime from @windDir ), q2 as ( select x=avg(sin(radians)), y=avg(cos(radians)) from q ), q3 as ( select case when x>=0 and y>=0 then 0 + atan(x/y) NE quadrant when x>=0 and y<0 then Pi()  atan(x/y) SE quadrant when x<0 and y<0 then Pi() + atan(x/y) SW quadrant when x<0 and y>=0 then 2*PI()  atan(x/y) end avgRadians NW quadrant from q2 ) select avgRadians * 180./PI() avgDegrees from q3
outputs
avgDegrees  352.851709391681
David Marked as answer by Eric__ZhangModerator Monday, April 20, 2015 1:09 AM
