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

Существует более короткий, быстрый и приятный способ получить разницу 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 имеют значительные накладные расходы, которые могут не подходить для тяжелых запросов.

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

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

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

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

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

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;

скрипт SQL

Настройка схемы 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;