Skip to Main Content

calculate average/min and max on calculated values.

zn553Jun 7 2021 — edited Jun 7 2021

for monitoring and statistics purposes i need to calculate the min/max/avg on the duration a requests takes
the data is stored in two tables. e

CREATE TABLE "SCOTT"."REQUEST_HISTORY_TBL"
   (    "TRANSACTION_ID" NUMBER(28,0) NOT NULL ENABLE,
        "TIME_RECEIVED" DATE NOT NULL ENABLE
)

CREATE TABLE "SCOTT"."EVENTLOG_TBL"
   (    "EVENT_ID" NUMBER(28,0) NOT NULL ENABLE,
        "TRANSACTION_ID" NUMBER(28,0) NOT NULL ENABLE,
        "EVENTTIME" DATE NOT NULL ENABLE,
        "EVENTCODE" NUMBER(4,0) NOT NULL ENABLE
)


INSERT INTO SCOTT.EVENTLOG_TBL (EVENT_ID, TRANSACTION_ID, EVENTTIME, EVENTCODE)
VALUES ('1', '0001', TO_DATE('06/06/2021 14:08:25', 'MM/DD/YYYY HH24:MI:SS', '50'));

INSERT INTO SCOTT.REQUEST_HISTORY_TBL (TRANSACTION_ID, TIME_RECEIVED)
VALUES ('0001', TO_DATE('06/06/2021 14:08:23', 'MM/DD/YYYY HH24:MI:SS'));

I need to calculate the difference in seconds between TIME_RECEIVED on table REQUEST_HISTORY_TBL and EVENTTIME on table EVENTLOG_TBL. where EVENTIME is less or equal to 10 minutes ago. and output 4 values:
min time elapsed
max time elapsed
average time elapsed
amount of transaction in the last 10 minutes.
I am using oracle 19.0.0.0.0
how can i calculate the average the min and the max of diff_in_seconds?
and print it with dbms_output.put_line the desired output is something like this:
average_request_time = 0.5 (s)
min_request_time = 0 (s)
max_request_time = 2(s)
total_requests_10m = 5
here I calculate the time difference between the columns in seconds but i do not manage to calculate the average or get the min/max.

alter session set current_schema = SCOTT;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
ALTER session SET TIME_ZONE='GMT';
select  a.transaction_id,
        e.eventcode,
        e.eventtime,
        a.time_received,
        (e.eventtime - a.time_received) * 86400  as diff_in_seconds       
  from      request_history_tbl a 
        join
            eventlog_tbl e
          on a.transaction_id = e.transaction_id
  where e.eventtime >= current_timestamp - 10/1440
    and e.eventcode = '50'
/

This post has been answered by Frank Kulash on Jun 10 2021
Jump to Answer
Comments
Post Details
Added on Jun 7 2021
11 comments
960 views