SQL Query to get difference between adjacent records

Question

I am having trouble with something I want to get from a database table.

The table looks like this

startTime  endTime type
1:00       1:02    A    
1:20       1:30    A
3:45       3:50    A
1:30       1:40    B
2:30       2:31    A
3:00       3:01    A
...

I want to get the average time gap (starttime of next A action minus starttime of this) on each type of action.

How am I suppose to do it?

Edit:

There is a rule. If the interval is bigger than 1 hour than it does not count towards the average. Therefor it is not equal to the whole time interval divided by the number of intervals. So it becomes, (just for A)

startTime  endTime type
1:00       1:02    A    
1:20       1:30    A
2:30       2:31    A
3:00       3:01    A
3:45       3:50    A

The calculation should be 1:20 - 1:00 = 20 min (take this record) 2:30 - 1:20 = 70 min (discard this record ) 3:00 - 2:30 = 30 min (take this) 3:45 - 3:00 = 45 min (take this)

The final result should be (20+30+45) / 3

*next A action* means *next in the time* or *next in the table*? — bluish, Mar 03 '11 at 08:46
there is an elegant solution to the similar/same problem in http://stackoverflow.com/questions/4312019/mysql-average-interval-between-records — Unreason, Mar 03 '11 at 09:25
Can you clarify the edit? Is it correct to assume that any interval bigger than 1 hour is removed from the sample set on which the average is calculated on or do you disregard the hour part of the larger intervals (or do something like that)? — Unreason, Mar 03 '11 at 09:37

Galz · Accepted Answer · 2011-03-05T21:41:56.007

I think there is no escaping a little reformatting of the data, and for that you can use a temp table.

Note: I created a table with integers instead of times as the source data to avoid all the time format calculations, but it's really the same.

The source data I created is:

CREATE TABLE `table` (
`start` INT(11) NOT NULL,
`end` INT(11) NOT NULL,
`type` VARCHAR(6));

INSERT INTO `table` VALUES
(1,3,'A'),
(5,7,'A'),
(6,10,'A'),
(2,6,'B'),
(3,4,'B'),
(5,11,'B'),
(12,13,'B');

Then the script you need to use to get your answer is:

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
id int(100) AUTO_INCREMENT,
start int(11) NOT NULL,
type VARCHAR(6),
PRIMARY KEY id (id));

INSERT INTO temp(start, type) 
SELECT start, type FROM table
ORDER BY type, start;

SELECT t1.type, AVG(t1.start - t2.start) AS avg_gap 
FROM temp t1
JOIN temp t2 ON t1.type = t2.type AND t1.id = (t2.id + 1)
WHERE t1.start - t2.start < 5
GROUP BY t1.type;

And the result is:

type   avg_gap
 A     2.5
 B     1.5

EDIT: According to your new rule in the edit: My rule is not to calculate gaps bigger than 5 (as you can see in the WHERE clause of the final query). Hence the last gap of type B was ignored.

score 0 · Answer 2 · edited May 23 '17 at 12:08

Ok, even though the problem is not the same as the one in MySQL: Average interval between records the other valid solutions from it do apply to you, for example:

SELECT type, AVG(minutes)
FROM
(SELECT type, EXTRACT(MINUTE FROM TIMEDIFF(t2.startTime, MAX(t1.startTime))) minutes
FROM table t1 JOIN
     table t2 ON t1.type = t2.type AND 
                 t1.startTime > SUBTIME(t2.startTime, '01:00:00') AND 
                 t1.startTime < t2.startTime
GROUP BY type, t2.startTime
) x
GROUP BY type

There are a few assumptions in the above query:

the startTime type is TIME
it is unique per type (no two events start at the same time)
it is actually excluding any interval exactly one hour long, too

SQL Query to get difference between adjacent records

2 Answers2

Linked