如何用Oracle SQL补全道路评级表年份间隙并继承历史值？

原始数据与表结构 #

以下是道路状况评级表的结构及数据：

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

需求说明 #

• 针对每条道路，从其最早检查年份开始生成至2022年的连续年份行
• 间隙年份的评级值继承最近一次已知的评级结果

期望结果 #

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

解决方案（简洁优先） #

利用Oracle的CONNECT BY生成连续年份，结合LAST_VALUE分析函数填充最近评级值，同时标记填充行：

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),
road_ranges as (
    -- 获取每条道路的最早检查年份
    select road_id, min(year) as min_year
    from road_inspections
    group by road_id
),
all_years as (
    -- 生成每条道路的连续年份
    select 
        rr.road_id,
        rr.min_year + level - 1 as year
    from road_ranges rr
    connect by level <= 2022 - rr.min_year + 1
    and prior road_id = road_id
    and prior sys_guid() is not null -- 避免层级循环
)
select 
    ay.road_id,
    ay.year,
    -- 拼接评级值与填充标记，匹配期望结果格式
    concat(
        last_value(ri.cond ignore nulls) over (partition by ay.road_id order by ay.year),
        case when exists (select 1 from road_inspections ri where ri.road_id = ay.road_id and ri.year = ay.year) then '' else ' *' end
    ) as cond
from all_years ay
left join road_inspections ri on ay.road_id = ri.road_id and ay.year = ri.year
order by ay.road_id, ay.year;

关键逻辑说明 #

1. 生成连续年份：先通过road_ranges获取每条道路的最早检查年份，再用CONNECT BY level生成从最早年份到2022年的所有连续年份。
2. 填充评级值：使用LAST_VALUE(ri.cond ignore nulls)分析函数，按年份排序后自动继承最近的非空评级结果。
3. 标记填充行：通过CASE判断当前年份是否存在原始检查记录，不存在则追加*标记。

内容的提问来源于stack exchange，提问作者User1974