select query to get data which occurs five times or more within 24 hours

Question

I am sorry in advance, I tried researching it but can't seem to find something that fits.

Following i have found already, but can not use for my issue:

Filtering MySQL query result set to yield multiple occurences within a specific period of time

SQL Query To Obtain Value that Occurs more than once

I was not able to modify these statements to solve my issue.

I would like to select all occurances of a Data which occurs five times or more in a Table within 24 hours. The table contains data about the past years.

The Table is not sorted by Date (Timestamp dd.mm.yy hh:mm:ss,sssssssss)

This shows me the overall occurance >= 5, but not if this happened within 24 hours.

SELECT Column, COUNT(*) FROM Table
GROUP BY Column
HAVING COUNT(*) >= 5;

Any suggestions for search keywords are welcome.

Thank you in advanced. Best regard Denyo

EDIT: Thank you all for your help and im sorry for the bad asked question. Ill try to answer all your questions.

@mathguy The Database version is 12c.

It does have one column for "data" and another column for date (timestamp). I would like to find occurrences of the same value in the "data" column within a <=24 hour window from 1st occurence of this value.

@Matthew McPeak Im still trying to understand your SQL Statement. Sry im totally new with SQL. This ist obvious by the way i asked my question :(.

@Aurelian My question is not well asked. So your answer couldn't fit, im sorry.

@all I feel bad about my bad asked question and wasting your time.

I hope you'll accept my apology.

Best regards Denyo

EDIT2: Im trying this now

begin
for i in (select Table.DATE(Timestamp), Table.IP, Table.Value from Table) 
loop
  dbms_output.put_line(i.Value); (instead of this output, i would like to add another loop and compare the Date(Timestamp) with i.DATE and count if matches
end loop;
end;

Answer 1

I would like to find occurrences of the same value in the "data" column within a <=24 hour window from 1st occurence of this value.

The first occurrence per value:

select value, min(timestmp) from mytable group by value;

Now we must check whether there are at least four more occurences of a value after its first occurrence:

select value, min(timestmp)
from mytable m
group by value
having
(
  select count(*)
  from mytable following24hours
  where following24hours.value = m.value
    and following24hours.timestmp > min(m.timestmp) 
    and following24hours.timestmp < min(m.timestmp) + interval '24' hour
) >= 4;

Answer 2

In Oracle 12, you can use the MATCH_RECOGNIZE clause for a very clean and efficient solution. (You can test it against the accepted answer, to verify if it is correct and to see which is more efficient on your actual data).

select value, first_timestmp
from   mytable
match_recognize(
  partition by value
  order by     timestmp
  measures     a.timestmp as first_timestmp
  one row per match
  pattern      ( a b{4,} )
  define       b as b.timestmp <= a.timestmp + interval '24' hour
)
;

Answer 3

You can use the following code:

SELECT DISTINCT Column, COUNT(*) 
FROM TABLE
WHERE date >= SYSDATE - 1
GROUP BY Column
HAVING COUNT(*) >= 5;   

Answer 4

You can use this expression to count up the occurrences for each value in the previous 24 hours:

 COUNT(*) OVER (PARTITION BY column
                ORDER BY date_column 
                RANGE NUMTODSINTERVAL(24,'HOUR') PRECEDING

Then, get the rows where that expression is at least 5.

I don't have your data, but here is an example that uses DBA_OBJECTS. This version will show any object updates where the object OWNER has updated 5 or more objects in the prior five-day period.

with five_day_count as 
( select o.owner, 
  last_ddl_time,
  count(*) over ( partition by owner 
                  order by last_ddl_time 
                  range numtodsinterval(5, 'DAY') PRECEDING ) cnt
  from dba_objects o)
SELECT owner, last_ddl_time, cnt 
FROM five_day_count
WHERE cnt >= 5

... and, just for fun, this query will give one row for each OWNER -- the five-day interval with the most updated objects for that user. A variant of this version may be more applicable to your need.

with five_day_count as 
( select o.owner, 
         last_ddl_time range_end,
         min(last_ddl_time) 
            over ( partition by owner 
                   order by last_ddl_time 
                   range numtodsinterval(5, 'DAY') PRECEDING ) range_start,
         count(*) 
            over ( partition by owner 
                   order by last_ddl_time range 
                   numtodsinterval(5, 'DAY') PRECEDING ) cnt
  from dba_objects o),
max_counts as 
( SELECT owner, 
         range_start, 
         range_end, 
         cnt, 
         row_number() 
             over ( partition by owner 
                    order by cnt desc, 
                             range_end desc) rn 
  FROM five_day_count
  WHERE cnt > = 5)
select owner, range_start, range_end, cnt
from max_counts
where rn = 1;