评估 SQLite 中的连续行对 [英] Evaluate sequential pairs of rows in SQLite

问题描述

我在 SQLite 表中有数据，如下所示:

I have data in an SQLite table that looks like this:

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 有三个缺口；最大的是 558 天.

For each of the user_ids I would like to select the largest time gap between pairs of consecutive event_dates. For example in this particular data, user 1000001 has two gaps: 72 days between 2008-01-01 and 2008-03-13, and 113 days between 2008-03-13 and 2008-07-04, so the query should output 113 for user 1000001. User 1000002 has three gaps; largest is 558 days.

这可能吗?我是否必须预先计算时间跨度并将它们与数据一起存储，或者选择所有内容并进行后期处理?我希望能够仅使用显示的数据并直接在数据库中直接完成它.我是否期望 SQL 能够将此数据视为列表来滥用 SQL?
谢谢.

Is this possible? Would I have to pre-calculate the time spans and store them with the data, or select everything and post process? I'd like to be able to get it done directly with only the data as shown and directly in the database. Am I abusing SQL by expecting it to be able to treat this data as a list?
Thanks.

推荐答案

如果你不能改变数据模型，你可以使用:

If you can not change the data-model, you could use:

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.

If you want 0s to be included use a LEFT JOIN.

这篇关于评估 SQLite 中的连续行对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！