# Vector Average function in T-SQL

• ### Question

• Hi - We need to calculate the Vector Average of the Wind direction based on Wind speed in SQL Server. Normal Average function is not suitable for us.  Has anyone earlier worked similar requirement.  Please let me know how you have solved it.

thanks

﻿

Tuesday, April 7, 2015 6:14 AM

• Yep.

```DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME);

insert into @windDir(recordedDateTime,degrees)
values
('2015-04-05 12:00 PM',359),
('2015-04-05 12:10 PM',0),
('2015-04-05 12:20 PM',357),
('2015-04-05 12:30 PM',355),
('2015-04-05 12:40 PM',347),
('2015-04-05 12:50 PM',339)

select * from @windDir;

;with q as
(
select degrees * PI()/180. radians, recordedDateTime
from @windDir
), q2 as
(
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
)
from q3```

outputs

```avgDegrees
------------
352.851709391681```

David

Wednesday, April 8, 2015 1:13 PM

### All replies

• Sounds like this is what you're after

http://www.nullskull.com/a/1390/sql-server-get-average-wind-direction-with-user-defined-aggregate-functions.aspx

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Tuesday, April 7, 2015 6:26 AM
• Thank you! But is there any way we can do it using T-SQL functions without using .net stuff.
Tuesday, April 7, 2015 6:35 AM
• How do you calculate "Vector Average of the Wind direction"?

David

Tuesday, April 7, 2015 1:49 PM
• 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, '2015-04-07 08:00:00'),(350, '2015-04-07 08:05:00'),(350, '2015-04-07 08:10:00'),(350, '2015-04-07 08:15:00'),
(5  , '2015-04-07 08:20:00'),(5  , '2015-04-07 08:25:00'),(350, '2015-04-07 08:30:00'),(5  , '2015-04-07 08:35:00'),
(5  , '2015-04-07 08:40:00'),(5  , '2015-04-07 08:45:00'),(350, '2015-04-07 08:50:00'),(5  , '2015-04-07 08:55:00'),
(15 , '2015-04-07 09:00:00'),(15 , '2015-04-07 09:05:00'),(15 , '2015-04-07 09:10:00'),(15 , '2015-04-07 09:15:00'),
(5  , '2015-04-07 09:20:00'),(5  , '2015-04-07 09:25:00'),(15 , '2015-04-07 09:30:00'),(5  , '2015-04-07 09:35:00'),
(5  , '2015-04-07 09:40:00'),(5  , '2015-04-07 09:45:00'),(15 , '2015-04-07 09:50:00'),(5  , '2015-04-07 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 degrees-360
ELSE degrees
END) < 0 THEN 360+AVG(CASE WHEN (degrees > 180) THEN degrees-360 ELSE degrees END)
ELSE AVG(CASE WHEN (degrees > 180) THEN degrees-360 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.

Tuesday, April 7, 2015 2:21 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)
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 Tuesday, April 7, 2015 5:06 PM
Tuesday, April 7, 2015 3:56 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, '2015-04-04T17:00:10')
From Numbers
Option(MaxRecursion 0)```

works well to test the top ends (270-90)

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Tuesday, April 7, 2015 4:55 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, '2015-04-04T17:00:10')
From Numbers
Option(MaxRecursion 0)

select * from @windDir;

;with q as
(
select degrees * PI()/180. radians, recordedDateTime
from @windDir
), q2 as
(
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
)
from q3```

You can simplify this if you want, but computers don't care and it's clearer if you don't.

David

Tuesday, April 7, 2015 7:06 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

﻿

Wednesday, April 8, 2015 11:10 AM
• Yep.

```DECLARE @windDir TABLE (degrees FLOAT, recordedDateTime DATETIME);

insert into @windDir(recordedDateTime,degrees)
values
('2015-04-05 12:00 PM',359),
('2015-04-05 12:10 PM',0),
('2015-04-05 12:20 PM',357),
('2015-04-05 12:30 PM',355),
('2015-04-05 12:40 PM',347),
('2015-04-05 12:50 PM',339)

select * from @windDir;

;with q as
(
select degrees * PI()/180. radians, recordedDateTime
from @windDir
), q2 as
(
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
)
from q3```

outputs

```avgDegrees
------------
352.851709391681```

David

Wednesday, April 8, 2015 1:13 PM
• Thanks David! Really appreciate your help!
Wednesday, April 8, 2015 5:31 PM