需要在Oracle SQL中找到所有时间戳记录之间的平均处理时间 [英] Need to find average processing time between all timestamp records in Oracle SQL

问题描述

我在SQL/Oracle中还很陌生，遇到了我需要组合的两个查询:

I am fairly new in SQL/Oracle, and have come across two queries I needed to put together:

1.)查找以天为单位的时间，HH:MM:SS处理一条记录所需的时间，并且 2.)根据所有时间戳查找所选结果的平均处理时间

1.) Find the time in Days, HH:MM:SS it takes to process one record AND 2.) Find the average processing time based on all timestammps for the selected results

我解决了1号(也许不是最好的方法)，并留下了2号查询，这让我很头疼.我会尝试用数据解释...

I solved number 1 (maybe not the best way) and am left with the number 2 query giving me headaches. I will try to explain with data ...

因此，为了解决数字1，我​​编写了以下查询，该查询产生了许多结果，该摘录显示在查询下方.

So, to solve number 1, I wrote the following query which produced many results, an excerpt of which is shown below the query..

SELECT to_number( to_char(to_date('1','J') + (t_modified - (t_created - (0/24))), 'J') - 1)  days,
to_char(to_date('00:00:00','HH24:MI:SS') +   
(t_modified - (t_created - (0/24))), 'HH24:MI:SS') TimeInQueue, message_no, id, t_created, t_modified
from TABLE   
where message_no in (MESSAGENUMBER) and status = 1 and t_modified > sysdate-2 order by t_created;

此查询产生了以下内容: 我是该论坛的新手，所以无法在我的问题中发布图像，因此这里是一个链接: http://imagesup.net/?di=313891989943

This query produced this: I am new to the forum so I cannot post the image in my problem, so here is a link: http://imagesup.net/?di=313891989943

现在，我实际上陷入的困境是……在T_MODIFIED字段中，对于给定的sysdate -1时间段，我需要找到所有记录的T_MODIFIED值之间的平均值.因此，这里的最终目标是找到整个选定范围(sysdate-1，-2或您拥有的东西)处理这些记录所需的平均时间.因此，我需要在T_MODIFIED列中获取所有那些JAN 8日期，并在它们之间找到一个平均时间.我真的希望这是有道理的.如有任何疑问，我会尝试指定更好的方法.

Now, where I am actually stuck is this ... In the T_MODIFIED field, for the given timespan of let's say sysdate -1 I need to find the average between ALL records' T_MODIFIED values. So the end goal here is to find the average time it is taking to process these records for the entire range selected (sysdate-1, -2 or what have you). So I need to take all of those JAN 8 dates in the T_MODIFIED column and find one average time between them all. I really hope this makes sense. If there is any question, I will try to specify better.

谢谢！

推荐答案

在Oracle中，执行时间间隔，尤其是跨多个间隔或间隔组进行聚合(求和，平均值)并不是一件容易的事. AVG功能在时间戳间隔上不起作用，它需要数字.因此，我们需要创建自己的聚合对象和函数来实现此目的.

Doing time intervals, and especially doing aggregations (sums, averages) across many intervals or interval groups is not straightforward in Oracle. The AVG function doesn't work on timestamp intervals, it expects numbers. So, we'll need to create our own aggregation object and function that will do this.

首先，对象规范 :

First, the object spec:

CREATE OR REPLACE TYPE AvgInterval 
AS OBJECT (
runningSum INTERVAL DAY(9) TO SECOND(9),
runningCnt number,

STATIC FUNCTION ODCIAggregateInitialize
  ( actx IN OUT AvgInterval
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate
  ( self  IN OUT AvgInterval,
    val   IN       DSINTERVAL_UNCONSTRAINED
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate
  ( self             IN   AvgInterval,
    returnValue  OUT DSINTERVAL_UNCONSTRAINED,
    flags           IN   NUMBER
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge
  (self  IN OUT AvgInterval,
   ctx2 IN      AvgInterval
  ) RETURN NUMBER
);

和对象主体 :

And the object body:

CREATE OR REPLACE TYPE BODY AvgInterval AS
STATIC FUNCTION ODCIAggregateInitialize
  ( actx IN OUT AvgInterval
  ) RETURN NUMBER IS 
  BEGIN
    IF actx IS NULL THEN
      actx := AvgInterval (INTERVAL '0 0:0:0.0' DAY TO SECOND, 0);
    ELSE
      actx.runningSum := INTERVAL '0 0:0:0.0' DAY TO SECOND;
      actx.runningCnt := 0;
    END IF;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateIterate
  ( self  IN OUT AvgInterval,
    val   IN     DSINTERVAL_UNCONSTRAINED
  ) RETURN NUMBER IS
  BEGIN
    self.runningSum := self.runningSum + val;
    self.runningCnt := self.runningCnt + 1;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateTerminate
  ( self        IN  AvgInterval,
    ReturnValue OUT DSINTERVAL_UNCONSTRAINED,
    flags       IN  NUMBER
  ) RETURN NUMBER IS
  BEGIN
    if (runningCnt <> 0) then
        returnValue := (self.runningSum/runningCnt);
    else
        returnValue := self.runningSum;
    end if;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateMerge
  (self IN OUT AvgInterval,
   ctx2 IN     AvgInterval
  ) RETURN NUMBER IS
  BEGIN
    self.runningSum := self.runningSum + ctx2.runningSum;
    self.runningCnt := self.runningCnt + ctx2.runningCnt;
    RETURN ODCIConst.Success;
  END;

END;

最后，使用上述对象的函数 :

CREATE OR REPLACE FUNCTION avg_interval( x DSINTERVAL_UNCONSTRAINED) 
RETURN DSINTERVAL_UNCONSTRAINED  PARALLEL_ENABLE
AGGREGATE USING AvgInterval;

现在，我们可以像这样 使用:

with x as (
    select systimestamp - 1/24 as created_date, systimestamp as modified_date from dual
    union
    select systimestamp - 2/24 as created_date, systimestamp as modified_date from dual
    union
    select systimestamp - 3/24 as created_date, systimestamp as modified_date from dual
)
select avg_interval(modified_date - created_date)
from x;

输出:

+00 02:00:00.562669

我们还可以通过此来汇总各个组:

with x as (
    select 'FL' as state, to_dsinterval('0 00:56:30') as duration from dual
    union
    select 'FL' as state, to_dsinterval('0 02:08:40') as duration from dual
    union
    select 'GA' as state, to_dsinterval('0 01:01:00') as duration from dual
)
select state, avg_interval(duration)
from x
group by state;

输出:

FL  +00 01:32:35.000000
GA  +00 01:01:00.000000

这篇关于需要在Oracle SQL中找到所有时间戳记录之间的平均处理时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！