我有一个由以下数据组成的表格:
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
我使用分组依据来分组 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
使用TIMEDIFF
返回hours:minutes:seconds
或DATEDIFF
返回days
SELECT TIMEDIFF('complete.TIMESTAMP','assign.TIMESTAMP');
SELECT DATEDIFF('complete.TIMESTAMP','assign.TIMESTAMP');
如果您对"已分配"和"已完成"之间的时差感兴趣,可以使用下面的脚本。
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'
大概,您希望时间差异与 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()
函数简化此逻辑。