Función agregada en un intervalo de time dado

Mi SQL está un poco oxidado y estoy teniendo bastante dificultad con este problema. Supongamos que tengo una tabla con una columna Timestamp y una columna Number. El objective es devolver un set de resultados que contenga el valor promedio de algún intervalo regular elegido arbitrariamente.

Entonces, por ejemplo, si tuviera los siguientes datos iniciales, la salida resultante con un intervalo de 5 minutos sería la siguiente:

time value ------------------------------- ----- 06-JUN-12 12.40.00.000000000 PM 2 06-JUN-12 12.41.35.000000000 PM 3 06-JUN-12 12.43.22.000000000 PM 4 06-JUN-12 12.47.55.000000000 PM 5 06-JUN-12 12.52.00.000000000 PM 2 06-JUN-12 12.54.59.000000000 PM 3 06-JUN-12 12.56.01.000000000 PM 4 OUTPUT: start_time avg_value ------------------------------- --------- 06-JUN-12 12.40.00.000000000 PM 3 06-JUN-12 12.45.00.000000000 PM 5 06-JUN-12 12.50.00.000000000 PM 2.5 06-JUN-12 12.55.00.000000000 PM 4 

Tenga en count que esta es una database Oracle, por lo que las soluciones específicas de Oracle funcionarían bien. Esto podría, por supuesto, hacerse con un procedimiento almacenado, pero esperaba lograr la tarea en una sola consulta.

 CREATE TABLE tt (time TIMESTAMP, value NUMBER); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.40.00.000000000 PM', 2); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.41.35.000000000 PM', 3); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.43.22.000000000 PM', 4); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.47.55.000000000 PM', 5); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.52.00.000000000 PM', 2); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.54.59.000000000 PM', 3); INSERT INTO tt (time, value) VALUES ('06-JUN-12 12.56.01.000000000 PM', 4); WITH tmin AS ( SELECT MIN(time) t FROM tt ), tmax AS ( SELECT MAX(time) t FROM tt ) SELECT ranges.inf, ranges.sup, AVG(tt.value) FROM ( SELECT 5*(level-1)*(1/24/60) + tmin.t as inf, 5*(level)*(1/24/60) + tmin.t as sup FROM tmin, tmax CONNECT BY (5*(level-1)*(1/24/60) + tmin.t) < tmax.t ) ranges JOIN tt ON tt.time BETWEEN ranges.inf AND ranges.sup GROUP BY ranges.inf, ranges.sup ORDER BY ranges.inf 

violín: http://sqlfiddle.com/#!4/9e314/11

editar: golpeado por Justin, como de costumbre … 🙂

Algo como

 with st as (SELECT to_timestamp( '2012-06-06 12:40:00', 'yyyy-mm-dd hh24:mi:ss') + numtodsinterval((level-1)*5, 'MINUTE') start_time, to_timestamp( '2012-06-06 12:40:00', 'yyyy-mm-dd hh24:mi:ss') + numtodsinterval(level*5, 'MINUTE') end_time from dual connect by level <= 10) SELECT st.start_time, avg( yt.value ) FROM your_table yt, st WHERE yt.time between st.start_time and st.end_time 

Deberia trabajar. En lugar de generar 10 intervalos y codificar el intervalo más bajo, puede mejorar la consulta para derivar el punto inicial y el número de filas de MIN(time) y MAX(time) en la tabla.

Las respuestas de Justin y Sebas se pueden ampliar con una UNIÓN IZQUIERDA para eliminar "brechas", que a menudo es deseable.

Si eso no es necesario, como alternativa, podemos ir a la aritmética Oracle DATE de la vieja escuela …

 SELECT TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400 AS time , AVG(t.value) AS avg_value FROM foo t WHERE t.time IS NOT NULL GROUP BY TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400 ORDER BY TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400 

Vamos a descomprimir un poco eso. Podemos separar los componentes de date y hora, usando TRUNC para get la porción de date y usando TO_CHAR para devolver el número de segundos desde la medianoche. Sabemos que 5 minutos son 300 segundos, y sabemos que hay 86400 segundos en un día. Entonces podemos dividir el número de segundos entre 300, y tomar el PISO de eso (solo la parte entera), que nos networkingondea al límite de 5 minutos más cercano. Multiplicamos eso de vuelta (por 300), para get segundos nuevamente, y luego dividimos eso por el número de segundos en un día (86400), y podemos agregar eso a la porción de date (truncada).

Doloroso, sí Pero sorprendentemente rápido.

NOTA: esto devuelve el valor de time networkingondeado como DATE , esto podría ser devuelto a una timestamp si es necesario, pero para límites de 5 minutos, una DATE tiene suficiente resolución.

Como beneficio de este enfoque, para una tabla grande, podemos boost el performance de la consulta al agregar un índice de cobertura para esta consulta:

 CREATE INDEX foo_FBX1 ON foo (TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400,value); 

APÉNDICE:

MiMo proporcionó una respuesta para SQL Server, sugiriendo que sería adaptable para Oracle. Aquí hay una adaptación de ese enfoque en Oracle. Tenga en count que Oracle no proporciona equivalentes para las funciones DATEDIFF y DATEADD. Oracle usa aritmética simple en su lugar.

 SELECT TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288 AS time , AVG(t.value) AS avg_value FROM foo t WHERE t.time IS NOT NULL GROUP BY TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288 ORDER BY TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288 

La elección del 1 de enero de 0001 AD como date base es arbitraria, pero no quería meterme con los valores negativos, y averiguar si FLOOR tendría razón, o si necesitaríamos usar CEIL con numbers negativos. (El número mágico 288 es el resultado de 1440 minutos en un día dividido entre 5). En este caso, estamos tomando el día fraccionario, multiplicando por 1440 y dividiendo por 5, y tomando una porción entera de eso, y luego volviéndolo a días fraccionarios.

Es tentador extraer esa "date base" de un package PL / SQL, o getla de una subconsulta, pero hacer cualquiera de ellas puede evitar que esta expresión sea determinista. Y nos gustaría mantener abierta la opción de crear un índice basado en function.

Mi preference es evitar la necesidad de include una "date base" en el cálculo.

Esta es una solución para SQL Server:

 declare @startDate datetime = '2000-01-01T00:00:00' declare @interval int = 5 select DATEADD(mi, DATEDIFF(mi, @startDate, time)/@interval, @startDate), AVG(value) from table group by DATEDIFF(mi, @startDate, s_modifiedDate)/@interval order by DATEDIFF(mi, @startDate, s_modifiedDate)/@interval 

La date de inicio es arbitraria. La idea es calcular el número de minutos desde la date de inicio, y luego agrupar por este número dividido por el intervalo.

Debe ser adaptable a Oracle fácilmente usando el equivalente para DATEADD y DATEDIFF