当前位置： 文江博客话题详情

如何在 SQL Server 2005 中对一天内的数据进行平均/求和

发布于 2024-12-07 16:51:33 字数 885 浏览 1 评论 0原文

我正在尝试对 SQL Server 2005 中一天的数据进行平均。 我的数据库如下所示

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

：当有人选择一个月中的日期并且它仅显示当天的平均值/总和时，我不知道如何对提要进行平均。

例如，在结果中，如果我选择日期为 02/07/2011。它会给我这样的东西：

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

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)

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（8）

踏月而来 2024-12-14 16:51:34

从这里查看一些教程
http://www.smallsql.de/doc/sql-functions /日期时间/index.html
我一直在寻找类似的东西，发现该网站很有用，并认为它可能会有所帮助

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

回复 原文
慈悲佛祖 2024-12-14 16:51:33

如果您需要足够频繁地执行此操作，则一种可能性是：将日、月、年的三个计算列添加到表中。这些列是根据 timestamp 列自动计算的，并且它们只是整数值，因此它们很容易在 GROUP BY 中使用。

为此，请使用以下 T-SQL 语句：

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

现在，您可以根据您希望的任何一天轻松选择数据：

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

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
回复 原文
夜深人未静 2024-12-14 16:51:33

上午 7:40 是 00:00 后 460 分钟

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

午夜是 00:00 后 1440 分钟

并且 DATEPART(hh, SomeDate)*60 + DATEPART(mi, SomeDate) 为您提供分钟数对于给定的 SomeDate 的 00:00 之后

，您可以使用：

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

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)
      )
回复 原文
哑 2024-12-14 16:51:33

好吧，我不是一个 SQL 专家，但我期望类似的东西：（

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

其中 @StartTime 和 @EndTime 是由调用代码填充的参数）。

（我不清楚如果没有找到记录会发生什么......值得检查。）

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

回复 原文
乖乖哒 2024-12-14 16:51:33

试试这个：

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

如果您正在处理大量数据，它可能会很慢，在这种情况下，您应该考虑使用一个额外的表来保存每天的预先计算的值。

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.

回复 原文
清欢 2024-12-14 16:51:33
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'

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'
回复 原文
沉睡月亮 2024-12-14 16:51:33

@marc_s 想到了使用计算列来处理此问题的首选解决方案，但要即时执行此操作，请转换时间戳数据以剪掉时间部分。在我的示例中，我将其转换为包含 0 填充日期的 10 个字符字段。

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

结果

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

@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
回复 原文
三月梨花 2024-12-14 16:51:33

要获得一天的平均饲料，您可以使用它。

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)

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)
回复 原文
~没有更多了~

关于作者

星

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

胡图图

文章 0 评论 0

zt006

文章 0 评论 0

z祗昰~

文章 0 评论 0

冰葑

文章 0 评论 0

野の

文章 0 评论 0

天空

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文