MySQL group by with average time distance to previous record-CodePudding

I would like to get the average distance (in seconds for example) between two distinct rows using an aggregate function with group by.

Lets say I've a table like this

id   | ts                  | data
1123 | 2022-01-19 07:15:23 | ...
1124 | 2022-01-19 07:17:28 | ...
1125 | 2022-01-19 07:21:19 | ...
1126 | 2022-01-19 07:21:23 | ...
....
1227 | 2022-01-19 09:01:04 | ...

Now. At first I want to group this and want to have a count how many rows we have per day. This is easy:

SELECT DATE(ts), COUNT(data) FROM table GROUP BY DATE(ts)

and the result looks like this:

DATE(ts)   | COUNT(data)
2022-01-18 | 519
2022-01-19 | 104

But additional to this I would like to have the average distance (in seconds) between the previous row so that I get an idea of how frequently new data is inserted. So the result should look like this:

DATE(ts)   | Average distance | COUNT(data)
2022-01-18 | 12               | 519
2022-01-19 | 14               | 104

Is there a way to get such an information using SQL?

CodePudding user response：

Assuming you're using MySQL 8 or later you can use lag function to find the previous datetime in order to calculate the difference. Rest is straight forward:

with cte as (
    select cast(ts as date) as dt
         , ts
         , lag(ts) over(partition by cast(ts as date) order by ts) as prev_ts
    from t
)
select dt
     , count(*) as count
     , avg(timestampdiff(second, prev_ts, ts)) as avg_diff
from cte
group by dt

CodePudding user response：

You may approximate the average distance in minutes between all records on a given date by just taking the difference between the latest and earliest timestamp, divided by the count:

SELECT DATE(ts),
       TIMESTAMPDIFF(MINUTE, MIN(ts), MAX(ts)) / (COUNT(data) - 1) AS avg_distance,
       COUNT(data)
FROM yourTable
GROUP BY DATE(ts);