SQL 如何平均时间间隔？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/450581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to average time intervals?
提问by Justsalt
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:
在 Oracle 10g 中，我有一个包含时间戳的表，显示某些操作花费了多长时间。它有两个时间戳字段：starttime 和 endtime。我想找到这些时间戳给出的持续时间的平均值。我尝试：
select avg(endtime-starttime) from timings;
But get:
但是得到：
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
SQL 错误：ORA-00932：不一致的数据类型：预期的 NUMBER 时间为 INTERVAL DAY TO SECOND
This works:
这有效：
select
avg(extract( second from endtime - starttime) +
extract ( minute from endtime - starttime) * 60 +
extract ( hour from endtime - starttime) * 3600) from timings;
But is really slow.
但是真的很慢。
Any better way to turn intervals into numbers of seconds, or some other way do this?
有没有更好的方法将间隔转换为秒数，或者其他一些方法呢？
EDIT: 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;
采纳答案by Chris R. Donnelly
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.coma while back (article includes source code).
事实上，这个问题不久前曾在asktom.oracle.com上被问过（并回答过）（文章包括源代码）。
回答by Vadzim
There is a shorter, faster and nicer way to get DATETIME difference in seconds in Oracle than that hairy formula with multiple extracts.
与具有多个提取物的毛茸茸的公式相比，有一种更短、更快、更好的方法可以在 Oracle 中以秒为单位获得 DATETIME 差异。
Just try this to get response time in seconds:
试试这个以获得以秒为单位的响应时间：
(sysdate + (endtime - starttime)*24*60*60 - sysdate)
It also preserves fractional part of seconds when subtracting TIMESTAMPs.
在减去 TIMESTAMP 时，它还保留秒的小数部分。
See http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.htmlfor some details.
有关详细信息，请参阅http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html。
Note that custom pl/sql functions have significant performace overheadthat may be not suitable for heavy queries.
请注意，自定义 pl/sql 函数具有显着的性能开销，可能不适合繁重的查询。
回答by jimmyorr
If your endtime and starttime aren't within a second of eachother, you can cast your timestamps as dates and do date arithmetic:
如果您的结束时间和开始时间不在彼此的一秒内，您可以将时间戳转换为日期并进行日期算术：
select avg(cast(endtime as date)-cast(starttime as date))*24*60*60
from timings;
回答by Adam Bellaire
It doesn't look like there is any function to do an explicit conversion of INTERVAL DAY TO SECONDto NUMBERin Oracle. See the table at the end of this documentwhich implies there is no such conversion.
看起来没有任何函数INTERVAL DAY TO SECOND可以NUMBER在 Oracle 中对to进行显式转换。请参阅本文档末尾的表格，这意味着没有此类转换。
Other sources seem to indicate that the method you're using is the only way to get a number from the INTERVAL DAY TO SECONDdatatype.
其他来源似乎表明您使用的方法是从INTERVAL DAY TO SECOND数据类型中获取数字的唯一方法。
The only other thing you could try in this particular case would be to convert to number before subtracting them, but since that'll do twice as many extractions, it will likely be even slower:
在这种特殊情况下，您唯一可以尝试的另一件事是在减去它们之前转换为数字，但由于这样做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;
回答by MT0
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
Create a type to use when performing a custom aggregation:
创建一个在执行自定义聚合时使用的类型：
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;
/
Then you can create a custom aggregation function:
然后你可以创建一个自定义聚合函数：
CREATE FUNCTION AVERAGE( difference INTERVAL DAY TO SECOND )
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE AGGREGATE USING IntervalAverageType;
/
Query 1:
查询 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 |
回答by Bork Blatt
Well, this is a really quick and dirty method, but what about storing the seconds difference in a separate column (you'll need to use a trigger or manually update this if the record changes) and averaging over that column?
嗯，这是一个非常快速和肮脏的方法，但是如何将秒差存储在单独的列中（如果记录发生变化，您将需要使用触发器或手动更新它）并对该列求平均值呢？
