如何计算时间间隔的平均值？

Question

如何计算时间间隔的平均值？

18

在Oracle 10g中，我有一张表格，其中包含显示某些操作所需时间的时间戳。它有两个时间戳字段：starttime和endtime。我想找到这些时间戳所指示的持续时间的平均值。我尝试了以下代码:

select avg(endtime-starttime) from timings;

但是得到：

SQL错误：ORA-00932：数据类型不一致：期望数字而得到间隔时间（INTERVAL DAY TO SECOND）

这个有效：

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

但非常慢。

有没有更好的方法将时间间隔转换为秒数，或者其他方式来完成此操作？

编辑：真正导致速度变慢的原因是我的一些结束时间早于开始时间。由于某种原因，这使得计算非常缓慢。通过从查询集中消除它们，解决了我的根本问题。我还定义了一个函数来更轻松地进行此转换：

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;

- Justsalt

6个回答

10

如果您的结束时间和开始时间相差不到一秒钟，您可以将您的时间戳转换为日期，并进行日期运算：

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

- uglysmurf

这将丢失时间戳中的任何小数秒（无论它们是否彼此相差一秒）。 - MT0

2

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 |

- MT0

2

在Oracle中似乎没有任何函数可以将INTERVAL DAY TO SECOND明确转换为NUMBER。请参见此文档末尾的表格，这表明不存在这样的转换方式。

其他来源似乎表明，您正在使用的方法是从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;

- Adam Bellaire

1

嗯，这是一种非常快速和简单的方法，但是如果将秒差存储在单独的列中（如果记录更改，则需要使用触发器或手动更新此列），并对该列进行平均处理呢？

- Bork Blatt

1

如果你想要这样做，可以使用基于函数的索引（fbi），这可以省去触发器或手动更新列的步骤。fbi可以用在where子句和select子句中。 - tuinstoel

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: 遇到符号"("时，期望以下一种：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;

- Chris R. Donnelly

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vadzim · Accepted Answer

在Oracle中，有一种更短、更快、更好的方法可以获得DATETIME时间差异，而不是使用多个提取操作的复杂公式。

只需尝试这个方法，以获取响应时间（以秒为单位）：

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

在减去 TIMESTAMP 时，它还会保留秒的小数部分。

有关详细信息，请参见此处。

请注意，自定义 PL/SQL 函数具有显着的性能开销，可能不适合于大型查询。

如何计算时间间隔的平均值？

使用dsinterval_unconstrained

备选方案

自定义聚合函数

日期算术运算

使用`dsinterval_unconstrained`