ORACLE SQL: Select records with time difference less than a minute

Question

I have a table in which I have same the records but with different timestamps. E.g. - 1

2015-03-03 13:41:11.0  [createService]: [accountNumber = 123456]
2015-03-02 01:19:11.0  [createService]: [accountNumber = 123456]
2015-03-02 01:19:06.0  [createService]: [accountNumber = 123456]

E.g. - 2

2015-03-07 01:21:11.0  [createService]: [accountNumber = 567890]
2015-03-04 01:17:11.0  [createService]: [accountNumber = 567890]

I need to pull only the records that have more than 2 rows with a time difference of less than a minute on any single day. In the examples above i should only pull account 123456 since it has 2 records on the same day with a time difference of less than 60 seconds.

This is the query ive used to pull all records that have the createservice row more than once but need to filter the list furhter with the condition above -

SELECT acct_number
FROM CUSTOMER_LOG
WHERE LOG_EVENT LIKE '%createService%serviceName%'
AND LOG_EVENT_TMST > to_date('03/01/2015 00:00:00','MM/DD/YYYY HH24:MI:SS')
GROUP BY ACCT_NUMBER
having count(ACCT_NUMBER) > 1;

Could you please help me with a query that can accomplish this in the fastest time? Thank you in advance!

Answer 1

Get the previous timestamp using lag(). The rest is just basic querying:

select acct_number, trunc(LOG_EVENT_TMST)
from (select cl.*, lag(log_event_tmst) over (partition by acct_number order by log_event_tmst) as prev_let
      from customer_log cl
      where LOG_EVENT_TMST > to_date('03/01/2015 00:00:00','MM/DD/YYYY HH24:MI:SS')
     ) cl
where (log_event_tmst - prevlet) < 1.0 / (60 * 24)
group by acct_number, trunc(LOG_EVENT_TMST);

Answer 2

SELECT *
FROM   CUSTOMER_LOG c
WHERE  LOG_EVENT LIKE '%createService%serviceName%'
AND    LOG_EVENT_TMST > DATE '2015-01-03'
AND    EXISTS ( SELECT 'X'
                FROM   CUSTOMER_LOG x
                WHERE  c.LOG_EVENT   = x.LOG_EVENT
                AND    c.ACCT_NUMBER = x.ACCT_NUMBER
                AND    c.LOG_EVENT_TMST
                         BETWEEN x.LOG_EVENT_TMST - INTERVAL '1' MINUTE
                         AND     x.LOG_EVENT_TMST + INTERVAL '1' MINUTE
              );

or

SELECT *
FROM   CUSTOMER_LOG
WHERE  LOG_EVENT LIKE '%createService%serviceName%'
AND    LOG_EVENT_TMST > DATE '2015-01-03'
AND    (  LAG(  LOG_EVENT_TMST ) OVER ( PARTITION BY LOG_EVENT, ACCT_NUMBER ORDER BY LOG_EVENT_TMST )
             > LOG_EVENT_TMST - INTERVAL '1' MINUTE
       OR LEAD( LOG_EVENT_TMST ) OVER ( PARTITION BY LOG_EVENT, ACCT_NUMBER ORDER BY LOG_EVENT_TMST )
             < LOG_EVENT_TMST + INTERVAL '1' MINUTE
       );