получение среднего значения между двумя столбцами даты с оракулом [дубликат]

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

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

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

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;

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

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

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

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 |

Существует более короткий, быстрый и приятный способ, чем эта волосатая формула с несколькими экстрактами.

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

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

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

См. http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html для некоторых деталей.

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