How to select most dense 1 min in Oracle

Question

I have table with time stamp column tmstmp, 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?

Have you checked this question already? http://stackoverflow.com/questions/14989927/sql-sliding-window-finding-max-value-over-interval I suppose it could be adapted for a 1-minute interval too. — p.marino, Jun 19 '16 at 16:42

Marmite Bomber · Accepted Answer · 2016-06-19T17:16:54.160

Analytic function with a logical window can provide this information directly:

select l.tmstmp,
count(*) over (order by tmstmp range between current row and interval '59.999999' second following) cnt
from log_table l
order by 1
;

TMSTMP                             CNT
--------------------------- ----------
01.01.16 00:00:00,000000000          4 
01.01.16 00:00:10,000000000          4 
01.01.16 00:00:15,000000000          3 
01.01.16 00:00:20,000000000          2 
01.01.16 00:01:00,000000000          3 
01.01.16 00:01:40,000000000          2 
01.01.16 00:01:50,000000000          1

Please adjust the interval length for your precision. It must be the highest possible value below 1 minute.

To get the maximal minute use the subquery (and don't forget you may receive more that one record - with the MAX count):

with tst as (
select l.tmstmp,
count(*) over (order by tmstmp range between current row and interval '59.999999' second following) cnt
from log_table l)
select * from tst where cnt = (select max(cnt) from tst);

TMSTMP                             CNT
--------------------------- ----------
01.01.16 00:00:00,000000000          4 
01.01.16 00:00:10,000000000          4

although @CarloCe offered interesting solution and fast, this solution runs literally 20 times faster on our prod exadata. — Boppity Bop, Jun 19 '16 at 18:10

Gordon Linoff · Answer 2 · 2016-06-19T19:35:19.070

One method uses a join and aggregation:

select t.*
from (select l.tmstmp, count(*)
      from log_table l join
           log_table l2
           on l2.tmstmp >= l.tmstmp and
              l2.tmstmp < l.tmstmp + interval '1' minute
      group by l.tmpstmp
      order by count(*) desc
     ) t
where rownum = 1;

Note: This assumes that tmstmp is unique on each row. If this is not true, then the subquery should be aggregating by some column that is unique.

EDIT:

For large data, there is a more efficient way that makes use of cumulative sums:

select tmstamp - interval 1 minute as starttm, tmstamp as endtm, cumulative
from (select tmstamp, sum(inc) over (order by tmstamp) as cumulative
      from (select tmstamp, 1 as inc from log_table union all
            select tmstamp + interval '1' day, -1 as inc from log_table
           ) t
      order by sum(inc) over (order by tmstamp) desc
     ) t
where rownum = 1;

thanks. very thorough answer.. doesn't work though.. you are missing ' quotes around interval param `+ interval '1'`.. fist sql goes for ever on 60k records.. I am looking into it atm.. the second returns 1 record only — Boppity Bop, Jun 19 '16 at 16:56

Carlo · Answer 3 · 2016-06-19T17:03:29.737

2

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

SELECT tmstmp, (
    SELECT COUNT(*) 
    FROM log_table t2 
    WHERE t2.tmstmp >= t.tmstmp AND t2.tmstmp < t.tmstmp + 1 / (24*60)
) AS events
FROM log_table t;

edited Jun 19 '16 at 17:03

answered Jun 19 '16 at 16:58

Carlo

1,539
1
11
25

its very difficult to test but it works. and it works fast! – Boppity Bop Jun 19 '16 at 17:07

How to select most dense 1 min in Oracle

3 Answers3