totales - sql agrupar por rangos

¿Hay alguna manera de AGRUPAR POR un intervalo de tiempo en esta tabla?

Question

Tengo una mesa como esta:

DateTime   A

10:00:01   2 
10:00:07   4
10:00:10   2
10:00:17   1
10:00:18   3

¿Es posible crear una consulta que me devuelva el valor promedio de A cada 10 segundos? En este caso, el resultado sería:

3 (4+2)/2
2 (2+1+3)/3

¡Gracias por adelantado!

EDITAR: ¡Si realmente crees que esto no se puede hacer, solo di NO DE MANERA! :) Es una respuesta aceptable, realmente no sé si se puede hacer esto.

EDIT2: estoy usando SQL Server 2008. Me gustaría tener diferentes agrupaciones pero arregladas. Por ejemplo, varía cada 10 segundos, 1 minuto, 5 minutos, 30 minutos, 1 hora y 1 día (solo un ejemplo, pero algo así)

Answer 1

Me acerqué a esto usando una Expresión de tabla común para obtener todos los períodos entre cualquier fecha dada de mis datos. En principio, podría cambiar el intervalo a cualquier intervalo SQL.

DECLARE @interval_minutes INT = 5, @start_date DATETIME = ''20130201'', @end_date DATETIME = GETDATE()

;WITH cte_period AS
 (
    SELECT  CAST(@start_date AS DATETIME) AS [date]

    UNION ALL

     SELECT DATEADD(MINUTE, @interval_minutes, cte_period.[date]) AS [date]
     FROM cte_period
     WHERE DATEADD(MINUTE, @interval_minutes, cte_period.[date]) < @end_date
 )

, cte_intervals AS
 (SELECT [first].[date] AS [Start], [second].[date] AS [End] 
 FROM cte_period [first] 
 LEFT OUTER JOIN cte_period [second] ON DATEADD(MINUTE, 5, [first].[date]) = [second].[date]
  )

SELECT i.[Start], AVG(data)
FROM cte_intervals i
LEFT OUTER JOIN your_data mu ON mu.your_date_time >= i.Start and mu.your_date_time < i.[End]
GROUP BY i.[Start]
OPTION (MAXRECURSION 0)

Answer 2

Depende de DBMS que esté usando. En Oracle puedes hacer lo siguiente:

SELECT AVG(A) 
FROM MYTABLE 
GROUP BY to_char(DateTime, ''HH24:MI'') 

Answer 3

En SQL Server, puede usar DATEPART y luego agrupar por hora, minuto y segundo número entero de división 10.

CREATE TABLE #times
(
    thetime time,
    A int
)

INSERT #times
VALUES (''10:00:01'', 2)
INSERT #times
VALUES (''10:00:07'', 4)
INSERT #times
VALUES (''10:00:10'', 2)
INSERT #times
VALUES (''10:00:17'', 1)
INSERT #times
VALUES (''10:00:18'', 3)

SELECT avg(A)    --   <-- here you might deal with precision issues if you need non-integer results.  eg:  (avg(a * 1.0)
FROM #times
GROUP BY datepart(hour, thetime), DATEPART(minute, thetime), DATEPART(SECOND, thetime) / 10

DROP TABLE #times

Answer 4

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `dtime` datetime NOT NULL,
  `val` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


INSERT INTO `test` (`id`, `dtime`, `val`) VALUES
(1, ''2011-09-27 18:36:19'', 8),
(2, ''2011-09-27 18:36:21'', 4),
(3, ''2011-09-27 18:36:27'', 5),
(4, ''2011-09-27 18:36:35'', 3),
(5, ''2011-09-27 18:36:37'', 2);

SELECT *, AVG(val) FROM test GROUP BY FLOOR(UNIX_TIMESTAMP(dtime) / 10)

Answer 5

Alguien puede venir y darle una respuesta con el código completo, pero la forma en que abordaría esto es dividirlo en varios problemas / soluciones más pequeñas:

(1) Crea una tabla temporal con intervalos. Ver la respuesta aceptada sobre esta pregunta:

Obtener una lista de fechas entre dos fechas

Esta respuesta fue para MySQL, pero debería comenzar. Google "Crear intervalos SQL" también debería proporcionar formas adicionales para lograr esto. Querrá usar MAX (DateTime) y MIN (DateTime) de su tabla principal como entradas en cualquier método que use (y 10 segundos para el lapso, obviamente).

(2) Únase a la tabla temporal con su tabla principal, con una condición de unión de (pseudocódigo):

FROM mainTable m INNER JOIN #tempTable t ON m BETWEEN t.StartDate AND t.EndDate

(3) Ahora debería ser tan simple como SELECCIONAR y AGRUPAR correctamente:

SELECT 
  AVG(m.A)     
FROM  
  mainTable m 
  INNER JOIN #tempTable t ON m BETWEEN t.StartDate AND t.EndDate 
GROUP BY 
  t.StartDate 

Editar: si desea ver los intervalos con los que no hay registros (promedio cero), deberá reordenar la consulta, usar un JOIN IZQUIERDO y COALESCE en mA (ver a continuación). Si no te importa ver tales interal, la solución de OCary es mejor / más limpia.

SELECT 
  AVG(COALESCE(m.A, 0))
FROM  
  #tempTable t
  LEFT JOIN mainTable m ON m BETWEEN t.StartDate AND t.EndDate 
GROUP BY 
  t.StartDate 

Answer 6

Desde http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=142634 también puede usar la siguiente consulta:

select dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0), avg ( value )
from   yourtable
group by dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0)

que luego alguien amplía para sugerir:

Select
a.MyDate,
Start_of_10_Min =
dateadd(mi,(datepart(mi,a.MyDate)/10)*10,dateadd(hh,datediff(hh,0,a.Mydate),0))
from
( -- Test Data
select MyDate = getdate()
) a

aunque tampoco sé cómo planean obtener el promedio en la segunda sugerencia.

Personalmente prefiero la respuesta de OCary ya que sé lo que está sucediendo allí y que podré entenderla dentro de 6 meses sin volver a buscarla, pero incluyo esta para completarla.