This is a generic solution to group rows. Set duration_in_minutes to the size of the interval; this is a bind variable in PL/SQL.
Group rows in N-minute intervals
declare
duration_in_minutes integer := 5;
begin
for rws in (
with rws as (
select trunc ( datetime ) dy,
trunc ( datetime, 'mi' ) mins,
duration_in_minutes / 1440 time_interval
from time_data
), intervals as (
select dy + (
floor ( ( mins - dy ) / time_interval ) * time_interval
) start_datetime
from rws
select start_datetime, count(*) row#
from intervals
group by start_datetime
order by start_datetime
fetch first 5 rows only
) loop
dbms_output.put_line ( rws.start_datetime || ' - ' || rws.row# );
end loop;
Statement processed.
01-AUG-2022 00:00 - 3
01-AUG-2022 00:05 - 1
01-AUG-2022 00:10 - 5
01-AUG-2022 00:20 - 3
01-AUG-2022 00:25 - 5
Statement
If you want to include missing intervals in the output, first generate a list of all the time buckets you want. Then outer join the table to this.
Group rows in 5-minute intervals including missing times
declare
duration_in_minutes integer := 5;
start_time varchar2(20) := '01-AUG-2022 00:00';
number_of_intervals integer := 12;
begin
for rws in (
with times as (
select to_date ( start_time, 'DD-MON-YYYY HH24:MI' )
+ ( ( level - 1 ) * duration_in_minutes / 1440 ) dt
from dual
connect by level <= number_of_intervals
), intervals as (
select dt start_date,
dt + ( duration_in_minutes / 1440 ) end_date
from times
select start_date, end_date, count ( datetime ) row#
from intervals i
left join time_data t
on start_date <= datetime
and datetime < end_date
group by start_date, end_date
order by start_date
fetch first 5 rows only
) loop
dbms_output.put_line ( rws.start_date || ' - ' || rws.row# );
end loop;
Statement processed.
01-AUG-2022 00:00 - 3
01-AUG-2022 00:05 - 1
01-AUG-2022 00:10 - 5
01-AUG-2022 00:15 - 0
01-AUG-2022 00:20 - 3
Statement
Instead of you supplying a start date, this finds the starting time from the table itself
Group rows in N-minute intervals starting from source data
declare
duration_in_minutes integer := 5;
number_of_intervals integer := 12;
begin
for rws in (
with start_date as (
select min ( datetime ) start_date,
duration_in_minutes / 1440 time_interval
from time_data
), intervals as (
select start_date + ( ( level - 1 ) * time_interval ) start_date,
start_date + ( ( level ) * time_interval ) end_date
from start_date
connect by level <= number_of_intervals
select start_date, count ( datetime ) row#
from intervals
left join time_data
on start_date <= datetime
and datetime < end_date
group by start_date
order by start_date
) loop
dbms_output.put_line ( rws.start_date || ' - ' || rws.row# );
end loop;
Statement processed.
01-AUG-2022 00:01 - 3
01-AUG-2022 00:06 - 2
01-AUG-2022 00:11 - 4
01-AUG-2022 00:16 - 2
01-AUG-2022 00:21 - 2
01-AUG-2022 00:26 - 4
01-AUG-2022 00:31 - 3
01-AUG-2022 00:36 - 3
01-AUG-2022 00:41 - 2
01-AUG-2022 00:46 - 3
01-AUG-2022 00:51 - 7
01-AUG-2022 00:56 - 4
Statement
After each 5-minute bucket, this starts the next interval at the time of the next row instead of a multiple of 5 minutes from the initial timestamp.
Group rows in N-minute intervals with variable gaps
declare
duration_in_minutes integer := 5;
begin
for rws in (
select *
from time_data
match_recognize (
order by datetime
measures
init.datetime as interval_start,
init.datetime
+ numtodsinterval ( duration_in_minutes, 'minute' ) as interval_end,
count(*) as row#
pattern ( init time_interval* )
define
time_interval as datetime <
init.datetime + numtodsinterval ( duration_in_minutes, 'minute' )
fetch first 5 rows only
) loop
dbms_output.put_line ( rws.interval_start || ' - ' || rws.row# );
end loop;
Statement processed.
01-AUG-2022 00:01 - 3
01-AUG-2022 00:06 - 2
01-AUG-2022 00:13 - 4
01-AUG-2022 00:20 - 4
01-AUG-2022 00:26 - 4
Statement
This groups together all rows that are within 5 minutes of the previous row; this could lead to groups of any size
Combine all rows within N-minutes of the previous row
declare
duration_in_minutes integer := 5;
begin
for rws in (
select *
from time_data
match_recognize (
order by datetime
measures
init.datetime as interval_start,
init.datetime
+ numtodsinterval ( duration_in_minutes, 'minute' ) as interval_end,
count(*) as row#
pattern ( init time_interval* )
define
time_interval as datetime <
prev ( datetime ) + numtodsinterval ( duration_in_minutes, 'minute' )
fetch first 5 rows only
) loop
dbms_output.put_line ( rws.interval_start || ' - ' || rws.row# );
end loop;
Privacy |
Terms of Use
