mysql 获取前后相邻_SQL查询以获取相邻记录之间的差异

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

解决方案

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.