SQL：找到最长的日期差距

我有一个包含2个字段的表：唯一ID,用户ID(外键)和日期时间.这是服务的访问日志.我在SQL Server工作,但我会欣赏不可知的答案.

我想使用SQL为某个用户查找最长间隙开始的ID.

例如,假设我的值如下(一个用户的简化)：

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(在那里得到间隙的长度也很好,然后,但更不重要).

在SQL中实现这一目标的最有效方法是什么？

注意：ID不一定是顺序的.

谢谢

解决方法

从这个设置开始：

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而不是时间来使这更简单.

（编辑：辽源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!