Как мне получить «следующее» событие, когда смещение является переменным для элементов, которые могут обрабатываться повторно?

Это проблема пробелов и островов, но островки, определяемые REQ транзакцией, делают ее немного сложнее, чем некоторые.

Вы можете использовать вложенные функции опережения и отставания и некоторые манипуляции, чтобы получить то, что вам нужно:

select distinct item,
  coalesce(start_tran,
    lag(start_tran) over (partition by item order by timestamp)) as start_tran,
  coalesce(end_tran,
    lead(end_tran) over (partition by item order by timestamp)) as end_tran,
  coalesce(end_time, 
    lead(end_time) over (partition by item order by timestamp))
    - coalesce(start_time,
        lag(start_time) over (partition by item order by timestamp)) as time
from (
  select item, timestamp, start_tran, start_time, end_tran, end_time
  from (
    select item,
      timestamp,
      case when lag_tran is null or transaction like 'REQ%'
        then transaction end as start_tran,
      case when lag_tran is null or transaction like 'REQ%'
        then timestamp end as start_time,
      case when lead_tran is null or lead_tran like 'REQ%'
        then transaction end as end_tran,
      case when lead_tran is null or lead_tran like 'REQ%'
        then timestamp end as end_time
    from (
      select item, transaction, timestamp,
        lag(transaction)
          over (partition by item order by timestamp) as lag_tran,
        lead(transaction)
          over (partition by item order by timestamp) as lead_tran
      from transactions
    )
  )
  where start_tran is not null or end_tran is not null
)
order by item, start_tran;

С дополнительными записями для второго цикла по пунктам 1 и 2, которые могут дать:

      ITEM START_TRAN END_TRAN   TIME      
---------- ---------- ---------- -----------
         1 REQ-A      PICKUP     0 1:53:30.0 
         1 REQ-E      PICKUP     0 1:23:30.0 
         2 REQ-B      MAIL       0 0:24:13.0 
         2 REQ-F      REQ-F      0 0:0:0.0   
         3 REQ-C      PICKUP     0 1:46:30.0 
         4 REQ-D      PULL       0 0:23:59.0 
         5 REQ-A      PICKUP     0 1:43:59.0 

SQL Fiddle, показывающий все промежуточные шаги.

Все не так страшно, как может показаться на первый взгляд. Самый внутренний запрос берет необработанные данные и добавляет дополнительный столбец для опережающих и отстающих транзакций. Взяв только первый набор записей item-1, который будет:

      ITEM TRANSACTION TIMESTAMP                LAG_TRAN   LEAD_TRAN
---------- ----------- ------------------------ ---------- ----------
         1 REQ-A       2014-07-31T09:51:32Z                PULL       
         1 PULL        2014-07-31T10:22:21Z     REQ-A      TRANSFER   
         1 TRANSFER    2014-07-31T10:22:23Z     PULL       ARRIVE     
         1 ARRIVE      2014-07-31T11:45:01Z     TRANSFER   PICKUP     
         1 PICKUP      2014-07-31T11:45:02Z     ARRIVE     REQ-E      

Заметили, что REQ-E появляется как последнее lead_tran? Это первая transaction для второго цикла записей для этого элемента, и она пригодится позже. Следующий уровень запроса использует эти значения опережения и запаздывания и обрабатывает значения REQ как начальные и конечные маркеры и использует эту информацию для обнуления всего, кроме первой и последней записи для каждого цикла.

      ITEM TIMESTAMP                START_TRAN START_TIME               END_TRAN   END_TIME               
---------- ------------------------ ---------- ------------------------ ---------- ------------------------
         1 2014-07-31T09:51:32Z     REQ-A      2014-07-31T09:51:32Z                                         
         1 2014-07-31T10:22:21Z                                                                             
         1 2014-07-31T10:22:23Z                                                                             
         1 2014-07-31T11:45:01Z                                                                             
         1 2014-07-31T11:45:02Z                                         PICKUP     2014-07-31T11:45:02Z     

Следующий уровень запроса удаляет все строки, которые не представляют начало или конец (или оба — см. REQ-F в Fiddle), поскольку они нам не интересны:

      ITEM TIMESTAMP                START_TRAN START_TIME               END_TRAN   END_TIME               
---------- ------------------------ ---------- ------------------------ ---------- ------------------------
         1 2014-07-31T09:51:32Z     REQ-A      2014-07-31T09:51:32Z                                         
         1 2014-07-31T11:45:02Z                                         PICKUP     2014-07-31T11:45:02Z     

Теперь у нас есть пары строк для каждого цикла (или одна строка для REQ-F). На последнем уровне снова используется опережение и отставание, чтобы заполнить пробелы; если start_tran равно нулю, то это конечная строка, и мы должны использовать начальные данные предыдущей строки; если end_tran равно нулю, то это начальная строка, и мы должны использовать конечные данные следующей строки.

  ITEM START_TRAN START_TIME               END_TRAN   END_TIME                 TIME      

     1 REQ-A      2014-07-31T09:51:32Z     PICKUP     2014-07-31T11:45:02Z     0 1:53:30.0 
     1 REQ-A      2014-07-31T09:51:32Z     PICKUP     2014-07-31T11:45:02Z     0 1:53:30.0 

Это делает обе строки одинаковыми, поэтому distinct удаляет дубликаты.

Это должно дать вам тот же результат. Я воспроизвожу ответ Гордона, и он все еще остается в силе

select item,
       min(transaction) keep (dense_rank first order by timestamp) as StartTx, 
       min(transaction) keep (dense_rank last order by timestamp) as EndTx,
       max(timestamp) - min(timestamp)
from transactions t
group by item;

Даже если у вас есть дубликаты в txn, они будут обработаны аналитической функцией. См. документацию для плотного ранжирования и ключевого слова keep.