SQL：找到最长的日期间隔

Question

我有一个包含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不一定是顺序的。

谢谢

Answer 1

与数据库无关，是richardtallent的一种变体，但没有限制。（我在这里使用SQLServer 2008，但这没关系。）

从此设置开始：

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, 1, '2009-03-11 18:00')
insert into test values (3, 1, '2009-03-13 19:00')
insert into test values (4, 1, '2009-03-14 18:00')

运行此查询：

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

然后，您可以ORDER BY使间隙表达式下降，然后选择最高的结果。

一些解释：

• 就像richardtallent的答案一样，您将表格连接到其自身上以查找“较晚的”记录。这基本上是将所有记录与其以后的任何记录配对，这里将{1 + 2，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 + null}。
• WHERE但是，该子句将它们过滤掉（仅保留没有中间行的行），因此仅保留{1 + 2 + null，2 + 3 + null，3 + 4 + null}。a！

正如Dems所指出的那样，如果您可能两次在其中有相同的时间（“ gap”为0），那么您将需要一种打破平局的方法。如果您可以将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而不是时间来进行 所有 比较，从而使操作更加简单。