Forum Stats

  • 3,769,356 Users
  • 2,252,953 Discussions
  • 7,874,996 Comments

Discussions

calculate average/min and max on calculated values.

zn553
zn553 Member Posts: 8 Green Ribbon
edited Jun 7, 2021 5:02PM in SQL & PL/SQL

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'
/

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    Accepted Answer

    Hi, @zn553

    how can i make so that the min/max/avg are still displayed with 0 as value if there are no row 

    You can use NVL to map NULLs to 0, like this:

    WITH  got_request_time  AS
    (
    	SELECT a.transaction_id,
        	e.eventcode,
        	e.eventtime,
        	a.time_received,
        	(e.eventtime - a.time_received) * 86400 AS request_time   
     	FROM  request_history_tst  a  -- Why a? Why not r, or rh? 
            JOIN  eventlog_tst 	   e  ON a.transaction_id = e.transaction_id
     	WHERE 	e.eventtime >= SYSDATE - INTERVAL '10' DAY
      	AND   e.eventcode IN ('50')
    )
    ,  aggregate_data  AS
    (
    	SELECT	 NVL (AVG (request_time), 0)	AS avg_request_time
    	,	 NVL (MIN (request_time), 0)	AS min_request_time
    	,	 NVL (MAX (request_time), 0)	AS max_request_time
    	,	 COUNT (*)		  	AS total_requests
    	FROM	 got_request_time
    )
    SELECT  label, val
    FROM	 aggregate_data
    UNPIVOT	 (  val
    	 FOR                             (label,          sort_order)
    	        IN ( avg_request_time AS ('average_request_time:', 1)
    	    	   , min_request_time AS ('min_request_time:',     2)
    		   , max_request_time AS ('max_request_time:',     3)
    		   , total_requests   AS ('total_requests:',       4)
    		   )
    	 )
    ORDER BY sort_order
    ;
    


