Average of Feed / Event Arrival Time for SLO-CodePudding

We have a table where Feed name and it's arrival times are captured. Was trying to get average feed arrival time, tried two ways to do that:

captured only time, converted it to minutes, used avg function and then converted back to time
captured only time, used median function

But in both the cases issue comes when the feed arrival time is near to mid night where sometimes feed comes before mid night e.g. 23:40 and sometimes after midnight e.g. 12:22. In this case the average comes as middle of the day due to values.

Can someone help how a query can be built in ORACLE to capture average time in such scenario?

8/31/2022 12:02:09.434916 AM

8/30/2022 12:51:01.950574 AM

8/26/2022 11:55:16.899643 PM

8/26/2022 12:32:16.250415 AM

8/25/2022 12:11:48.869983 AM

8/23/2022 11:40:21.081020 PM

8/23/2022 12:16:59.638991 AM

8/19/2022 11:19:15.580283 PM

8/18/2022 11:23:03.077817 PM

8/17/2022 11:19:07.286905 PM

8/16/2022 11:26:33.472424 PM

8/15/2022 11:17:14.263464 PM

8/12/2022 11:50:07.724526 PM

8/11/2022 11:47:15.508658 PM

8/10/2022 11:50:15.372413 PM

8/10/2022 1:09:52.835009 AM

8/9/2022 12:24:36.999384 AM

8/6/2022 12:22:48.737356 AM

8/5/2022 12:21:50.502211 AM

8/4/2022 12:18:59.592631 AM

8/3/2022 12:14:46.761802 AM

8/2/2022 12:16:51.087926 AM

Additionally - is there a way to capture running average to identify when we see increase in average? i.e. with time the feed arrival starts to get delayed but due to ongoing nature unless we analyze data it is difficult to capture that.

Just to add trying to built SLI / SLO in case of an ETL process where feed comes, gets loaded and the SLIs here can be taken as data availability time for users based on region. (accuracy of data would be taken up later).

Thanks in advance for your help.

CodePudding user response：

If you are not worried about the fractional seconds then you can convert the values to a DATE and find the difference from the nearest midnight (as a NUMBER) and then average those to get the average time.

