Boppity Bop Boppity Bop - 1 year ago 106
SQL Question

How to select most dense 1 min in Oracle

I have table with time stamp column

, this table contains log of certain events. I need to find out the max number events which occurred within 1 min interval.

Please read carefully! I do NOT want to extract the time stamps minute fraction and sum like this:

select count(*), TO_CHAR(tmstmp,'MI')
from log_table
group by TO_CHAR(tmstmp,'MI')
order by TO_CHAR(tmstmp,'MI');

It needs to take 1st record and then look ahead until it selects all records within 1 min from the 1st and sum number of records, then take 2nd and do the same etc..

And as the result there must be a recordset of (sum, starting timestamp).

Anyone has a snippet of code somewhere and care to share please?

Answer Source

I think you can achieve your goal using a subquery in SELECT statement, as follow:

SELECT tmstmp, (
    FROM log_table t2 
    WHERE t2.tmstmp >= t.tmstmp AND t2.tmstmp < t.tmstmp + 1 / (24*60)
) AS events
FROM log_table t;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download