从每小时获取每日数据

Question

我有一个查询，它返回每小时的数据。但我想从这个查询中获得每日数据，所以每天所有的小时数据都将被平均为每日数据。

declare @Days int
set @Days = -1
select      
dateadd(hour,datepart(hour,Timestamp),cast(CAST((Timestamp) as date) as datetime)) as [Time]  
,[value] 

from  [Employee]
where dateadd(hour,datepart(hour,Timestamp),cast(CAST((Timestamp) as date) as datetime)) >= CONVERT(date, DATEADD(DAY, @Days, GETDATE()))

Answer 1

假设您有其他列要按日期进行平均和分组，您可以尝试以下操作：

DECLARE @Days int = -1;
SELECT 
CAST(Timestamp AS date) AS date
, AVG(Value) AS Value
FROM  [Employee]
WHERE Timestamp >= DATEADD(day, @Days, CAST(GETDATE() AS date))
GROUP BY CAST(Timestamp AS date)
ORDER BY date;

请注意重构的WHERE子句，它避免将函数应用于列值。这将允许有效地使用Timestamp上的索引(sargable表达式(。