MySQL - Computing average time difference between 2 closest records-CodePudding

I have a table access that stores the time when an employee accessed the system and also when he logged out.

access_id int(11) -- PK, auto-generated
employee_id (11)
in_time bigint(20) 
out_time bigint(20)

What I need is the average time when the employee was out (in_time - out_time of the previous access of the employee) - for all employees.

What I managed so far:

I could manage to calculate the average for a single employee using this (rather complex) query

SELECT AVG (b.in_time - a.out_time) / 60000 AS avginminutes
FROM access a CROSS JOIN access b
WHERE 
    b.access_id = 
    (SELECT MIN(c.access_id)
    FROM access c
    WHERE c.access_id > a.access_id
    and c.employee_id = 1765708 )
AND a.employee_id = 1765708
AND  b.in_time - a.out_time != 0
ORDER BY a.access_id ASC;

My main question is, how can I modify this query such that it could calculate the average for all employees? I did not get anywhere on this, after spending much time.

Secondly (but not important) , is there a way the query can be simplified?

Sample data:

access_id|employee_id|in_time      |out_time
|1       |1765708    |1643720400000|1643727600000
|2       |1765708    |1643728200000|1643734800000
|3       |1765708    |1643735100000|1643738400000
|4       |4344524    |1646125200000|1646128800000
|5       |4344524    |1646129100000|1646134200000
|6       |4344524    |1646134800000|1646142000000
|7       |4344524    |1646149200000|1646156400000

MySQL version: 5.5

CodePudding user response：

(The average time when the employee was out) 
 = 
(MAX(out_time) - MIN(in_time) - SUM(out_time - in_time)) / (COUNT(*) - 1)

I.e.

SELECT employee_id,
       (MAX(out_time) - MIN(in_time) - SUM(out_time - in_time)) / (COUNT(*) - 1) / 60000 avginminutes
FROM access 
GROUP BY 1;

Of course will fail or will produce NULL (depends on SQL mode) if there is only one row for some employee. In strict mode - adjust with according CASE.

CodePudding user response：

If you're using MySQL 8 you can use LAG function to find previous out time for each row:

WITH cte AS (
    SELECT
        employee_id,
        LAG(out_time) OVER (PARTITION BY employee_id ORDER BY in_time) AS prev_out,
        in_time
    FROM t
)
SELECT employee_id, AVG(in_time - prev_out)
FROM cte
GROUP BY employee_id