In the previous post I used MATCH_RECOGNIZE to convert a list of concrete event times to frequency-based ranges.
The same technique can be applied with different tables, so I wrapped the query in a Table SQL Macro.
The parameters are the table, the “type” column, the “time” column, and (optionally) the period used for filtering.
I assumed the frequencies are always in minutes. The macro can be further generalized to support different time resolutions.
Here is the code:
create or replace function freq_view
(
i_table in dbms_tf.table_t,
i_columns in dbms_tf.columns_t,
i_from_time in date default date '1970-01-01',
i_to_time in date default date '9999-12-31'
) return varchar2 sql_macro as
begin
return 'select event_type ' || i_columns(1) || ',
to_char(from_time,''yyyy-mm-dd'') "DATE",
to_char(from_time,''hh24:mi'') ||
nvl2(to_time,
'' - '' || to_char(to_time,''hh24:mi'')
|| '' every '' || minutes || '' minutes (''
|| number_of_events || '' events)'',
null) event_times
from (
select ' || i_columns(1) || ' as event_type, trunc(' || i_columns(2) || ',''mi'') as event_time
from i_table
where ' || i_columns(2) || ' between i_from_time and i_to_time
) match_recognize (
partition by event_type
order by event_time
measures X.event_time from_time,
last(Y.event_time) to_time,
round((first(Y.event_time)-X.event_time)*24*60) minutes,
count(*) number_of_events
pattern (X Y{2,} | X)
define
Y as event_time-prev(event_time) = first(Y.event_time)-X.event_time
)
order by event_type, from_time';
end freq_view;
/
Let’s use it for the same data set used in the original post:
SQL> break on event_type skip 1 dup
SQL> select * from freq_view(events,columns(event_type,event_time),timestamp'2022-07-29 08:00:00');
EVENT_ DATE EVENT_TIMES
------ -------------- ----------------------------------------------
A 2022-07-29 08:10 - 08:50 every 10 minutes (5 events)
A 2022-07-29 08:58 - 09:22 every 8 minutes (4 events)
A 2022-07-29 09:33
A 2022-07-29 09:42 - 10:52 every 10 minutes (8 events)
A 2022-07-29 10:59 - 11:11 every 4 minutes (4 events)
A 2022-07-29 11:21 - 12:01 every 10 minutes (5 events)
B 2022-07-29 08:15 - 09:55 every 20 minutes (6 events)
B 2022-07-29 10:10
B 2022-07-29 10:25 - 12:05 every 25 minutes (5 events)
C 2022-07-29 08:02 - 08:32 every 2 minutes (16 events)
C 2022-07-29 08:49 - 09:05 every 2 minutes (9 events)
C 2022-07-29 09:08 - 09:20 every 4 minutes (4 events)
C 2022-07-29 09:22 - 10:16 every 2 minutes (28 events)
C 2022-07-29 11:46 - 12:20 every 2 minutes (18 events)
14 rows selected.
And now let’s use it for something else – the DBMS_SCHEDULER executions from the last 3 hours:
SQL> select * from freq_view(user_scheduler_job_run_details,columns(job_name,log_date),sysdate-3/24);
JOB_NAME DATE EVENT_TIMES
------------ --------------- ------------------------------------------------
JOB_AAA 2022-07-30 08:45 - 10:45 every 60 minutes (3 events)
JOB_BBB 2022-07-30 08:10 - 10:10 every 60 minutes (3 events)
JOB_CCC 2022-07-30 08:07 - 11:02 every 5 minutes (36 events)
JOB_DDD 2022-07-30 10:00
JOB_EEE 2022-07-30 08:06 - 11:05 every 1 minutes (180 events)