如何在SQL Server 2005中一天平均/求和数据 [英] How to average/sum data in a day in SQL Server 2005

问题描述

我正在尝试对SQL Server 2005中的数据进行平均计算。如果我将简单查询用作

I'm trying to average data in SQL Server 2005 in a day. Here is what my database look like this if I use simple query as

SELECT timestamp, FEED
FROM ROASTER_FEED
ORDER timestamp

数据：

timestamp               Feed    
02/07/2011 12:00:01     1246   
02/07/2011 12:00:01     1234    
02/07/2011 12:00:01     1387    
02/07/2011 12:00:02     1425   
02/07/2011 12:00:03     1263   
...    
02/07/2011 11:00:01     1153    
02/07/2011 11:00:01     1348    
02/07/2011 11:00:01     1387    
02/07/2011 11:00:02     1425    
02/07/2011 11:00:03     1223    
....   
03/07/2011 12:00:01     1226    
03/07/2011 12:00:01     1245    
03/07/2011 12:00:01     1384    
03/07/2011 12:00:02     1225    
03/07/2011 12:00:03     1363

当某人选择一个月中的某个日期时，我不知道如何平均供稿显示tha的平均值/总和

I don't know how to average the feed when someone select a date in a month and it give display the average/sum of that day only.

例如，在结果中，如果我选择 day作为02/07/2011。它会给我这样的内容：

For example, in the outcome, if I select day as 02/07/2011. It would give me something like this:

02/07/2011 1234 (average value/or sum)

推荐答案

7:40 am是00:00之后的460分钟

7:40am is 460 minutes after 00:00

7:40 pm是00:00之后的1180分钟

7:40pm is 1180 minutes after 00:00

midnigth是00:00之后的1440分钟

midnigth is 1440 minutes after 00:00

和DATEPART（hh，SomeDate）* 60 + DATEPART（mi，SomeDate）会为您提供给定SomeDate在00:00之后的分钟数

And DATEPART(hh, SomeDate)*60 + DATEPART(mi, SomeDate) gives you the amount of minutes after 00:00 for a given SomeDate

因此，您可以使用：

SELECT
    AVG(Temperature) As Dayshift
FROM Drye_data
WHERE DATEPART(hh, TimeStamp)*60 + DATEPART(mi, TimeStamp) BETWEEN 460 AND 1180
      AND @SelectedDate = CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME)

和夜班：

    SELECT
    AVG(Temperature) As Nigthshift
FROM Drye_data
WHERE (
        (DATEPART(hh, TimeStamp)*60 + DATEPART(mi, TimeStamp) BETWEEN 0 AND 460)
        AND @SelectedDate = DATEADD(dd, 1, CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME))
      )
      OR 
      (
         (DATEPART(hh, TimeStamp)*60 + DATEPART(mi, TimeStamp) BETWEEN 1180 AND 1440) AND @SelectedDate = CAST(FLOOR(CAST(TimeStamp AS FLOAT)) AS DATETIME)
      )

这篇关于如何在SQL Server 2005中一天平均/求和数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！