sql-server - Calculate average wind direction weighted by wind speed

Question

I have a SQL Server database that has one data table that has one second data. The table has the following columns:

DataId
DataType
NumericValue
PostDate

WindSpeed is one data type
Wind Direction is a second

Ultimately I would like to be able to have a stored procedure that I can send a start and end datetime and an average time interval in minutes. What I have so far is the following and it is not working.

select 
    'avgWD' = case
                 when weather.OuvWithoutFlow > 180 
                    then sum(weather.OuvWithoutFlow - 180)
                 when weather.OuvWithoutFlow < 180 
                    then sum(weather.OuvWithoutFlow + 180)
                 else sum(weather.OuvWithoutFlow + 0)
              end
from
    (select 
         (atan(((-1/(cast(count(*) as float)))*(sum(WD_sin)))/((-1/(cast(count(*) as float)))*(sum(WD_cos))))) as OuvWithoutFlow
     from
         (select 
              d_WS.numericvalue as WS,
              sin(d_WD.WD) as WD_sin,
              cos(d_WD.WD) as WD_cos,
              d_WD.WD, d_WS.PostDate
          from 
              (select Top 10 * 
               from datum 
               where datum.DatTypeID = @ParameterID_WS) as d_WS
       cross apply 
           (select numericvalue as WD 
            from datum 
            where datum.DatTypeID = @ParameterID_WD 
              and datum.postdate = d_WS.postdate) as d_WD) wind
    ) weather 
group by 
    weather.OuvWithoutFlow

Using this data set it returns 180.784167303869 which is wrong.

WS           WD_Sin             WD_COS              WD          PostDate
0.720000    0.632170969166108   0.344615533230271   214.700000  2018-05-21 21:50:03.0000000
0.977000    -0.520565399773975  0.82676518102673    200.500000  2018-05-21 21:50:04.0000000
1.132000    1.01064916139673    -0.509913985460376  203.100000  2018-05-21 21:50:05.0000000
1.183000    -0.859512457376702  -0.812851361328911  211.300000  2018-05-21 21:50:06.0000000
0.977000    -0.478215500921105  0.851961815270366   219.400000  2018-05-21 21:50:07.0000000
1.183000    0.555198984923194   1.04462581202087    220.400000  2018-05-21 21:50:08.0000000
0.926000    0.922867100571996   0.0761072577474554  221.400000  2018-05-21 21:50:09.0000000
1.029000    -0.686290821523267  -0.76671109832382   217.500000  2018-05-21 21:50:10.0000000
0.977000    0.102716204322373   0.971585498743988   226.300000  2018-05-21 21:50:11.0000000
1.080000    0.489014249853966   -0.962946033503313  216.300000  2018-05-21 21:50:12.0000000

标签： sql-serverdatabaserdbms

Answer 1

Upworks 帮了大忙。

    SET NOCOUNT ON;

;WITH 
     WindDirection 
     AS (SELECT d.PostDate, 
                d.NumericValue AS Direction 
         FROM   Datum AS d
         WHERE  ( d.ParameterId = @ParameterID_WD )), 
     WindSpeed 
     AS (SELECT d.PostDate, 
                d.NumericValue AS Speed 
         FROM   Datum  AS d
         WHERE  ( d.ParameterId = @ParameterID_WS )), 
     DatumCombined 
     AS (SELECT wd.postdate, 
                wd.direction, 
                ws.speed 
         FROM   WindDirection AS wd 
                INNER JOIN WindSpeed AS ws 
                        ON wd.PostDate = ws.PostDate), 
     DirectionRadians 
     AS (SELECT dc.Direction * Pi() / 180. AS [radians], 
                dc.PostDate, 
                dc.Speed 
         FROM   DatumCombined AS dc), 
     postdate 
     AS (SELECT Dateadd(minute, Datediff(minute, 0, dr.PostDate) / @interval * @interval, 0 ) AS postdate, 
                x = Avg(Sin(radians) * dr.Speed), 
                y = Avg(Cos(radians) * dr.Speed) 
         FROM   DirectionRadians AS dr
         GROUP  BY Dateadd(minute, Datediff(minute, 0, dr.PostDate) / @interval * @interval, 0)), 
     AverageRadiansByIntervals 
     AS (SELECT di.PostDate, 
                CASE 
                  WHEN di.x >= 0 
                       AND di.y >= 0 THEN 0 + Atan(di.x/di.y) 
                  WHEN di.x >= 0 
                       AND di.y < 0 THEN Pi() - Atan(di.x /-di.y) 
                  WHEN di.x < 0 
                       AND di.y < 0 THEN Pi() + Atan(-di.x/-di.y) 
                  WHEN di.x < 0 
                       AND di.y >= 0 THEN 2 * Pi() - Atan(-di.x /di.y) 
                END AS AverageRadians 
         FROM   PostDate AS di) 

SELECT ar.postdate, 
       ar.AverageRadians * 180. / Pi() numericvalue 
FROM  AverageRadiansByIntervals AS ar
WHERE ar.PostDate >= @StartDate AND ar.PostDate < @EndDate
ORDER  BY ar.PostDate