关系数据库中的行程时间计算？

这是使用分析函数的Oracle方法论。

with data as (
SELECT 1 trip_id, to_date('20080801 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Initial'  step from dual UNION ALL
SELECT 1 trip_id, to_date('20080802 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Work'     step from dual  UNION ALL
SELECT 1 trip_id, to_date('20080803 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Review'   step from dual  UNION ALL
SELECT 1 trip_id, to_date('20080804 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Work'     step from dual UNION ALL
SELECT 1 trip_id, to_date('20080805 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Review'   step from dual  UNION ALL
SELECT 1 trip_id, to_date('20080806 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Accepted' step from dual  UNION ALL
SELECT 1 trip_id, to_date('20080807 13:30:00','YYYYMMDD HH24:mi:ss') dt, 'Done'     step from dual )
select trip_id,
       step,
       dt - lag(dt) over (partition by trip_id order by dt) trip_time
from  data
/


1   Initial 
1   Work        1
1   Review      1
1   Work        1
1   Review      1
1   Accepted    1
1   Done        1

这些通常在传统上我们可能使用自联接的情况下使用。