рассчитать средние значения для 5-минутных интервалов в mysql

У меня есть таблица log с идентификатором столбца, значением, категорией и отметкой времени. Предположим, что таблица заполнена так:

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

Теперь я хотел бы создать таблицу со значением столбца avg() с интервалом в 5 минут для указанной проверки, начиная с последней записи проверки. Вывод для проверки = 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-минутных интервалов? Любая помощь приветствуется.


mysql sql average
person Ivo    schedule 01.11.2010    source источник
comment
Я не знаю mysql, но начнем с того, что делает GROUP BY.   -  person Pointy    schedule 02.11.2010
comment
Можете ли вы объяснить, что делает check=1? Я не получаю эту часть.   -  person rlb.usa    schedule 02.11.2010
comment
check = 1 просто означает среднее значение с 5-минутными интервалами для проверки столбца со значением 1.   -  person Ivo    schedule 02.11.2010

Ответы (4)


arrow_upward
1
arrow_downward

См. это.

select AVG(value),  from_unixtime(ROUND(timestamp / (60*5)) * 60 * 5) as rounded_time
from table
group by rounded_time
person Xodarap    schedule 01.11.2010
comment
Это почти доводит меня до цели! Я должен был упомянуть, что временная метка находится в формате Datetime, поэтому так: выберите AVG (значение), from_unixtime (ROUND (unix_timestamp (timestamp) / (60 * 5)) * 60 * 5) как округленное_время из log, где check = 1 группа по rounded_time почти доводит меня до цели. Я хотел бы, чтобы 5-минутные интервалы начинались с последней записи метки времени для конкретной проверки, а не с 00, 05, 10 и т. д. Например, интервалы проверки 2 должны быть 10:19, 10:14 и т. д. - person Ivo; 02.11.2010
comment
@Ivo: может быть, что-то вроде set @min_1 := min(from_unixtime..., тогда в вашем выборе сделайте ...ROUND((timestamp + @min_1) / (60 * 5)... - person Xodarap; 02.11.2010

arrow_upward
0
arrow_downward

Если кому-то еще интересно, вот ответ, который я придумал (с помощью коллеги).

Сначала создайте хранимую процедуру в 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;

С уважением, Иво

person Ivo    schedule 05.11.2010

arrow_upward
0
arrow_downward

Может быть, позже, но самый короткий запрос будет:

 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)
person Jaime M.    schedule 08.11.2011

arrow_upward
0
arrow_downward 

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 и т.д. минута средняя легко.

person Prasanna Sujeewa    schedule 02.12.2013