在两个特定时间之间每 5 分钟减去一次数据 [英] subtract the data for every 5 minutes between two particular times

问题描述

我对 MYSQL 有一些问题，我需要每 5 分钟减去两个特定时间之间的数据，然后将 5 分钟数据取平均值.我现在正在做的是:

I have some problem with MYSQL,I need to subtract the data between two particular times,for every 5 minutes and then average it the 5 minutes data. What I am doing now is:

select (avg(columnname)),convert((min(datetime) div 500)*500, datetime) + INTERVAL 5 minute as endOfInterval 
from Databasename.Tablename 
where datetime BETWEEN '2012-09-12 10:50:00' AND '2012-09-12 14:50:00' 
group by datetime div 500;

这是累积平均值.假设我在 11 点钟得到 500，在 11.05 得到 700，我需要的平均值是 (700-500)/5 = 40.但现在我得到 (500+700)/5 = 240.我不需要累积平均值.请帮助我.

It is the cumulative average. Suppose i get 500 at 11 o' clock and 700 at 11.05 ,the average i need is (700-500)/5 = 40. But now i am getting (500+700)/5 = 240. I dont need the cumulative average . Kindly help me.

推荐答案

对于您所谈论的那种平均值，您不希望使用 GROUP 来聚合多行BY 子句.相反，您希望使用同一个表中正好两个不同的行来计算结果.这需要自加入:

For the kind of average you're talking about, you don't want to aggregate multiple rows using a GROUP BY clause. INstead, you want to compute your result using exactly two diffrent rows from the same table. This calls for a self-join:

SELECT (b.columnname - a.columnname)/5, a.datetime, b.datetime
FROM Database.Tablename a, Database.Tablename b
WHERE b.datetime = a.datetime + INTERVAL 5 MINUTE
  AND a.datetime BETWEEN '2012-09-12 10:50:00' AND '2012-09-12 14:45:00'

a 和 b 指的是同一个表的两个不同的行.WHERE 子句确保它们正好相隔 5 分钟.

a and b refer to two different rows of the same table. The WHERE clause ensures that they are exactly 5 minutes apart.

如果没有与该时间距离匹配的第二列，则查询结果中将不包含结果行.如果您的表格并非每五分钟就有一次数据点，而是您必须搜索合适的合作伙伴，那么事情就会变得更加困难.此答案可能会针对该用例进行调整.或者您可以在应用程序级别而不是在数据库服务器上实现这一点.

If there is no second column matching that temporal distance, no resulting row will be included in the query result. If your table doesn't have data points exactly every five minutes, but you have to search for the suitable partner instead, then things become much more difficult. This answer might perhaps be adjusted for that use case. Or you might implement this at the application level, instead of on the database server.

这篇关于在两个特定时间之间每 5 分钟减去一次数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！