MySQL - 查找连续日期时间之间的最大时间差

Question

我有一个包含日期时间列表的表格。

我想找到连续日期时间之间的最长时间量，即找到时间轴上彼此相邻的任意两个日期时间条目之间的最大距离。将其视为“最长的连胜” - 按时间顺序从一次重置到下一次重置的最长时间。

例如:

mysql> select * from resets order by datetime asc;
+----+---------------------+-------------+---------------------+---------------------+
| id | datetime            | activity_id | created_at          | updated_at          |
+----+---------------------+-------------+---------------------+---------------------+
|  7 | 2014-12-30 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  3 | 2014-12-31 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  5 | 2015-01-01 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  4 | 2015-01-02 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  6 | 2015-01-03 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  1 | 2015-01-04 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |
|  2 | 2015-01-05 20:38:22 |           1 | 2015-01-06 20:38:22 | 2015-01-06 20:38:22 |

 etc...

从上面的示例数据集中，我想知道以下哪个时间差更大:

• 第 7 行和第 3 行之间
• 第 3 行和第 5 行之间
• 第 5 行和第 4 行之间
• 等...

(显然，它们都恰好相隔 24 小时。我正在寻找一个通用的解决方案。)

通过遍历有序数组、存储连续值之间的差异并选择最大的一个，使用普通编程语言可以轻松完成此操作。

是否有一种巧妙的方法可以只使用 SQL 来做到这一点？

更新:

对我有用的查询是

SELECT MAX(DATEDIFF(r.next_datetime, r.datetime))
    FROM (

    # finds each datetime and the next consecutive one in the table
    SELECT r1.datetime as datetime, (

        # finds the next consecutive datetime
        SELECT datetime
        FROM resets r2
        WHERE r2.datetime > r1.datetime
        ORDER BY datetime ASC
        LIMIT 1
    ) as next_datetime

    FROM resets as r1
    ORDER BY r1.datetime

) as r;

最里面的查询 SELECT datetime FROM resets r2... 负责在列表中查找比当前日期时间大的下一个日期时间。请注意，此查询是有序的并且限制为 1。这对我来说是最困难的部分。

剩下的就很简单了。对于表中的每一行，我们选择日期时间值和表中的下一个连续日期时间。最外层的查询找到我们刚刚创建的任何日期时间对之间的最大差异 - “连胜”。

我选择了@OllieJones 给出的答案，因为它是最简洁和最好的解释，即使我更喜欢“纯 SQL”解决方案。

Answer 1

此查询将计算连续行之间的时间差并显示最大的一个——连胜的长度。如果您需要整行，您将需要 Gordon 的查询。

SELECT MAX(diff)
  FROM (
          SELECT TIMEDIFF(datetime,@prev) AS diff,
                 (@prev:=datetime) AS datetime  
            FROM resets, 
                 (SELECT @prev:=(SELECT MIN(datetime) FROM resets)) AS init
           ORDER BY datetime
       ) AS diffs

这是如何工作的？

首先，它是单行查询和您的表之间的交叉连接。单行查询是这样的:

       (SELECT @prev:=(SELECT MIN(datetime) FROM resets)) 

它将用户定义的值 @prev 设置为表中最低/最早的 datetime。这是在查询开始时初始化用户定义变量的 MySQL 技巧。

然后，SELECT 子句中有两列:

          SELECT TIMEDIFF(datetime,@prev) AS diff,
                 (@prev:=datetime) AS datetime  

第一个获取当前行的datetime 和@prev 的值之间的时间差。第二个将 @prev 的值更新为当前行的 datetime。

所以内部查询吐出一个时间戳列表以及与 ORDER BY datetime 中的前一个时间戳的差异。

外部查询 SELECT MAX(diff) 从内部查询中获取 diff 的最大值——最长的连胜。

让我们明确一点:这是 MySQL 特有的猴子生意。纯 SQL 应该是声明性的，而不是过程性的。但是这个使用用户定义的 @prev 变量的技巧让我们能够以一种有用的方式混合声明性和过程性代码，即使它有些晦涩。