我有一张表格,其中包含以下数据:
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
xx
应该是什么,你的问题不够清晰。如果每个id
只有两行,为什么不让结果集每个id
只有一行呢? - Gordon Linoff