我假设您使用的是当前支持的Oracle版本，因此它应该支持match_recognize：

select *
from your_table
match_recognize (
   partition by req_num
   order by trans_date
   measures
      first(trans_date) as start_dt,
      last(trans_date)  as end_dt,
      last(event)       as end_event
   pattern (start_ next_+)
   define
     start_ as (event='Posted'),
     next_ as (event!='Posted')
)

完整的DBFiddle示例：https://dbfiddle.uk/4Fai2-ZQ

如果您的实际数据看起来像问题中的那样-您可以使用条件LAG() and LEAD() analytic functions在同一行中获取开始和结束日期。

SELECT DISTINCT 
          REQ_NUM,  
          CASE WHEN EVENT  = 'Posted' THEN A_DATE ELSE LAG(A_DATE)  OVER(Order By REQ_NUM, A_DATE) END "START_DT", 
          CASE WHEN EVENT != 'Posted' THEN A_DATE ELSE LEAD(A_DATE) OVER(Order By REQ_NUM, A_DATE) END "END_DT"
FROM      tbl

......通过样本数据......

WITH
    tbl (REQ_NUM,   A_DATE, EVENT,  USER_ENTR) AS
        (
            Select  23877,  To_Date('2022-03-24', 'yyyy-mm-dd'),    'Posted',   'John' From Dual Union All
            Select  23877,  To_Date('2022-04-03', 'yyyy-mm-dd'),    'Expired',  'John' From Dual Union All
            Select  23877,  To_Date('2022-05-03', 'yyyy-mm-dd'),    'Posted',   'Jane' From Dual Union All
            Select  23877,  To_Date('2022-05-09', 'yyyy-mm-dd'),    'Expired',  'Jane' From Dual Union All
            Select  23877,  To_Date('2022-05-27', 'yyyy-mm-dd'),    'Posted',   'John' From Dual Union All
            Select  23877,  To_Date('2022-06-17', 'yyyy-mm-dd'),    'Unposted', 'John' From Dual 
        )

.应导致：

REQ_NUM START_DT  END_DT  
---------- --------- ---------
     23877 24-MAR-22 03-APR-22 
     23877 03-MAY-22 09-MAY-22 
     23877 27-MAY-22 17-JUN-22

注：DISTINCT关键字可消除重复行，对于大型数据集，这可能会导致性能成本过高。