MySQL - 查找不同ID行之间的时间差

Question

我有一张表格，其中包含以下数据：

ID   status-before             status-current         DATE-timestamp         
5    Un-Assigned                Assigned              2013-12-17 14:14:04
5    Assigned                   Completed             2013-12-21 14:14:04
7    Un-Assigned                Assigned              2014-01-02 14:14:04 
7    Assigned                   Completed             2014-01-21 14:14:04
7    Completed                  Locked                2014-01-21 14:14:04
8    Un-Assigned                Assigned              2014-12-21 14:14:04
8    Assigned                   Completed             2014-12-27 14:14:04

我已经使用了group by来分组id并按时间戳字段排序。我不知道如何使用rownum（或任何其他方法）来计算仅具有相同id的行之间的时间差。

我希望我的最终输出如下所示：

ID   status-before              status-current     DATE-timestamp       Time Diff(Hours)
5    Un-Assigned                Assigned        2013-12-17 14:14:04    xx
5    Assigned                   Completed       2013-12-21 14:14:04    xx
7    Un-Assigned                Assigned        2014-01-02 14:14:04    xx 
7    Assigned                   Completed       2014-01-21 14:14:04    xx
7    Completed                  Locked          2014-01-21 14:14:04    xx
8    Un_Assigned                Assigned        2014-12-21 14:14:04    xx
8    Assigned                   Completed       2014-12-27 14:14:04    xx

- Mirza Hashim

---

为什么有两个status_after，而且最终输出没有意义。 - user8406805

你能手动写出ID为7的三行中“xx”应该是什么吗？ - L_J

不知道xx应该是什么，你的问题不够清晰。如果每个id只有两行，为什么不让结果集每个id只有一行呢？ - Gordon Linoff

为什么你的表格有相同的列名 status-after？ - Alpesh Jikadra

Answer 1

如果您对指派时间和完成时间之间的时间差感兴趣，可以使用以下脚本。

SELECT a.ID, 
   b.[status-after], 
   a.[DATE-timestamp], 
   DATEDIFF(Hour, a.[DATE-timestamp], b.[DATE-timestamp]) AS TimeDiff_Hours
FROM YourTable a
JOIN YourTable b 
ON a.ID = b.ID AND b.[status-after] = 'Completed'
WHERE a.[status-after] = 'Assigned'

- L_J

Answer 2

假设您想要计算id的下一个状态的时间差。让我假设使用MySQL 8之前的版本。您可以使用相关子查询获取下一个时间戳，然后使用timestampdiff()函数：

select t.*,
       timestampdiff(hour, date_timestamp, next_datetimestamp) as diff_hours
from (select t.*,
             (select t2.date_timestamp
              from t t2
              where t2.id = t.id and
                    t2.date_timestamp > t.date_timestamp
              order by t2.date_timestamp asc
             ) as next_date_timestamp
      from t
     ) t;

如果你正在使用MySQL 8.0，你可以使用lead()函数来简化这个逻辑。

- Gordon Linoff

Answer 3

使用 TIMEDIFF 返回 小时:分钟:秒，或使用 DATEDIFF 返回 天数。

SELECT TIMEDIFF('complete.TIMESTAMP','assign.TIMESTAMP');
SELECT DATEDIFF('complete.TIMESTAMP','assign.TIMESTAMP');

- hatched