我认为没有必要对数据进行一些重新格式化,为此,您可以使用临时表。
注意 :我创建了一个以整数而不是时间为源数据的表,以避免所有时间格式的计算,但实际上是相同的。
我创建的源数据是:
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的最后一个间隙被忽略了。