如何平均时间间隔? [英] How to average time intervals?
问题描述
在Oracle 10g中,我有一个表,其中包含显示某些操作花费了多长时间的时间戳记.它有两个时间戳字段:开始时间和结束时间.我想找到这些时间戳记给出的持续时间的平均值.我尝试:
In Oracle 10g I have a table that holds timestamps showing how long certain operations took. It has two timestamp fields: starttime and endtime. I want to find averages of the durations given by these timestamps. I try:
select avg(endtime-starttime) from timings;
但是得到:
SQL错误:ORA-00932:不一致 数据类型:预期获得NUMBER个 第二天的间隔
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got 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;
但是真的很慢.
有什么更好的方法可以将间隔转换为秒数,或者通过其他某种方式呢?
Any better way to turn intervals into numbers of seconds, or some other way do this?
真正减慢速度的原因是我在开始时间之前有一些结束时间.由于某种原因,这使计算变得异常缓慢.通过从查询集中消除它们,解决了我的基本问题.我还只是定义了一个函数来简化此转换:
What was really slowing this down was the fact that I had some endtime's before the starttime's. For some reason that made this calculation incredibly slow. My underlying problem was solved by eliminating them from the query set. I also just defined a function to do this conversion easier:
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;
推荐答案
最干净的方法是编写自己的聚合函数来执行此操作,因为它将最干净地处理此问题(处理亚秒级分辨率等).
The cleanest way is to write your own aggregate function to do this, since it will handle this the most cleanly (handles sub-second resolution, etc.).
In fact, this question was asked (and answered) on asktom.oracle.com a while back (article includes source code).
这篇关于如何平均时间间隔?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!