Skip to content
Advertisement

How do I count distinct events over sequential seconds?

I have a database with multiple columns, date, timestamp (HH:MM:SS), and phone numbers. I am trying to find a way to find unique calls in sequential seconds. For Example:

Ideally, I’d like to count the first 999-999-9999 as one call and 111-111-1111 as one call, and the second 999-999-9999 call as a call on the basis of the calls have the same timestamp or occurred in sequential seconds.

With an output similar to this with a count of 1 with the min(timestamp) of the non sequential call:

I’m not entirely sure where to start. I believe I’d have to create a subselect of the time based on the previous second. Not sure how to implement it…or if it would hand timestamps with the same second

I’m using MySql. Open to any help! Thank you in advance

Advertisement

Answer

In MySQL 8.0, this can be solved using window function lag(). You can recover the date/time of the previous call to the same number in a subquery, and then filter out records for which a previous call to the same number occured one second before:

Note: storing date and time in separated columns is not a good idea, since it complicates the logic when you need to actually compare date/times.

Demo on DB Fiddle:

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement