Find avg, min, and max of grouped by rows

user_2924127 0
Viewed 124

I have created the following schema:

CREATE TABLE test (
  id INT,
  stat_id INT,
  time DATETIME
);

INSERT INTO test (id, stat_id, time) VALUES (1, 1, '2020-09-21 00:02:31');
INSERT INTO test (id, stat_id, time) VALUES (5, 1, '2020-09-21 00:06:31');
INSERT INTO test (id, stat_id, time) VALUES (2, 2, '2020-09-19 00:08:31');
INSERT INTO test (id, stat_id, time) VALUES (3, 2, '2020-09-21 00:03:31');
INSERT INTO test (id, stat_id, time) VALUES (6, 2, '2020-09-23 00:02:31');
INSERT INTO test (id, stat_id, time) VALUES (4, 2, '2020-09-27 00:04:31');
INSERT INTO test (id, stat_id, time) VALUES (7, 3, '2020-09-20 00:04:31');
INSERT INTO test (id, stat_id, time) VALUES (8, 3, '2020-09-23 00:05:31');

https://www.db-fiddle.com/f/6CRv6XqYMAfkBHEBhz1zGe/1

I have 3 different stat_id groups.

They are ordered by the id (smallest to largest).

I need to find the avg duration between one event in each group to the the next.

For example, for site_id = 2, I need to get the difference between the 2020-09-21 and 2020-09-19, then 2020-09-23 and 2020-09-21, and then 2020-09-27 and 2020-09-23.

And then I need to get the avg duration between each of those rows, the maximum time (which be the time between the 2020-09-27 and 2020-09-23) and the minimum time.

I need to do this for all 3 stat_id groups.

I'm essentially looking, on average, how long it took for each each stat_id group to create a new row.

I tried something like:

select 
    stat_id,
    AVG(time) as avg,
    timestampdiff(hour, min(time), max(time))   as diff_in_hours,
from test
group by stat_id;

but obviously this is wrong. It gives the wrong average and just gives the difference between the biggest and the smallest in each group, which is not exactly what I am looking for. I am not sure how to do the difference between one row and its previous row?

window-functions datetime mysql average sql
1 Answers

One option uses lag():

select stat_id, avg(diff) avg_diff
from (
    select t.*, 
        timestampdiff(hour, lag(time) over(partition by stat_id order by id), time) diff
    from test t
) t
group by stat_id
user_106767160
Related