您可以将LAST_VALUE解析函数与IGNORE NULLS选项一起使用，然后过滤以仅包括最近的行：

SELECT id,
       start_value,
       end_value,
       start_date,
       end_date
FROM   (
  SELECT id,
         LAST_VALUE(start_value)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_value,
         LAST_VALUE(end_value)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_value,
         LAST_VALUE(CASE WHEN start_value IS NOT NULL THEN "DATE" END)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_date,
         LAST_VALUE(CASE WHEN end_value IS NOT NULL THEN "DATE" END)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_date,
         ROW_NUMBER()
            OVER (PARTITION BY id ORDER BY "DATE" DESC) AS rn
  FROM   table_name
)
WHERE  rn = 1

其中，对于样本数据：

CREATE TABLE table_name (id, start_value, end_value, "DATE") AS
SELECT 1, null, null, DATE '2023-04-05' FROM DUAL UNION ALL
SELECT 1, null, 5,    DATE '2023-04-09' FROM DUAL UNION ALL
SELECT 1, 5,    null, DATE '2023-04-15' FROM DUAL UNION ALL
SELECT 1, null, 8,    DATE '2023-04-16' FROM DUAL UNION ALL
SELECT 2, 1,    null, DATE '2023-04-05' FROM DUAL UNION ALL
SELECT 2, null, 9,    DATE '2023-04-09' FROM DUAL UNION ALL
SELECT 2, 9,    null, DATE '2023-04-13' FROM DUAL UNION ALL
SELECT 2, null, -1,   DATE '2023-04-16' FROM DUAL;

输出：
| ID|开始值|END_VALUE|开始日期|结束日期|
| --------------|--------------|--------------|--------------|--------------|
| 1|五|八|2019 -04-15 00：00：00|2019 -04-16 00：00：00|
| 二|九|-1|2019 -04-13 00：00：00|2019 -04-16 00：00：00|
fiddle

有很多可能性，使用last_value（），使用row_number（）两次，然后按id和max分组，例如，为每个id取last no empty“start row”，并将其与last no empty“end row”连接：

select id, start_value, end_value, start_date, end_date
from (
    select id, start_value, dt start_date from t 
    where start_value is not null 
    order by row_number() over (partition by id order by dt desc)  
    fetch first 1 rows with ties)
natural join (
    select id, end_value, dt end_date from t 
    where end_value is not null 
    order by row_number() over (partition by id order by dt desc) 
    fetch first 1 rows with ties)

dbfiddle

其中一个选项是使用LEAD（）和LAG（）分析函数，如下所示：

WITH        --  Sample data
    tbl (ID, START_VALUE, END_VALUE, A_DATE) AS
        (   Select 1, Null, Null, To_Date('05-APR-23', 'dd-MON-yy') From Dual Union All
            Select 1, Null, 5,    To_Date('09-APR-23', 'dd-MON-yy') From Dual Union All
            Select 1, 5,      Null, To_Date('15-APR-23', 'dd-MON-yy') From Dual Union All
            Select 1, Null, 8,    To_Date('16-APR-23', 'dd-MON-yy') From Dual Union All
            Select 2, 1,    Null, To_Date('05-APR-23', 'dd-MON-yy') From Dual Union All
            Select 2, Null, 9,    To_Date('09-APR-23', 'dd-MON-yy') From Dual Union All
            Select 2, 9,    Null, To_Date('13-APR-23', 'dd-MON-yy') From Dual Union All
            Select 2, Null, -1,   To_Date('16-APR-23', 'dd-MON-yy') From Dual 
        )
--
--  Main SQL
Select  ID, Max(STARTS) "START_VALUE", Max(ENDS) "END_VALUE", START_DATE, END_DATE
From    ( Select    ID, ROW_NUMBER() OVER(Partition By ID Order By A_DATE DESC) "RN",
                    CASE  WHEN LEAD(ID) OVER(Partition By ID Order By A_DATE DESC) = ID 
                        THEN LEAD(START_VALUE) OVER(Partition By ID Order By A_DATE DESC) 
                    END "STARTS",
                    CASE  WHEN LAG(ID) OVER(Partition By ID Order By A_DATE DESC) = ID
                        THEN LAG(END_VALUE) OVER(Partition By ID Order By A_DATE DESC) 
                    END "ENDS",
                    MAX(CASE WHEN START_VALUE Is Not Null THEN A_DATE END) OVER(Partition By ID) "START_DATE",
                    MAX(CASE WHEN END_VALUE Is Not Null THEN A_DATE END) OVER(Partition By ID) "END_DATE"
          From  tbl
        )
Where RN <= 2
Group By ID, START_DATE, END_DATE
Order By ID
--
--  R e s u l t:
        ID START_VALUE  END_VALUE START_DATE END_DATE 
---------- ----------- ---------- ---------- ---------
         1           5          8  15-APR-23 16-APR-23
         2           9         -1  13-APR-23 16-APR-23