在SQLite中评估连续的行对

Question

我在SQLite表中有如下数据：

user_id     event_date
----------  ----------
1000001     2008-01-01
1000001     2008-03-13
1000001     2008-07-04
1000002     2007-01-06
1000002     2008-01-01
1000002     2009-06-01
1000002     2010-12-11

对于每个user_ids，我想选择成对的连续event_dates之间的最大时间间隔。例如，在此特定数据中，用户1000001有两个间隔：2008-01-01和2008-03-13之间为72天，以及2008-03-13和2008-07-04之间为113天，因此查询应输出113对于用户1000001。用户1000002具有三个间隙；对于用户1000001，具有三个间隙。最大的是558天。

这可能吗？我需要预先计算时间跨度并将其与数据一起存储，还是选择所有内容并进行后期处理？我希望能够仅使用所示数据直接在数据库中直接完成它。我是否期望SQL能够将这些数据视为列表来滥用SQL？
谢谢。

Answer 1

如果无法更改数据模型，则可以使用：

SELECT user_id, MAX(event_date - prior_event_date)
  FROM (SELECT t.user_id, t.event_date, MAX(prior.event_date) AS prior_event_date
          FROM your_table AS t
               JOIN your_table AS prior ON (t.user_id=prior.user_id
                                            AND prior.event_date < t.event_date)
         GROUP BY t.user_id, t.event_date) AS events
 GROUP BY user_id;

如果要包含0，请使用LEFT JOIN。