我有一个包含2个字段的表：唯一ID,用户ID(外键)和日期时间.这是服务的访问日志.我在sql Server工作,但我会欣赏不可知的答案.
例如,假设我的值如下(一个用户的简化)：
ID | User-ID | Time ---------------------------------- 1 | 1 | 11-MAR-09,8:00am 2 | 1 | 11-MAR-09,6:00pm 3 | 1 | 13-MAR-09,7:00pm 4 | 1 | 14-MAR-09,6:00pm
如果我为用户1搜索最长的间隙,我将得到ID 2(在那里得到间隙的长度也很好,然后,但更不重要).
注意：ID不一定是顺序的.
谢谢
解决方法
与数据库无关,属于richardtallent的变体,但没有限制.
从这个设置开始：
create table test(id int,userid int,time datetime) insert into test values (1,1,'2009-03-11 08:00') insert into test values (2,'2009-03-11 18:00') insert into test values (3,'2009-03-13 19:00') insert into test values (4,'2009-03-14 18:00')
(我这是sql Server 2008,但不应该)
运行此查询：
select starttime.id as gapid,starttime.time as starttime,endtime.time as endtime,/* Replace next line with your DB's way of calculating the gap */ DATEDIFF(second,starttime.time,endtime.time) as gap from test as starttime inner join test as endtime on (starttime.userid = endtime.userid) and (starttime.time < endtime.time) left join test as intermediatetime on (starttime.userid = intermediatetime.userid) and (starttime.time < intermediatetime.time) and (intermediatetime.time < endtime.time) where (intermediatetime.id is null)
给出以下内容：
gapid starttime endtime gap 1 2009-03-11 08:00:00.000 2009-03-11 18:00:00.000 36000 2 2009-03-11 18:00:00.000 2009-03-13 19:00:00.000 176400 3 2009-03-13 19:00:00.000 2009-03-14 18:00:00.000 82800
然后,您可以按顺序删除间隙表达式,然后选择最佳结果.
一些解释：就像richardtallent的答案一样,你加入表格以找到一个’后来’的记录 – 这基本上将所有记录与他们后来的记录中的任何一个配对(所以对1,2 1,1 3,1 4,2 3,2 4,3 4).然后是另一个自连接,这次是左连接,以找到之前选择的两个之间的行(1 2 null,1 3 2,1 4 2,1 4 3,2 3 null,2 4 3,3 4空值).但是,WHERE子句将这些过滤掉(仅保留没有中间行的行),因此只保留1 2 null,2 3 null和3 4 null. TAA-DAA！
如果可能的话,可能会在那里有两次相同的时间(‘间隙’为0)那么你需要一种方法来打破关系,正如Dems指出的那样.如果您可以使用ID作为决胜局,那么请更改,例如
and (starttime.time < intermediatetime.time)
至
and ((starttime.time < intermediatetime.time) or ((starttime.time = intermediatetime.time) and (starttime.id < intermediatetime.id)))
假设’id’是打破关系的有效方式.
事实上,如果你知道ID会单调增加(我知道你说’不顺序’ – 不清楚这是否意味着它们不会随着每一行增加,或者只是两个相关条目的ID可能不是顺序因为例如另一个用户之间有条目),你可以在所有比较中使用ID而不是时间来使这更简单.
