在给定每日小时数据的情况下，如何在SQL Server中对从上午8点到晚上8点的12小时范围内的小时间隔数据进行平均

Question

我的数据库中有以下格式的小时间隔数据：

EFFECTIVEDATETIME       NEW_YORK ETC...                
---------------------------------------
2017-01-01 01:00:00.000 44
2017-01-01 02:00:00.000 44
2017-01-01 03:00:00.000 45
2017-01-01 04:00:00.000 44
2017-01-01 05:00:00.000 45
2017-01-01 06:00:00.000 44
2017-01-01 07:00:00.000 44
2017-01-01 08:00:00.000 44
2017-01-01 09:00:00.000 44
2017-01-01 10:00:00.000 46
2017-01-01 11:00:00.000 45
2017-01-01 12:00:00.000 46
2017-01-01 13:00:00.000 47
2017-01-01 14:00:00.000 47
2017-01-01 15:00:00.000 47
2017-01-01 16:00:00.000 47
2017-01-01 17:00:00.000 46
2017-01-01 18:00:00.000 45
2017-01-01 19:00:00.000 43
2017-01-01 20:00:00.000 41
2017-01-01 21:00:00.000 42
2017-01-01 22:00:00.000 42
2017-01-01 23:00:00.000 41

我试图仅在08:00:00.000到20:00:000的特定范围内对纽约和以下列进行平均。我可以用下面的代码做一整天。然而，我更希望能够平均我的数据只有12小时。任何帮助都将不胜感激，谢谢！

SELECT 
CAST(EFFECTIVEDATETIME AS DATE) AS EFFECTIVE_DATE,
AVG(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN TEMPERATURE ELSE NULL END) AS NEW_YORK,
AVG(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN DEWPOINT ELSE NULL END) AS NEW_YORK_DEWPOINT,
AVG(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN RelativeHumidity ELSE NULL END) AS NEW_YORK_HUMIDITY,
MAX(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN TEMPERATURE ELSE NULL END) AS NEW_YORK_DAILY_MAX,
MIN(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN TEMPERATURE ELSE NULL END)  AS NEW_YORK_DAILY_MIN
FROM
DBO.WeatherHourly
WHERE
EffectiveDateTime > '2017-01-01'
GROUP BY 
CAST(EFFECTIVEDATETIME AS DATE)
ORDER BY 
EFFECTIVE_DATE

Answer 1

如果您想将开始时间限制在间隔内，可以使用：

WHERE EffectiveDateTime > '2017-01-01' AND
CAST(EffectiveDateTime as TIME) BETWEEN '08:00:00' AND '20:00:00'

如果你也想限制结束日期/时间，那就更具挑战性了。

Answer 2

您可以在WHERE子句中添加一个关于一天中某个小时的筛选条件：

SELECT CAST(EFFECTIVEDATETIME AS DATE) AS EFFECTIVE_DATE,
AVG(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN TEMPERATURE ELSE NULL END) AS NEW_YORK,
AVG(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN DEWPOINT ELSE NULL END) AS NEW_YORK_DEWPOINT,
AVG(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN RelativeHumidity ELSE NULL END) AS NEW_YORK_HUMIDITY,
MAX(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN TEMPERATURE ELSE NULL END) AS NEW_YORK_DAILY_MAX,
MIN(CASE WHEN WEATHERSTATIONID = 'KNYC' THEN TEMPERATURE ELSE NULL END)  AS NEW_YORK_DAILY_MIN
FROM DBO.WeatherHourly
WHERE EffectiveDateTime > '2017-01-01' AND DATEPART(hour, EffectiveDateTime) BETWEEN 8 AND 19
GROUP BY CAST(EFFECTIVEDATETIME AS DATE)
ORDER BY EFFECTIVE_DATE