How to average/sum data in a day in SQL Server 200

问题:

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

Data:

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

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.

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)

回答1:

7:40am is 460 minutes after 00:00

7:40pm is 1180 minutes after 00:00

midnigth is 1440 minutes after 00:00

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

So, you could use:

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)

and for the Nightshift:

    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)
      )

回答2:

One possibility, if you need to do this often enough: add three computed columns for day, month, year to your table. Those columns are computed automatically based on the timestamp column, and they're just integer values, so they're easy to use in a GROUP BY.

To do this, use these T-SQL statements:

ALTER TABLE dbo.ROASTER_FEED ADD TSDay AS DAY(timestamp) PERSISTED
ALTER TABLE dbo.ROASTER_FEED ADD TSMonth AS MONTH(timestamp) PERSISTED
ALTER TABLE dbo.ROASTER_FEED ADD TSYear AS YEAR(timestamp) PERSISTED

Now, you can easily select your data based on any day you wish:

SELECT TSDay, TSMonth, TSYear, SUM(FEED)   -- use AVG(FEED) for average values
FROM dbo.ROASTER_FEED
WHERE TSYear = 2011 AND TSMonth = 8   -- or whatever you want to grab from the table!
ORDER BY timestamp
GROUP BY TSDay, TSMonth, TSYear

回答3:

Well I'm not a SQL guy, but I'd expect something like:

SELECT SUM(ValueColumn) AS Total, AVG(ValueColumn) AS Average FROM YourTableName
WHERE RecordedTime >= @StartTime AND RecordedTime < @EndTime

(where @StartTime and @EndTime are parameters filled in by the calling code).

(It's not clear to me what will happen if no records are found... worth checking out.)

回答4:

try this:

SELECT timestamp, sum(FEED)
FROM ROASTER_FEED
WHERE timestamp=....
GROUP BY timestamp

it could be slow if you are handling alot of data, in this case you should considering an additional table which holds the pre-calculated values for each day.

回答5:

SELECT COUNT(timestamp) as NumValues, 
       SUM(Feed) as ValuesSum, 
       AVG(Feed) as Average

  FROM ROASTER_FEED

 WHERE timestamp BETWEEN '1/1/2011'  AND '9/15/2011'

回答6:

@marc_s hit upon the preferred solution of using a computed columns to handle this but to do it on-the-fly, cast your timestamp data to shear off the time component. In my example, I cast it to a 10 character field that contains the 0 padded date.

;
WITH ROASTER_FEED ([timestamp],[Feed]) AS
(
SELECT CAST('02/07/2011 12:00:01' AS datetime),1246   
UNION ALL SELECT '02/07/2011 12:00:01',1234    
UNION ALL SELECT '02/07/2011 12:00:01',1387    
UNION ALL SELECT '02/07/2011 12:00:02',1425   
UNION ALL SELECT '02/07/2011 12:00:03',1263   

UNION ALL SELECT '02/07/2011 11:00:01',1153    
UNION ALL SELECT '02/07/2011 11:00:01',1348    
UNION ALL SELECT '02/07/2011 11:00:01',1387    
UNION ALL SELECT '02/07/2011 11:00:02',1425    
UNION ALL SELECT '02/07/2011 11:00:03',1223    

UNION ALL SELECT '03/07/2011 12:00:01',1226    
UNION ALL SELECT '03/07/2011 12:00:01',1245    
UNION ALL SELECT '03/07/2011 12:00:01',1384    
UNION ALL SELECT '03/07/2011 12:00:02',1225    
UNION ALL SELECT '03/07/2011 12:00:03',1363
)
SELECT
    -- Shear off the time value by forcing the date into date values
    -- If this was SQL Server 2008+, we'd just cast to date type and
    -- be done with it
    -- Chart for convert options
    -- http://msdn.microsoft.com/en-us/library/ms187928.aspx
    CONVERT(char(10), RF.[timestamp], 121) AS [timestamp]
,   COUNT(1) AS row_counts
,   SUM(RF.Feed) as ValuesSum 
,   AVG(RF.Feed) as Average
FROM 
    ROASTER_FEED RF
GROUP BY
    CONVERT(char(10), RF.[timestamp], 121)    

Results in

timestamp   row_counts   ValuesSum  Average
2011-02-07  10           13091      1309
2011-03-07  5            6443       1288

回答7:

To get the average feed for one day you can use this.

declare @Date datetime

set @Date = '20110702'

select @Date as [timestamp], avg(FEED) as AvgFeed
from ROASTER_FEED
where [timestamp] >= @Date and 
      [timestamp] < dateadd(day, 1, @Date)

回答8:

Check out some of the tutorials from here http://www.smallsql.de/doc/sql-functions/date-time/index.html ive been looking for something simialr and found that site useful and thought it may help