SQL查询以获取相邻记录之间的差异 [英] SQL Query to get difference between adjacent records

问题描述

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

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

桌子看起来像这样

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动作的开始时间减去此动作的开始时间).

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

我应该怎么做?

有一条规则.如果间隔大于1小时，则不计入平均值.因此，它不等于整个时间间隔除以间隔数.这样就变成了(仅适用于A)

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

计算应为 1:20-1:00 = 20分钟(获取此记录) 2:30-1:20 = 70分钟(舍弃此记录) 3:00-2:30 = 30分钟(参加此活动) 3:45-3:00 = 45分钟(参加)

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)

最终结果应该是(20 + 30 + 45)/3

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.

我创建的源数据是:

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;

结果是:

type   avg_gap
 A     2.5
 B     1.5

根据您在编辑中的新规则:我的规则是不计算大于5的缺口(如您在最终查询的WHERE子句中所见).因此，B型的最后一个间隙被忽略了.

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.

这篇关于SQL查询以获取相邻记录之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！