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

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

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

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

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;

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

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

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

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

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

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

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

Подробнее см. http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html.

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

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 |

К сожалению, Oracle не поддерживает большинство функций с интервалами. Для этого есть несколько обходных путей, но все они имеют какой-то недостаток (и, в частности, ни один из них не совместим с ANSI-SQL).

Лучший ответ (как позже обнаружил @justsalt) - написать специальную функцию для преобразования интервалов в числа, усреднения чисел и последующего (необязательно) преобразования обратно в интервалы. Oracle 12.1 и более поздние версии поддерживают это с помощью блока WITH для объявления функции:

with
    function fn_interval_to_sec(i in dsinterval_unconstrained)
        return number is
    begin
        return ((extract(day from i) * 24
               + extract(hour from i) )*60
               + extract(minute from i) )*60
               + extract(second from i);
    end;
select numtodsinterval(avg(fn_interval_to_sec(endtime-starttime)), 'SECOND') 
  from timings;

Если вы используете 11.2 или более раннюю версию, или если вы предпочитаете не включать функции в свои операторы SQL, вы можете объявить это как хранимую функцию:

create or replace function fn_interval_to_sec(i in dsinterval_unconstrained)
    return number is
begin
    return ((extract(day from i) * 24
           + extract(hour from i) )*60
           + extract(minute from i) )*60
           + extract(second from i);
end;

Затем вы можете использовать его в SQL, как и ожидалось:

select numtodsinterval(avg(fn_interval_to_sec(endtime-starttime)), 'SECOND') 
  from timings;

Использование dsinterval_unconstrained

Использование псевдонима типа PL / SQL dsinterval_unconstrained для параметра функции обеспечивает максимальную точность / масштаб; INTERVAL DAY TO SECOND по умолчанию устанавливает точность DAY до 2 цифр (что означает, что все, что не превышает ± 100 дней, является переполнением и вызывает исключение), а SECOND масштабируется до 6 цифр.

Кроме того, Oracle 12.1 вызовет ошибку PL / SQL, если вы попытаетесь указать любую точность / масштаб в своем параметре:

with
    function fn_interval_to_sec(i in interval day(9) to second(9))
        return number is
        ...

ORA-06553: PLS-103: Encountered the symbol "(" when expecting one of the following: to

Альтернативы

Пользовательская агрегатная функция

Oracle поддерживает настраиваемые агрегатные функции, написанные на PL / SQL, что позволит вам вносить минимальные изменения в оператор:

select ds_avg(endtime-starttime) from timings;

Однако у этого подхода есть несколько серьезных недостатков:

• Вы должны создать Агрегатные объекты PL / SQL в своей базе данных, что может быть нежелательно или разрешено;
• Вы не можете назвать его avg, поскольку Oracle всегда будет использовать встроенную функцию avg, а не вашу собственную. (Технически вы можете, но тогда вы должны квалифицировать это с помощью схемы, что противоречит цели.)
• Как отметил @vadzim, агрегированные функции PL / SQL имеют значительные накладные расходы на производительность.

Дата арифметика

Если ваши значения не сильно различаются, подход @vadzim также работает:

select avg((sysdate + (endtime-starttime)*24*60*60*1000000 - sysdate)/1000000.0) 
  from timings;

Однако имейте в виду, что если интервал слишком велик, выражение (endtime-starttime)*24*60*60*1000000 переполнится и выдаст ORA-01873: the leading precision of the interval is too small. При такой точности (1 мкс) разница не может быть больше или равна 00:16:40 по величине, поэтому она безопасна для небольших интервалов, но не для всех.

Наконец, если вам удобно потерять всю субсекундную точность, вы можете преобразовать столбцы TIMESTAMP в DATE; вычитание DATE из DATE вернет количество дней со второй точностью (кредит @jimmyorr):

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