평균 시간 간격은 어떻게 합니까?

평균 시간 간격은 어떻게 합니까?

Oracle 10g에는 특정 작업에 소요된 시간을 보여주는 타임스탬프가 포함된 테이블이 있습니다.시작 시간과 종료 시간이라는 두 개의 타임스탬프 필드가 있습니다.저는 이 타임스탬프에 의해 주어진 기간의 평균을 찾고 싶습니다.시도:

select avg(endtime-starttime) from timings;

그러나 가져오기:

SQL 오류: ORA-00932: 일관성 없는 데이터 유형: 예상 NUMBER에서 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;

여러 추출물이 포함된 털이 많은 공식보다 오라클에서 DATTIME 차이를 몇 초 만에 얻을 수 있는 더 짧고 빠르고 좋은 방법이 있습니다.

응답 시간(초)을 확인하려면 다음과 같이 하십시오.

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

또한 TIMESTAMP를 뺄 때 초 단위의 부분을 보존합니다.

자세한 내용은 http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html 을 참조하십시오.

---

사용자 지정 pl/sql 함수에는 과도한 쿼리에 적합하지 않을 수 있는 상당한 성능 오버헤드가 있습니다.

종료 시간과 시작 시간이 1초 이내에 있지 않으면 타임스탬프를 날짜로 캐스팅하고 날짜 산술을 수행할 수 있습니다.

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

의 명시적인 변환을 수행할 기능이 없는 것으로 보입니다.INTERVAL DAY TO SECONDNUMBER오라클에서.이 문서의 끝에 있는 표를 참조하십시오. 이 표는 이러한 변환이 없음을 의미합니다.

다른 출처들은 당신이 사용하는 방법이 당신으로부터 번호를 얻는 유일한 방법임을 나타내는 것 같습니다.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;

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가 나중에 발견한 것처럼) 가장 좋은 대답은 사용자 정의 함수를 작성하여 간격을 숫자로 변환하고, 평균을 낸 다음 (선택적으로) 다시 간격으로 변환하는 것입니다. 12에서는 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(±), 정밀도(±100일 이상은 오버플로임을 의미), 2자리 정밀도(±100일)SECOND6자리로 축척합니다.

또한 매개 변수에 정밀도/척도를 지정하려고 하면 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로 작성된 사용자 지정 Aggregate 함수를 지원하므로 문을 최소한으로 변경할 수 있습니다.

select ds_avg(endtime-starttime) from timings;

그러나 이 방법에는 몇 가지 주요 단점이 있습니다.

• 데이터베이스에 PL/SQL Aggregate 개체를 생성해야 합니다. 이 개체는 필요하지 않거나 허용되지 않을 수 있습니다.
• 이름을 지정할 수 없습니다.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μs)보다 수 .00:16:40크기가 크기 때문에 작은 간격에도 안전하지만 모두 안전하지는 않습니다.

마지막으로, 만약 당신이 모든 1초 미만의 정밀도를 잃는 것이 편하다면, 당신은 다음을 캐스팅할 수 있습니다.TIMESTAMP에서 의까지.DATEa를 DATEa부터DATE두 번째 정밀도로 일 수를 반환합니다(@precitorr로 크레딧).

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

언급URL : https://stackoverflow.com/questions/450581/how-to-average-time-intervals