oracle 的lag,oracle分析函数lag

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

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