Calculate average of time difference between consecutive row-CodePudding

What would be the most efficient query to calculate the average of time difference between consecutive rows in a table? Note that the table has no primary key.

If the table looks like below:

|     tran_end_time     |
|-----------------------|
|2022-02-08 07:04:46.610|
|2022-02-08 07:09:47.403|
|2022-02-08 07:14:48.100|
|2022-02-08 07:20:03.973|

Then I need the answer to be:

avg('2022-02-08 07:20:03.973' - '2022-02-08 07:14:48.100',
    '2022-02-08 07:14:48.100' - '2022-02-08 07:09:47.403',
    '2022-02-08 07:09:47.403' - '2022-02-08 07:04:46.610')

CodePudding user response：

We can use DATEDIFF along with LAG:

WITH cte AS (
    SELECT tran_end_time,
           LAG(tran_end_time) OVER (ORDER BY tran_end_time) AS tran_end_time_lag
    FROM yourTable
)

SELECT AVG(DATEDIFF(minute, tran_end_time_lag, tran_end_time)) AS diff_avg
FROM cte
WHERE tran_end_time_lag IS NOT NULL;

Note that the WHERE clause in the final query above ensures that we do not include any diff involving the earliest record.