mysql查询记录相邻10条记录,mysql – 用于获取相邻记录之间差异的SQL Query

我遇到了一些我想从数据库表中获取的东西.

该表看起来像这样

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

...

我希望得到每种动作的平均时间间隔(下一个动作的开始时间减去此动作的开始时间).

我怎么想这样做？

编辑：

有规则.如果间隔大于1小时,则不计入平均值.因此,它不等于整个时间间隔除以间隔的数量.所以它变成,(仅适用于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

计算应该是

1:20 – 1:00 = 20分钟(记录此记录)

2:30 – 1:20 = 70分钟(丢弃此记录)

3：00-2：30 = 30分钟(拿这个)

3：45-3：00 = 45分钟(拿这个)

最终结果应为(20 30 45)/ 3

解决方法:

我认为没有转义数据的重新格式化,为此您可以使用临时表.

注意：我创建了一个以整数而不是时间作为源数据的表,以避免所有时间格式计算,但它实际上是相同的.

我创建的源数据是：

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');

然后,您需要用来获得答案的脚本是：

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;

结果是：

type avg_gap

A 2.5

B 1.5

编辑：根据编辑中的新规则：我的规则不是计算大于5的间隙(正如您在最终查询的WHERE子句中看到的那样).因此忽略了B类的最后一个差距.

标签：mysql,sql

来源： https://codeday.me/bug/20190630/1339946.html