«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi, @zn553

    Thanks for posting the sample data. Don't forget to post the exact results you want from the given sample data.

    Sine this problem involves averages, wouldn't it make more sense to include more that one row in either (or both) tables?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    HI,

    Would you like to get answers that work? Make sure the CREATE TABLE and INSERT statements you post for the sample data work, too. Test (and, if necessary, fix) them before you post them.

  • zn553
    zn553 Member Posts: 8 Green Ribbon

    Hello yes indeed. i will add more examples.

    however sometimes there is no data to calculate or only one entry. as you can see in my code i calculate when eventtime is less than 10 minutes. sometimes there is no new data in the last 10 minutes or just one entry.. therefore the results for all the wished responses should be 0.

    regards

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi, @zn553

    however sometimes there is no data to calculate or only one entry.

    I agree; sometimes there re no rows, sometimes only 1 and sometimes many rows. You want a query that will work in all those situations, so you need to test all those situations. Normally, people test that by Partitioning the data; for example, they might post sample data with no rows for eventcode='50', 1 row for eventcode='51' and 3 rows for eventcode='53', then saying "I need separate results for each eventcode". If it's impossible to do that, then they test with multiple sets of sample data. E.g, "When I have only this row ..., then the desired results are .... because ... If I add this row ... then the results are .... Then if I add these rows ..., the results are ... because ...". If you absolutely must test with only one , unpartitioned sample data set, then it's best to use one of the more complicated scenarios. If you see that a query is producing the correct average for three numbers, there's a good chance that it will produce the correct average for one number. The converse is not true: there are lots of ways to get the right average for one number that get the wrong average for several.

  • zn553
    zn553 Member Posts: 8 Green Ribbon

    Hello,

    this is my test data:


      CREATE TABLE "PROMETHEUS"."EVENTLOG_TST" 
       (	"EVENT_ID" NUMBER(28,0), 
    	"TRANSACTION_ID" NUMBER(28,0), 
    	"EVENTTIME" DATE, 
    	"EVENTCODE" NUMBER(4,0)
       ) SEGMENT CREATION IMMEDIATE 
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS" ;
    
    Insert into PROMETHEUS.EVENTLOG_TST (EVENT_ID,TRANSACTION_ID,EVENTTIME,EVENTCODE) values (7638658,387673,to_date('08-JUN-21 09:49:34','DD-MON-RR HH24:MI:SS'),50);
    Insert into PROMETHEUS.EVENTLOG_TST (EVENT_ID,TRANSACTION_ID,EVENTTIME,EVENTCODE) values (7638681,387675,to_date('08-JUN-21 10:20:58','DD-MON-RR HH24:MI:SS'),50);
    Insert into PROMETHEUS.EVENTLOG_TST (EVENT_ID,TRANSACTION_ID,EVENTTIME,EVENTCODE) values (7638684,387676,to_date('08-JUN-21 10:20:59','DD-MON-RR HH24:MI:SS'),50);
    Insert into PROMETHEUS.EVENTLOG_TST (EVENT_ID,TRANSACTION_ID,EVENTTIME,EVENTCODE) values (7638688,387677,to_date('08-JUN-21 10:20:59','DD-MON-RR HH24:MI:SS'),50);
    


      CREATE TABLE "PROMETHEUS"."REQUEST_HISTORY_TST" 
       (	"TRANSACTION_ID" NUMBER(28,0), 
    	"REQUEST_STATUS" NUMBER(2,0), 
    	"REQUEST_TYPE" NUMBER(2,0), 
    	"TIME_RECEIVED" DATE
       ) SEGMENT CREATION IMMEDIATE 
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS" ;
    
    
    Insert into PROMETHEUS.REQUEST_HISTORY_TST (TRANSACTION_ID,REQUEST_STATUS,REQUEST_TYPE,TIME_RECEIVED) values (387673,3,1,to_date('08-JUN-21 09:49:33','DD-MON-RR HH24:MI:SS'));
    Insert into PROMETHEUS.REQUEST_HISTORY_TST (TRANSACTION_ID,REQUEST_STATUS,REQUEST_TYPE,TIME_RECEIVED) values (387675,3,1,to_date('08-JUN-21 10:20:56','DD-MON-RR HH24:MI:SS'));
    Insert into PROMETHEUS.REQUEST_HISTORY_TST (TRANSACTION_ID,REQUEST_STATUS,REQUEST_TYPE,TIME_RECEIVED) values (387676,3,1,to_date('08-JUN-21 10:20:57','DD-MON-RR HH24:MI:SS'));
    Insert into PROMETHEUS.REQUEST_HISTORY_TST (TRANSACTION_ID,REQUEST_STATUS,REQUEST_TYPE,TIME_RECEIVED) values (387677,3,1,to_date('08-JUN-21 10:20:59','DD-MON-RR HH24:MI:SS'));
    

    i would like to get the diff between request_history_tst.time_received - eventlog_tst.eventime and get the min/max/average and the total transactions.

    this is my current script:


            set serveroutput on
    whenever sqlerror exit sql.sqlcode;
    set echo off
    SET VERIFY OFF
    set heading off
    SET FEEDBACK OFF
    set serveroutput on size unlimited
    set linesize 300
    ALTER session SET TIME_ZONE='GMT';
    Declare
    vv_SchemaName VARCHAR2(15);
    BEGIN
    vv_SchemaName := Sys_Context('USERENV','CURRENT_SCHEMA');
    FOR REVOKED IN (
    select  a.transaction_id as trans_Id,
            e.eventcode,
            e.eventtime,
            a.time_received,
            round((e.eventtime - a.time_received) * 86400) as elapsed
      from      request_history_tbl a 
            join
                eventlog_tbl e
              on a.transaction_id = e.transaction_id
      where e.eventtime >= current_timestamp - 2
        and e.eventcode = '50'
        )
        LOOP
        dbms_output.put_line('average_request_time' || ...)
        dbms_output.put_line('Min_request_time' || ...)
        dbms_output.put_line('max_request_time' || ...)
       
        END LOOP;
    END;
    /
    

    I do not know how to calculate the values to put in the dbms_output lines i would like something like this:


     average_request_time: 1.25

     min_request_time: 0

     max_request_time: 2

     total_requests: 4 (there are four transactions that match eventcode=50. in my sample data)

    thank you.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi,

    Here's one way, using pure SQL:

    WITH  got_request_time  AS
    (
    	SELECT  a.transaction_id,
        	        e.eventcode,
        	        e.eventtime,
        	        a.time_received,
        	        (e.eventtime - a.time_received) * 86400 AS request_time   
     	FROM    request_history_tst a  -- Why a? Why not r, or rh? 
            JOIN    eventlog_tst 	    e  ON a.transaction_id = e.transaction_id
     	WHERE 	e.eventtime >= SYSDATE - INTERVAL '10' DAY
      	AND     e.eventcode IN ('50')
    )
    SELECT	 AVG (request_time)	AS avg_request_time
    ,	 MIN (request_time)	AS min_request_time
    ,	 MAX (request_time)	AS max_request_time
    ,	 COUNT (*)		AS total_requests
    ,	 eventcode -- if wanted
    FROM	 got_request_time
    GROUP BY eventcode -- if wanted
    ;
    

    If you need to use PL/SQL for some reason, you can run the query above in PL/SQL.

    When I ran this (around 13:00 on June 9, 2021, there were no rows that with eventtime in the last 10 minutes. I changed the condition to be the last 10 days.

    As posted, the query lets you look for multiple eventcodes at once, and produces a separate average, min, max and count for each. If you just want one row of output that has the average, min, max and count of all the rows that met the criteria, then omit the two lines with the comments -- if wanted .

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    By the way:

    whenever sqlerror exit sql.sqlcode;
    

    It's questionable whether you should do this in Production, but you should definitely NOT do it when developing and debugging code. If you cause an error, then you want to see the error message, and you want to make it easy to try again.

  • zn553
    zn553 Member Posts: 8 Green Ribbon

    thank you.

    but I need it one per line like the output of dbms_output.

    i run this on a shell script like this:


    #!/usr/bin/bash
    
    
    catch_error() {
          echo "Error in line $1"
    }
    
    
    trap 'catch_error $LINENO' ERR
    
    
    
    {
    $ORACLE_HOME/bin/sqlplus -s /nolog      <<EOF1
    connect prometheus/${DBPASSWD}@TEST_DB
            whenever sqlerror exit sql.sqlcode;
            set echo off
            set heading off
            set serveroutput on
            @unicert2.sql
    EOF1
    } > prometheus.prom
    

    this will create a file on disk with one item per line with average/min/max... this will be feed to a monitoring tool

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    hI, @zn553

    but I need it one per line like the output of dbms_output.

    Then you can unpivot the results like this:

    WITH  got_request_time  AS
    (
    	SELECT  a.transaction_id,
        	        e.eventcode,
            	e.eventtime,
        	        a.time_received,
        	        (e.eventtime - a.time_received) * 86400 AS request_time   
     	FROM    request_history_tst  a  -- Why a? Why not r, or rh? 
            JOIN    eventlog_tst 	   e  ON a.transaction_id = e.transaction_id
     	WHERE 	e.eventtime >= SYSDATE - INTERVAL '10' DAY
      	AND     e.eventcode IN ('50')
    )
    ,  aggregate_data  AS
    (
    	SELECT	 AVG (request_time)	AS avg_request_time
    	,	 MIN (request_time)	AS min_request_time
    	,	 MAX (request_time)	AS max_request_time
    	,	 COUNT (*)		AS total_requests
    	FROM	 got_request_time
    )
    SELECT  label, val
    FROM	 aggregate_data
    UNPIVOT	 (  val          	   FOR (label,          sort_order)
    	      IN ( avg_request_time AS ('average_request_time:', 1)
    	    	 , min_request_time AS ('min_request_time:',     2)
    		 , max_request_time AS ('max_request_time:',     3)
    		 , total_requests   AS ('total_requests:',       4)
    		 )
    	 )
    ORDER BY sort_order
    ;
    

    Notice that the sub-queries are just what was posted before.

  • zn553
    zn553 Member Posts: 8 Green Ribbon

    that is very helpful.

    thank you.

    I notice that if there are no rows then it only displays total requests.

    how can i make so that the min/max/avg are still displayed with 0 as value if there are no row or new request in the timeframe requested?

    regards.