[Resolvido] Pegar média de 30 em 30 minutos

[rodrigopaivaf 0]

Boa tarde,

 

Estou com um dúvida que me rendeu o dia. Tenho uma determinada tabela a qual preciso separá-la por período de 30 em 30 minutos, fazer a média desses registros, e fazer um 'group by'.

 

select count(id), ROUND(sum(pe)/count(ID)) as pe, ROUND(sum(pms)/count(ID)) as pms, ROUND(sum(pc)/count(ID)) as pc,
ROUND(sum(livre)/count(ID)) as livre
FROM lucy_logger.int_temp_graf i
where (TIMEDIFF(hora,DATE_ADD('2009-09-03 00:00:00', INTERVAL 30 Minute))) <= '-00:30:00'

UNION ALL

select count(id), ROUND(sum(pe)/count(ID)) as pe, ROUND(sum(pms)/count(ID)) as pms, ROUND(sum(pc)/count(ID)) as pc,
ROUND(sum(livre)/count(ID)) as livre
FROM lucy_logger.int_temp_graf i
where (TIMEDIFF(hora,DATE_ADD('2009-09-03 00:30:00', INTERVAL 30 Minute))) <= '-00:30:00'

Essa query que eu fiz, funciona porém terei que replicar para o dia todo, então teriamos 48 selects em union, tem tudo pra da merd... rsrs. Alguém conhece alguma função ou outro jeito!? Conto com vocês!!

 

Abs,

[Eclesiastes 2]

O que a gente pode fazer é um join com uma query que gera esses intervalos.

 

Exemplo:

 

SELECT count(i.id),
    ROUND(sum(i.pe)/count(ID)) as pe,
    ROUND(sum(i.pms)/count(ID)) as pms,
    ROUND(sum(i.pc)/count(ID)) as pc,
    ROUND(sum(i.livre)/count(ID)) as livre,
    X.INICIO,
    X.FIM
  FROM (
	SELECT TIME(DATE_ADD('2009-01-01 00:00:00', INTERVAL 30 * (A.N+B.N) MINUTE)) INICIO,
		   TIME(DATE_ADD('2009-01-01 00:00:00', INTERVAL 30 * (A.N+B.N+1) MINUTE)) FIM
	  FROM (
		  SELECT 0 N UNION ALL
		  SELECT 1   UNION ALL
		  SELECT 2   UNION ALL
		  SELECT 3   UNION ALL
		  SELECT 4   UNION ALL
		  SELECT 5   UNION ALL
		  SELECT 6   UNION ALL
		  SELECT 7   UNION ALL
		  SELECT 8   UNION ALL
		  SELECT 9) A,
		 (SELECT 0 N  UNION ALL
		  SELECT 10   UNION ALL
		  SELECT 20   UNION ALL
		  SELECT 30   UNION ALL
		  SELECT 40   UNION ALL
		  SELECT 50   UNION ALL
		  SELECT 60   UNION ALL
		  SELECT 70   UNION ALL
		  SELECT 80   UNION ALL
		  SELECT 90) B
	  LIMIT 48
  ) X
  JOIN lucy_logger.int_temp_graf i
  ON DATE(i.hora) = '2009-01-01'
    AND TIME(i.hora) > X.INICIO
    AND TIME(i.hora) <= X.INICIO

[rodrigopaivaf 0]

Cara ficou muito bom!!!! Interessante o conceito e como utilizou a subquery.

 

Eu tive que fazer algumas modificações para funcionar:

 

SELECT count(i.id),
    ROUND(sum(i.pe)/count(ID)) as pe,
    ROUND(sum(i.pms)/count(ID)) as pms,
    ROUND(sum(i.pc)/count(ID)) as pc,
    ROUND(sum(i.livre)/count(ID)) as livre ,
    X.INICIO,
    X.FIM
  FROM
   (
     SELECT TIME(DATE_ADD('2009-09-03 00:00:00', INTERVAL 30 * (A.N+B.N) MINUTE)) INICIO,
                 TIME(DATE_ADD('2009-09-03 00:00:00', INTERVAL 30 * (A.N+B.N+1) MINUTE)) FIM
          FROM (
                  SELECT 0 N UNION ALL
                  SELECT 1   UNION ALL
                  SELECT 2   UNION ALL
                  SELECT 3   UNION ALL
                  SELECT 4   UNION ALL
                  SELECT 5   UNION ALL
                  SELECT 6   UNION ALL
                  SELECT 7   UNION ALL
                  SELECT 8   UNION ALL
                  SELECT 9) A,
                 (SELECT 0 N  UNION ALL
                  SELECT 10   UNION ALL
                  SELECT 20   UNION ALL
                  SELECT 30   UNION ALL
                  SELECT 40   UNION ALL
                  SELECT 50   UNION ALL
                  SELECT 60   UNION ALL
                  SELECT 70   UNION ALL
                  SELECT 80   UNION ALL
                  SELECT 90) B
          LIMIT 48
  ) X
  JOIN lucy_logger.int_temp_graf i
  ON DATE(i.hora) = '2009-09-03'
   AND TIME(i.hora) > X.INICIO
   AND TIME(i.hora) <= X.FIM
group by X.INICIO, X.FIM

Agradeço pelo ajuda! Att, Paiva.