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

与数据库无关，有点像变种 of richardtallent's ，但没有限制。 (我在这里使用的是 SQL Server 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 gap 表达式降序，并选择最高的结果。
一些解释:

赞 richardtallent's answer ，您将表连接到自身上以查找“稍后”记录 - 这基本上将所有记录与任何后来的记录配对，这里配对 {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+空、2+4+3、3+4+空}。

WHERE但是，子句将这些过滤掉(只保留没有中间行的行)，因此只保留 {1+2+null, 2+3+null, 3+4+null}。哒哒!

正如 Dems 指出的那样，如果您可能在那里有两次相同的时间(“差距”为 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 而不是时间来使这更简单。