sql - second - ¿Cómo promediar los intervalos de tiempo?
oracle sysdate milliseconds (6)
En Oracle 10g tengo una tabla que contiene marcas de tiempo que muestran cuánto tiempo tomaron ciertas operaciones. Tiene dos campos de marca de tiempo: hora de inicio y hora de finalización. Quiero encontrar promedios de las duraciones dadas por estas marcas de tiempo. Lo intento:
select avg(endtime-starttime) from timings;
Pero consigue:
Error de SQL: ORA-00932: tipos de datos incoherentes: el NUMBER esperado obtuvo INTERVAL DAY TO SECOND
Esto funciona:
select
avg(extract( second from endtime - starttime) +
extract ( minute from endtime - starttime) * 60 +
extract ( hour from endtime - starttime) * 3600) from timings;
Pero es realmente lento.
¿Alguna mejor manera de convertir intervalos en números de segundos, o de alguna otra manera, hacer esto?
EDITAR: Lo que realmente estaba frenando esto era el hecho de que tenía un tiempo de finalización antes de la hora de inicio. Por alguna razón que hizo que este cálculo sea increíblemente lento. Mi problema subyacente se resolvió eliminándolos del conjunto de consultas. También definí una función para facilitar esta conversión:
FUNCTION fn_interval_to_sec ( i IN INTERVAL DAY TO SECOND )
RETURN NUMBER
IS
numSecs NUMBER;
BEGIN
numSecs := ((extract(day from i) * 24
+ extract(hour from i) )*60
+ extract(minute from i) )*60
+ extract(second from i);
RETURN numSecs;
END;
Bueno, este es un método realmente rápido y sucio, pero ¿qué hay de almacenar la diferencia de segundos en una columna separada (necesitarás usar un activador o actualizarlo manualmente si el registro cambia) y promediar sobre esa columna?
No parece que haya ninguna función para hacer una conversión explícita de INTERVAL DAY TO SECOND a NUMBER en Oracle. Consulte la tabla al final de este documento que implica que no hay tal conversión.
Otras fuentes parecen indicar que el método que está utilizando es la única forma de obtener un número del tipo de datos INTERVAL DAY TO SECOND .
La única otra cosa que podrías probar en este caso particular sería convertir a número antes de restarlos, pero como eso hará el doble de iones de extract , probablemente sea aún más lento:
select
avg(
(extract( second from endtime) +
extract ( minute from endtime) * 60 +
extract ( hour from endtime ) * 3600) -
(extract( second from starttime) +
extract ( minute from starttime) * 60 +
extract ( hour from starttime ) * 3600)
) from timings;
Si su hora de finalización y hora de inicio no están dentro de un segundo el uno del otro, puede convertir sus marcas de tiempo como fechas y fechas aritméticas:
select avg(cast(endtime as date)-cast(starttime as date))*24*60*60
from timings;
La forma más limpia es escribir su propia función de agregado para hacer esto, ya que manejará esto de la manera más limpia (maneja la resolución por debajo del segundo, etc.).
De hecho, esta pregunta fue formulada (y respondida) en asktom.oracle.com hace un tiempo (el artículo incluye el código fuente).
Hay una forma más corta, más rápida y más agradable que la fórmula peluda con múltiples extractos.
Solo prueba esto para obtener el tiempo de respuesta en segundos:
(sysdate + (endtime - starttime)*24*60*60 - sysdate)
También conserva fracciones de segundos al restar TIMESTAMPs.
Consulte http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html para obtener más información.
Tenga en cuenta que las funciones pl / sql personalizadas tienen una sobrecarga de rendimiento significativa que puede no ser adecuada para consultas pesadas.
Configuración del esquema Oracle 11g R2 :
Cree un tipo para usar al realizar una agregación personalizada:
CREATE TYPE IntervalAverageType AS OBJECT(
total INTERVAL DAY(9) TO SECOND(9),
ct INTEGER,
STATIC FUNCTION ODCIAggregateInitialize(
ctx IN OUT IntervalAverageType
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT IntervalAverageType,
value IN INTERVAL DAY TO SECOND
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(
self IN OUT IntervalAverageType,
returnValue OUT INTERVAL DAY TO SECOND,
flags IN NUMBER
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(
self IN OUT IntervalAverageType,
ctx IN OUT IntervalAverageType
) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY IntervalAverageType
IS
STATIC FUNCTION ODCIAggregateInitialize(
ctx IN OUT IntervalAverageType
) RETURN NUMBER
IS
BEGIN
ctx := IntervalAverageType( INTERVAL ''0'' DAY, 0 );
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT IntervalAverageType,
value IN INTERVAL DAY TO SECOND
) RETURN NUMBER
IS
BEGIN
IF value IS NOT NULL THEN
self.total := self.total + value;
self.ct := self.ct + 1;
END IF;
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateTerminate(
self IN OUT IntervalAverageType,
returnValue OUT INTERVAL DAY TO SECOND,
flags IN NUMBER
) RETURN NUMBER
IS
BEGIN
IF self.ct = 0 THEN
returnValue := NULL;
ELSE
returnValue := self.total / self.ct;
END IF;
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateMerge(
self IN OUT IntervalAverageType,
ctx IN OUT IntervalAverageType
) RETURN NUMBER
IS
BEGIN
self.total := self.total + ctx.total;
self.ct := self.ct + ctx.ct;
RETURN ODCIConst.SUCCESS;
END;
END;
/
Luego puede crear una función de agregación personalizada:
CREATE FUNCTION AVERAGE( difference INTERVAL DAY TO SECOND )
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE AGGREGATE USING IntervalAverageType;
/
Consulta 1 :
WITH INTERVALS( diff ) AS (
SELECT INTERVAL ''0'' DAY FROM DUAL UNION ALL
SELECT INTERVAL ''1'' DAY FROM DUAL UNION ALL
SELECT INTERVAL ''-1'' DAY FROM DUAL UNION ALL
SELECT INTERVAL ''8'' HOUR FROM DUAL UNION ALL
SELECT NULL FROM DUAL
)
SELECT AVERAGE( diff ) FROM intervals
| AVERAGE(DIFF) |
|---------------|
| 0 2:0:0.0 |