在mysql中计算5分钟间隔的平均值-Java 学习之路

我有一个表 log ，其中包含列id，值，类别和时间戳 . 假设表格填充如下：

ID   VALUE        CATEGORY   TIMESTAMP
-----------------------------------------------
1     10             1       2010-11-1 10:00:00
2     20             1       2010-11-1 10:03:00
3     15             2       2010-11-1 10:15:00
4     05             2       2010-11-1 10:19:00
5     30             1       2010-11-1 10:24:00
6     12             1       2010-11-1 10:30:00

现在，我想从最后一个检查条目开始，以5分钟的间隔生成一个带有avg（）的表，用于指定的检查 . check = 1的输出应该是：

ID AVERAGE
----------
1  12
2  30
3  15

check = 2的输出应该是这样的：

ID AVERAGE
----------
1  10

解决这个问题的最佳方法是什么？我有MySQL的基本知识，但这让我很困惑 . 我认为部分查询将是这样的（没有5分钟间隔分组）：

SELECT avg(value) FROM log WHERE check = ..

如何使用时间戳生成5分钟的间隔平均值？任何帮助是极大的赞赏 .

4 回答

见this .

select AVG(value),  from_unixtime(ROUND(timestamp / (60*5)) * 60 * 5) as rounded_time
from table
group by rounded_time

回复于 2024-05-03T19:50:39+08:00

如果其他人感兴趣，这里是我提出的答案（在同事的帮助下） .

首先在MysQL中创建一个存储过程，如此（原始源代码从这里修改http://ondra.zizka.cz/stranky/programovani/sql/mysql_stored_procedures.texy）：

DELIMITER $$
CREATE PROCEDURE generate_series (
  iFrom TIMESTAMP, iTo TIMESTAMP, iStep INTEGER )

body:
BEGIN

  DROP TEMPORARY TABLE IF EXISTS stamp_series;
  CREATE TEMPORARY TABLE stamp_series ( stamp TIMESTAMP NOT NULL);

  IF iFrom IS NULL OR iTo IS NULL OR iStep IS NULL
    THEN LEAVE body; END IF;

  SET @iMax = iFrom;
  SET @iMin = iTo;

  InsertLoop: LOOP
    INSERT INTO stamp_series SET stamp = @iMax;
    SET @iMax = DATE_SUB( @iMax, INTERVAL iStep SECOND);
    IF @iMin > @iMax THEN LEAVE InsertLoop; END IF;
  END LOOP;

END; $$
DELIMITER ;

然后实际的查询是这样的：

CALL generate_series( timestamp1 , timestamp2 , 300);
SELECT stamp, DATE_SUB(stamp, INTERVAL 5 MINUTE) AS stamp_min_5, (  SELECT COALESCE( AVG(value), 0) FROM `table` WHERE `category` = 1 AND timestamp <= stamp AND timestamp > DATE_SUB(stamp, INTERVAL 5 MINUTE) ) AS gem FROM stamp_series;

问候，伊沃

回复于 2024-05-03T19:50:39+08:00

也许以后会有一个最短的查询：

SELECT ID, AVG(VALUE) as average, DATE_FORMAT(MIN(timestamp), '%Y-%m-%d %H:%i:00') AS tmstamp
   FROM yourtable GROUP BY round(UNIX_TIMESTAMP(timestamp) DIV 60*5)

回复于 2024-05-03T19:50:39+08:00

SELECT 
   DATE_FORMAT((atimestamp),
      concat( '%Y-%m-%d %H:' , 
            cast(round(minute(atimestamp)) -  round(minute(atimestamp)) % 5 as char))
   )AS rounded_time
   ,avg(price) , count(*) from table

这样你就可以得到5分，10分，15分，30分 . 分钟平均很容易 .

回复于 2024-05-03T19:50:39+08:00

在mysql中计算5分钟间隔的平均值

4 回答

相关问题