Как средние интервалы времени?

Ответ 1

Самый чистый способ - написать свою собственную агрегатную функцию, чтобы сделать это, поскольку он будет обрабатывать это наиболее чисто (обрабатывает подсезонное разрешение и т.д.).

На самом деле этот вопрос был задан (и ответил) на asktom.oracle.com некоторое время назад (статья содержит исходный код).

Ответ 2

Существует более короткий, быстрый и лучший способ получить DATETIME разницу в секундах в Oracle, чем эта волосатая формула с несколькими экстрактами.

Просто попробуйте это, чтобы получить время отклика в секундах:

(sysdate + (endtime - starttime)*24*60*60 - sysdate)

Он также сохраняет дробную часть секунд при вычитании TIMESTAMP.

Для получения дополнительной информации см. Http://kenneспасибоu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html.

Обратите внимание, что пользовательские функции pl/sql имеют значительную служебную нагрузку, которая может быть непригодна для тяжелых запросов.

Ответ 3

Если ваше время окончания и время начала не находятся в пределах секунды друг от друга, вы можете отбрасывать свои отметки времени как даты и выполнять арифметику даты:

select avg(cast(endtime as date)-cast(starttime as date))*24*60*60 
  from timings;

Ответ 4

Не похоже, что есть какая-либо функция для явного преобразования INTERVAL DAY TO SECOND в NUMBER в Oracle. См. Таблицу в конце этот документ, который подразумевает, что такого преобразования нет.

Другие источники, похоже, указывают, что метод, который вы используете, является единственным способом получить число из типа данных INTERVAL DAY TO SECOND.

Единственное, что вы могли бы попробовать в этом конкретном случае, - это преобразовать в число перед их вычитанием, но поскольку это будет делать в два раза больше ионов extract, это, вероятно, будет еще медленнее:

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;

Ответ 5

SQL Fiddle

Настройка схемы Oracle 11g R2:

Создайте тип, который будет использоваться при выполнении пользовательской агрегации:

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

Затем вы можете создать настраиваемую функцию агрегации:

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

Запрос 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 |

Ответ 6

Ну, это действительно быстрый и грязный метод, но как насчет сохранения разницы секунд в отдельном столбце (вам нужно будет использовать триггер или вручную обновить его, если запись изменится) и усреднение по этому столбцу?