SELECT NUMTODSINTERVAL(
         AVG(
           CASE
           WHEN EXTRACT(HOUR FROM value) < 12
           THEN CAST(value AS DATE) - TRUNC(value)
           ELSE CAST(value AS DATE) - TRUNC(value) - 1
           END
         ),
         'DAY'
       ) AS avg_delivery_time
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS 
  SELECT TO_TIMESTAMP('8/31/2022 12:02:09.434916 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/30/2022 12:51:01.950574 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/26/2022 11:55:16.899643 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/26/2022 12:32:16.250415 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/25/2022 12:11:48.869983 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/23/2022 11:40:21.081020 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/23/2022 12:16:59.638991 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/19/2022 11:19:15.580283 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/18/2022 11:23:03.077817 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/17/2022 11:19:07.286905 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/16/2022 11:26:33.472424 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/15/2022 11:17:14.263464 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/12/2022 11:50:07.724526 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/11/2022 11:47:15.508658 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/10/2022 11:50:15.372413 PM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/10/2022 1:09:52.835009 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/9/2022 12:24:36.999384 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/6/2022 12:22:48.737356 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/5/2022 12:21:50.502211 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/4/2022 12:18:59.592631 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/3/2022 12:14:46.761802 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL UNION ALL
  SELECT TO_TIMESTAMP('8/2/2022 12:16:51.087926 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') FROM DUAL;

Outputs:

AVG_DELIVERY_TIME

000000000 00:02:22.772727273

AVG_DELIVERY_TIME
000000000 00:02:22.772727273

(2 minutes 22 seconds past midnight)

fiddle

CodePudding user response：

Following your attempt with taking out time, converting it to minutes (seconds in this answer) and calculating avg values (both - total avg of all rows and running avg of previous rows with current row). There is a backwards conversions of avg seconds back to time values.
With your sample data :

VALUE
31-AUG-22 00.02.09.434916000
30-AUG-22 00.51.01.950574000
26-AUG-22 23.55.16.899643000
26-AUG-22 00.32.16.250415000
25-AUG-22 00.11.48.869983000
23-AUG-22 23.40.21.081020000
23-AUG-22 00.16.59.638991000
19-AUG-22 23.19.15.580283000
18-AUG-22 23.23.03.077817000
17-AUG-22 23.19.07.286905000
16-AUG-22 23.26.33.472424000
15-AUG-22 23.17.14.263464000
12-AUG-22 23.50.07.724526000
11-AUG-22 23.47.15.508658000
10-AUG-22 23.50.15.372413000
10-AUG-22 01.09.52.835009000
09-AUG-22 00.24.36.999384000
06-AUG-22 00.22.48.737356000
05-AUG-22 00.21.50.502211000
04-AUG-22 00.18.59.592631000
03-AUG-22 00.14.46.761802000
02-AUG-22 00.16.51.087926000

Firstly, times are formated like hh24 (instead of AM/PM) and converted to seconds to do the AVG with later.

WITH
    tbl_2 AS 
        (
            Select 
                To_Date(To_Char(VALUE, 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') "DATE_TIME",
                      (To_Number(To_Char(To_Date(To_Char(VALUE, 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss'), 'hh24')) * 3600)    
                      (To_Number(To_Char(To_Date(To_Char(VALUE, 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss'), 'mi')) * 60)   
                       To_Number(To_Char(To_Date(To_Char(VALUE, 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss'), 'ss')) "SECONDS"
            From tbl
        )

This is used as the source data for the main SQL

SELECT
    DATE_TIME "DATE_TIME",
    JUST_TIME "JUST_TIME",
    SECONDS "SECONDS",
    RUNNING_AVG_SECONDS "RUNNING_AVG_SECONDS",
    
    To_Char(Trunc(RUNNING_AVG_SECONDS / 3600), 'FM9900') || ':' ||
    To_Char(Trunc(Mod(RUNNING_AVG_SECONDS, 3600) / 60), 'FM00') || ':' ||
    To_Char(Mod(RUNNING_AVG_SECONDS, 60), 'FM00') "RUNNING_AVG_TIME", 

    TOTAL_AVG_SECONDS "TOTAL_AVG_SECONDS",
    To_Char(Trunc(TOTAL_AVG_SECONDS / 3600), 'FM9900') || ':' ||
    To_Char(Trunc(Mod(TOTAL_AVG_SECONDS, 3600) / 60), 'FM00') || ':' ||
    To_Char(Mod(TOTAL_AVG_SECONDS, 60), 'FM00') "TOTAL_AVG_TIME"
FROM
    (
        SELECT 
            To_Char(DATE_TIME, 'mm/dd/yyyy hh24:mi:ss') "DATE_TIME",
            To_Char(DATE_TIME, 'hh24:mi:ss') "JUST_TIME",
            SECONDS "SECONDS", 
            Round(AVG(SECONDS) OVER(PARTITION BY 1 ORDER BY DATE_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 6) "RUNNING_AVG_SECONDS",
            Round(AVG(SECONDS) OVER(), 6) "TOTAL_AVG_SECONDS"
        FROM 
            tbl_2 
        ORDER BY 
            DATE_TIME
    )

I selected some additional columns so you could control the results. Used AVG analytic function to get both TOTAL_AVG_SECONDS and RUNNING_AVG_SECONDS. Both of the columns are converted back to time (using accepted answer from Oracle Convert Seconds to Hours:Minutes:Seconds : thank's Mike). Here is the resulting dataset:

DATE_TIME	JUST_TIME	SECONDS	RUNNING_AVG_SECONDS	RUNNING_AVG_TIME	TOTAL_AVG_SECONDS	TOTAL_AVG_TIME
08/02/2022 00:16:51	00:16:51	1011	1011	00:16:51	39415.5	10:56:56
08/03/2022 00:14:46	00:14:46	886	948.5	00:15:49	39415.5	10:56:56
08/04/2022 00:18:59	00:18:59	1139	1012	00:16:52	39415.5	10:56:56
08/05/2022 00:21:50	00:21:50	1310	1086.5	00:18:07	39415.5	10:56:56
08/06/2022 00:22:48	00:22:48	1368	1142.8	00:19:03	39415.5	10:56:56
08/09/2022 00:24:36	00:24:36	1476	1198.33333	00:19:58	39415.5	10:56:56
08/10/2022 01:09:52	01:09:52	4192	1626	00:27:06	39415.5	10:56:56
08/10/2022 23:50:15	23:50:15	85815	12149.625	03:22:30	39415.5	10:56:56
08/11/2022 23:47:15	23:47:15	85635	20314.6667	05:38:35	39415.5	10:56:56
08/12/2022 23:50:07	23:50:07	85807	26863.9	07:27:44	39415.5	10:56:56
08/15/2022 23:17:14	23:17:14	83834	32043	08:54:03	39415.5	10:56:56
08/16/2022 23:26:33	23:26:33	84393	36405.5	10:06:46	39415.5	10:56:56
08/17/2022 23:19:07	23:19:07	83947	40062.5385	11:07:43	39415.5	10:56:56
08/18/2022 23:23:03	23:23:03	84183	43214	12:00:14	39415.5	10:56:56
08/19/2022 23:19:15	23:19:15	83955	45930.0667	12:45:30	39415.5	10:56:56
08/23/2022 00:16:59	00:16:59	1019	43123.125	11:58:43	39415.5	10:56:56
08/23/2022 23:40:21	23:40:21	85221	45599.4706	12:39:59	39415.5	10:56:56
08/25/2022 00:11:48	00:11:48	708	43105.5	11:58:26	39415.5	10:56:56
08/26/2022 00:32:16	00:32:16	1936	40938.6842	11:22:19	39415.5	10:56:56
08/26/2022 23:55:16	23:55:16	86116	43197.55	11:59:58	39415.5	10:56:56
08/30/2022 00:51:01	00:51:01	3061	41286.2857	11:28:06	39415.5	10:56:56
08/31/2022 00:02:09	00:02:09	129	39415.5	10:56:56	39415.5	10:56:56

Regards...