Generate rows to fill in gaps between years, carry over a value from previous year

I have a table of road condition ratings (roads are rated from 1-20; 20 being good).

with road_inspections 
(road_id, year, cond) as (

select 1, 2009,   17 from dual union all
select 1, 2011,   16 from dual union all
select 1, 2015,   14 from dual union all
select 1, 2016, 18.3 from dual union all
select 1, 2019, 18.1 from dual union all

select 2, 2013, 17.5 from dual union all
select 2, 2016,   18 from dual union all
select 2, 2019,   18 from dual union all
select 2, 2022,   18 from dual union all

select 3, 2022,   20 from dual)


select * from road_inspections

   ROAD_ID       YEAR       COND
---------- ---------- ----------
         1       2009         17
         1       2011         16
         1       2015         14
         1       2016       18.3
         1       2019       18.1
         2       2013       17.5
         2       2016         18
         2       2019         18
         2       2022         18
         3       2022         20

db<>fiddle

In a query, for each road, I want to generate rows to fill in the gaps between the years.

1. For a given road, starting at the first row (the earliest inspection), there should be consecutive rows for each year all the way to the current year (the sysdate year; currently 2022).
2. For the filler rows, I want carry over the condition rating from the last known inspection.

The result would look like this:

   ROAD_ID       YEAR       COND
---------- ---------- ----------
         1       2009         17
         1       2010         17 *
         1       2011         16
         1       2012         16 *
         1       2013         16 *
         1       2014         16 *
         1       2015         14
         1       2016       18.3
         1       2017       18.3 *
         1       2018       18.3 *
         1       2019       18.1
         1       2020       18.1 *
         1       2021       18.1 *
         1       2022       18.1 *

         2       2013       17.5
         2       2014       17.5 *
         2       2015       17.5 *
         2       2016         18
         2       2017         18 *
         2       2018         18 *
         2       2019         18
         2       2020         18 *
         2       2021         18 *
         2       2022         18

         3       2022         20

*=filler row

---

Question:

How can I create those filler rows using an Oracle SQL query?

(My priorities are: simplicity first, performance second.)

Answer

You can use the LEAD analytic function with a LATERAL joined hierarchical query to generate the missing rows from each row until the next row:

SELECT r.road_id,
       y.year,
       r.cond
FROM   ( SELECT r.*,
                LEAD(year, 1, EXTRACT(YEAR FROM SYSDATE) + 1)
                  OVER (PARTITION BY road_id ORDER BY year) AS next_year
         FROM   road_inspections r
       ) r
       CROSS JOIN LATERAL (
         SELECT r.year + LEVEL - 1 AS year
         FROM   DUAL
         CONNECT BY r.year + LEVEL - 1 < r.next_year
       ) y

Which, for the sample data:

CREATE TABLE road_inspections (road_id, year, cond) as 
select 1, 2009,   17 from dual union all
select 1, 2011,   16 from dual union all
select 1, 2015,   14 from dual union all
select 1, 2016, 18.3 from dual union all
select 1, 2019, 18.1 from dual union all
select 2, 2013, 17.5 from dual union all
select 2, 2016,   18 from dual union all
select 2, 2019,   18 from dual union all
select 2, 2022,   18 from dual union all
select 3, 2022,   20 from dual;

Outputs:

ROAD_ID	YEAR	COND
1	2009	17
1	2010	17
1	2011	16
1	2012	16
1	2013	16
1	2014	16
1	2015	14
1	2016	18.3
1	2017	18.3
1	2018	18.3
1	2019	18.1
1	2020	18.1
1	2021	18.1
1	2022	18.1
2	2013	17.5
2	2014	17.5
2	2015	17.5
2	2016	18
2	2017	18
2	2018	18
2	2019	18
2	2020	18
2	2021	18
2	2022	18
3	2022	20

db<>fiddle here