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

Question

我想到了这个问题，因为我刚刚发现这个网站，所以我决定把它贴在这里。

假设我有一个表，其中包含给定“对象”(一般含义，不是 OOP 对象)的时间戳和状态；有没有一种最佳方法可以使用单个 SQL 语句(不计算内部 SELECT 和 UNION)来计算一个状态与下一次出现另一个(或相同)状态(我称之为“旅行”)之间的时间？

例如:对于以下情况，初始和完成之间的行程时间为 6 天，但初始和审核之间的行程时间为 2 天。

2008-08-01 13:30:00 - Initial
2008-08-02 13:30:00 - Work
2008-08-03 13:30:00 - Review
2008-08-04 13:30:00 - Work
2008-08-05 13:30:00 - Review
2008-08-06 13:30:00 - Accepted
2008-08-07 13:30:00 - Done

不用笼统，直说什么SGBD如果不是通用的，您的解决方案是特定的。

Answer 1

这是一个使用分析函数的 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

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