MS Access-SQL查询每天的平均数据 [英] MS Access - SQL Query for Average of Each Day Data

问题描述

我有一组跨越3天的数据.根据左侧的第一列，是否可能有一个查询来基于每天计算左侧左侧的第三列中的值的平均值?

I have a set of data spanning across 3 days. Based on the first column from the left, is it possible to have a query that calculates the average of the values in the third column from the left based on each day?

最终结果将是两列:

1/1/2008 | 1.605 
2/1/2008 | 1.59 
3/1/2008 | 1.56

我认为逻辑将类似于循环:

I think the logic will be something like in a loop:

• 根据第一列中的日期(如果当天是同一天)，计算行数并累加第三列的值
• 使用第三列值的总和除以行数即可得出平均值
• 天+ 1

但是我们如何在MS Access中实现循环?

But how can we implement a loop in MS Access?

这是数据集:

如果我想按日期分组，那一天是昨天的下午6点到今天的下午6点之间?

If I want to group by the date, where the day is between yesterday's 6PM to today's 6PM?

BETWEEN子句用于检查昨天的18:00:00和今天的18:00:00之间的记录.例如，对于 1/1/2008 ，记录将从1/1/2008 6:00PM开始到2/1/2008 6:00PM.对于 2/1/2008 ，记录将从2/1/2008 6:00PM开始到3/1/2008 6:00 PM`.依此类推...

The BETWEEN clause is to check for records that are between yesterday's 18:00:00 and today's 18:00:00. Example, for 1/1/2008, the record will start from 1/1/2008 6:00PM to 2/1/2008 6:00PM. For 2/1/2008, the record will start from 2/1/2008 6:00PM to 3/1/2008 6:00PM`. So on and so forth...

我有一个检查此内容的代码段:

I have a code snippet that checks for this:

([In process analysis result].[Date Time]) Between Date()-1+#12/30/1899 18:0:0# And Date()+#12/30/1899 18:0:0#)

但是它只能分组一天.如何申请该组数据?

But it only groups for one day before. How can I apply for the set of data?

这是我所做的查询，但仍然不正确.知道有什么问题吗?

Edit 2: This is the query that I have done, but it is still not correct. Any idea what is wrong?

SELECT DateValue([Date Time]) As DateValue, Avg([MFR g/10min]) AS [AvgOfMFR g/10min]
FROM [In process analysis result]
WHERE ((([Date Time])>Now()-365) AND (([Operation Grade-Load]) Like "EX*") AND (([Date Time]) Between [Date Time]-1+#12/30/1899 18:0:0# And [Date Time]+#12/30/1899 18:0:0#))
GROUP BY DateValue([Date Time]);

推荐答案

您的逻辑看起来基本正确. where子句看起来不正确.从此开始:

Your logic looks basically correct. The where clause looks off. Start with this:

SELECT DateValue(DateAdd("h", 6, [Date Time])) As DateValue,
       Avg([MFR g/10min]) AS [AvgOfMFR g/10min]
FROM [In process analysis result]
WHERE [Date Time] > DateAdd("yyyy", -1, DateAdd("h", 6, Now())) AND
      [Operation Grade-Load] Like "EX*" 
GROUP BY DateValue(DateAdd("h", 6, [Date Time]));

我不确定BETWEEN条件应该做什么.如果 您需要特定的日期范围，只需使用日期常量即可.

I'm not sure what the BETWEEN condition is supposed to be doing. If you want a particular date range, just use date constants.

修改:与上述修改一样，日期/时间应偏移6小时.并且应该使用正确的1年后的表达式.不知道当前时间是否也应该偏移6小时(如图所示)；如果没有，只需删除Now()的 DateAdd .

Edit: Date/time should be offset by 6 hours as in the above edit. And a proper 1-year-back expression should be used. Not sure if current time should be shifted 6 hours too (shown); if not, just remove the DateAdd of Now().

这篇关于MS Access-SQL查询每天的平均数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！