SQL查询以获取相邻记录之间的差异

一尘不染

SQL查询以获取相邻记录之间的差异

sql

我想从数据库表中获取某些东西时遇到麻烦。

桌子看起来像这样

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
...

我想获得每种动作类型的平均时间间隔（下一个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

阅读 117

2021-05-30

共1个答案

一尘不染

我认为没有必要对数据进行一些重新格式化，为此，您可以使用临时表。

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

我创建的源数据是：

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的最后一个间隙被忽略了。

2021-05-30