Wie zu durchschnittlichen Abständen?

In Oracle 10g habe ich eine Tabelle, die hält Zeitstempel zeigt an, wie lange bestimmte Vorgänge nahm. Es hat zwei timestamp Felder: starttime und endtime. Ich möchte zu finden, die Mittelwerte der Laufzeiten gegeben durch diese Zeitstempel. Ich versuche:

select avg(endtime-starttime) from timings;

Aber erhalten:

SQL-Fehler: ORA-00932: inkonsistente
datatypes: expected NUMBER got
INTERVAL DAY TO SECOND

Dies funktioniert:

select
     avg(extract( second from  endtime - starttime) +
        extract ( minute from  endtime - starttime) * 60 +
        extract ( hour   from  endtime - starttime) * 3600) from timings;

Ist aber wirklich langsam.

Einen besseren Weg zu drehen Abständen in die Anzahl von Sekunden, oder auf eine andere Weise, dies zu tun?

BEARBEITEN:
Was war wirklich verlangsamen diese nach unten, war die Tatsache, dass ich hatte einige Endzeit vor der Startzeit. Für einige Grund, dass aus dieser Berechnung unglaublich langsam. Meine zugrunde liegende problem wurde gelöst durch die Beseitigung von Ihnen aus der Abfrage gesetzt. Ich habe auch gerade definierten Funktion zu tun, diese Umstellung leichter:

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;

InformationsquelleAutor der Frage Justsalt | 2009-01-16

6

Der sauberste Weg ist, schreiben Sie Ihre eigene Aggregat-Funktion um dies zu tun, da wird es handhaben, die meisten sauber (Griffe, sub-Sekunden-Auflösung, etc.).

In der Tat, diese Frage gestellt (und beantwortet) auf asktom.oracle.com eine Weile zurück (Artikel enthält Quellcode).

InformationsquelleAutor der Antwort Chris R. Donnelly
17

Da gibt es eine kürzere, schnellere und schönere Weg als haarige Formel mit mehreren Auszügen.

Nur versuchen, diese zu bekommen Reaktionszeit in Sekunden:
```
(sysdate + (endtime - starttime)*24*60*60 - sysdate)
```
Es bewahrt auch den Bruchteil von Sekunden, wenn die Subtraktion der Zeitstempel.

Sehen http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html für einige details.

Beachten Sie, dass benutzerdefinierte pl/sql-Funktionen haben erheblichen Performance-overheaddie möglicherweise nicht geeignet für schwere Abfragen.

InformationsquelleAutor der Antwort Vadzim
9

Wenn Ihr endtime und starttime sind nicht innerhalb einer Sekunde von einander, Sie können werfen Sie Ihre Zeitstempel als Datum und tun Datums-Arithmetik:
```
select avg(cast(endtime as date)-cast(starttime as date))*24*60*60 
  from timings;
```
InformationsquelleAutor der Antwort jimmyorr
2

Sieht es nicht so wie es irgendeine Funktion führen Sie eine explizite Umwandlung von INTERVAL DAY TO SECOND zu NUMBER in Oracle. Finden Sie in der Tabelle am Ende dieses Dokument was bedeutet, es gibt keine solche Umwandlung.

Anderen Quellen scheinen zu zeigen, dass die Methode, die Sie verwenden, ist der einzige Weg, um eine Nummer aus der INTERVAL DAY TO SECOND Datentyp.

Die einzige andere Sache, die Sie könnten versuchen, in diesem speziellen Fall wäre in eine Zahl umwandeln, bevor subtrahieren, aber da Mach doppelt so viele extract - Ionen, wird es wahrscheinlich noch langsamer:
```
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;
```
InformationsquelleAutor der Antwort Adam Bellaire
1

Gut, das ist wirklich ein quick-and-dirty Methode, aber was ist mit der Speicherung der Sekunden Unterschied in einer separaten Spalte (Sie müssen einen trigger verwenden oder Sie manuell aktualisieren Sie dies, wenn der Datensatz geändert wird) und Mittelung über die Spalte?

InformationsquelleAutor der Antwort Bork Blatt

SQL Fiddle

Oracle 11g R2-Schema-Setup:

Erstellen Sie eine Art zu verwenden, wenn Sie eine benutzerdefinierte aggregation:

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;
/

Dann können Sie erstellen eine benutzerdefinierte aggregation-Funktion:

CREATE FUNCTION AVERAGE( difference INTERVAL DAY TO SECOND )
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE AGGREGATE USING IntervalAverageType;
/

Abfrage 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

Ergebnisse:

| AVERAGE(DIFF) |
|---------------|
|     0 2:0:0.0 |

InformationsquelleAutor der Antwort MT0

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.