Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
2,484 views