SQL选择（最大值B处的值A）和其他计算值（平均/最小/最大）

I have some weather data stored in a SQL Server database. The relevant columns are

我有一些存储在SQL Server数据库中的天气数据。相关栏目是

[SampleDate], [SampleDateTime], [WindSpeed_Avg_2MIN], [WindDir_AVG_2MIN]

and store data at 2 minute intervals. The code gives me a summary by day for a month

并以2分钟的间隔存储数据。该代码每天给我一个总结一个月

SELECT 
    [SampleDate], 
    Max([WindSpeed_Avg_2MIN]) as PeakWind, 
    Avg([WindSpeed_Avg_2MIN]) as AverageWind
FROM
   (SELECT 
        [SampleDateTime], 
        [WindSpeed_Avg_2MIN],
        [WindDir_AVG_2MIN] 
    FROM 
        WeatherData
    WHERE 
        ((DATEPART(mm,SampleDateTime) = @Month) 
        AND (DATEPART(yyyy,SampleDateTime) = @Year))) as tblA
GROUP BY 
    [SampleDate]
ORDER BY 
    [SampleDate]

Which yields

SampleDate  PeakWind    AverageWind
----------------------------------------
15/01/01    3.9         1.18587301587302
15/01/02    4.6         1.60222531293463
15/01/03    6.6         1.86013888888888

What I want is to add a column that would show the Wind Direction [WindDir_AVG_2MIN] for the row that had the PeakWind. So for 720 rows in a day, I've got the AVG and MAX of [WindSpeed_Avg_2MIN], and I want to show the discrete value for [WindDir_AVG_2MIN] for the row that has the MAX of [WindSpeed_Avg_2MIN]

我想要的是添加一个列,该列将显示具有PeakWind的行的Wind Direction [WindDir_AVG_2MIN]。因此,对于一天中的720行,我有[WindSpeed_Avg_2MIN]的AVG和MAX,我想为[WindSpeed_Avg_2MIN]的MAX显示[WindDir_AVG_2MIN]的离散值

Expected output:

SampleDate  PeakWind    AverageWind      WindDirAtPeakWind
----------------------------------------------------------
15/01/01    3.9         1.18587301587302 78
15/01/02    4.6         1.60222531293463 85
15/01/03    6.6         1.86013888888888 26

I can't seem to find the proper JOIN or sub query to get the result.

我似乎无法找到正确的JOIN或子查询来获得结果。

Any ideas?

DDL

CREATE TABLE [dbo].[WeatherData](
[SampleDateTime] [datetime] NULL,
[StationID] [smallint] NULL,
[SampleDate] [nchar](10) NULL,
[SampleTime] [nchar](10) NULL,
[WindSpeed_AVG_2MIN] [float] NULL,
[WindGust_AVG_2MIN] [float] NULL,
[WindDir_AVG_2MIN] [float] NULL,
[WindSpeed_AVG_10MIN] [float] NULL,
[WindGust_AVG_10MIN] [float] NULL,
[WindDir_AVG_10MIN] [float] NULL,
[AirTemp] [float] NULL,
[RelHumidity] [float] NULL,
[DewPoint] [float] NULL,
[Pyranometer] [float] NULL,
[Quantum] [float] NULL,
[AirPressure] [float] NULL,
[SnowLevel] [float] NULL,
[MeltedPrecip] [float] NULL,
[PW_Current] [char](10) NULL,
[PW_15MIN] [char](10) NULL,
[PW_60MIN] [char](10) NULL,
[PW_Vis] [float] NULL,
[Visibility] [float] NULL,
[CloudBase_1] [float] NULL,
[CloudBase_2] [float] NULL,
[CloudBase_3] [float] NULL,
[VerticalVis] [float] NULL,
[Batt_VDC] [float] NULL,
[BIT] [char](10) NULL
)

Example Data

SampleDateTime  StationID   SampleDate  SampleTime  WindSpeed_AVG_2MIN  WindGust_AVG_2MIN   WindDir_AVG_2MIN    WindSpeed_AVG_10MIN WindGust_AVG_10MIN  WindDir_AVG_10MIN
2015-01-31 23:59:06.000 100 15/01/31    03:03:58    16.1    19.3    25  15.6    19.3    27
2015-01-31 23:57:06.000 100 15/01/31    03:01:58    15.8    19.3    28  15.5    19.3    27
2015-01-31 23:55:05.000 100 15/02/01    02:59:58    9.8 10.9    16  8.8 11.1    19
2015-01-31 23:53:05.000 100 15/02/01    02:57:58    9.7 10.9    16  8.5 11.1    20

2 个解决方案

#1

There are a few ways you can do this, but one that will be less of an overhaul of your current query would be a sub-query:

有几种方法可以做到这一点,但是对于当前查询不那么彻底检查的方法是子查询:

SELECT 
 [SampleDate], 
 Max([WindSpeed_Avg_2MIN]) as PeakWind, 
 Avg([WindSpeed_Avg_2MIN]) as AverageWind
 Max([WindDir_AVG_2MIN]) AS PeakWindDir
FROM
(
 SELECT 
  [SampleDate], 
  [WindSpeed_Avg_2MIN],
  (SELECT TOP 1 [WindDir_AVG_2MIN] FROM WeatherData wd1
     WHERE wd1.SampleDate=wd.SampleDate
     ORDER BY [WindSpeed_Avg_2MIN] DESC
  ) AS [WindDir_AVG_2MIN]
 FROM WeatherData wd
 WHERE ((DATEPART(mm,SampleDateTime) = @Month) and
       (DATEPART(yyyy,SampleDateTime) = @Year))
) as tblA
GROUP BY [SampleDate]
ORDER BY [SampleDate]

Note that I also changed SampleDateTime to SampleDate in the derived table, since you can't possibly be selecting SampleDate in the outer query otherwise.

请注意,我还在派生表中将SampleDateTime更改为SampleDate,否则您无法在外部查询中选择SampleDate。

#2

So I moved to a CTE which is the same as a sub query but you can join twice on it. I added a column to give the wind speed an order. Then I did your same query and joined back to the the value needed.

所以我转移到CTE,它与子查询相同,但你可以加入两次。我添加了一个列来给风速一个订单。然后我做了同样的查询并加入了所需的值。

WITH monthData AS ( 
  SELECT 
    [SampleDate], 
    [WindSpeed_Avg_2MIN],
    [WindDir_AVG_2MIN],
    ROW_NUMBER() OVER (PARTITION BY SampleDate ORDER BY WindSpeed_Avg_2MIN DESC) AS SpeedOrder   
  FROM WeatherData
  WHERE Month(SampleDateTime) = @Month AND Year(SampleDateTime) = @Year     
) SELECT [a.SampleDate], 
    Max([a.WindSpeed_Avg_2MIN]) as PeakWind, 
    Avg([a.WindSpeed_Avg_2MIN]) as AverageWind,
    Max([b.WindDir_AVG_2MIN]) as WindDir_atMax
  FROM monthData a 
  JOIN monthData b ON a.SampleDateTime = b.SampleDateTime AND b.SpeedOrder = 1 
  GROUP BY [SampleDate]
  ORDER BY [SampleDate]

#1