Oracle
最新已知路況
桌子：
我有一張
road_condition_test桌子：create table road_condition_test ( road_id int, age int, condition number(10,1) ); insert into road_condition_test (road_id,age,condition) values ('500080',0,20); insert into road_condition_test (road_id,age,condition) values ('500080',1,20); insert into road_condition_test (road_id,age,condition) values ('500080',2,20); insert into road_condition_test (road_id,age,condition) values ('500080',3,20); insert into road_condition_test (road_id,age,condition) values ('500080',4,19.6); insert into road_condition_test (road_id,age,condition) values ('500080',5,19.2); insert into road_condition_test (road_id,age,condition) values ('500080',6,18.8); insert into road_condition_test (road_id,age,condition) values ('500080',7,18.4); insert into road_condition_test (road_id,age,condition) values ('500080',8,18); insert into road_condition_test (road_id,age,condition) values ('500080',9,18); insert into road_condition_test (road_id,age,condition) values ('500080',10,18); insert into road_condition_test (road_id,age,condition) values ('500080',11,18); insert into road_condition_test (road_id,age,condition) values ('500080',12,null); insert into road_condition_test (road_id,age,condition) values ('500080',13,null); insert into road_condition_test (road_id,age,condition) values ('500080',14,null); insert into road_condition_test (road_id,age,condition) values ('500080',15,null); insert into road_condition_test (road_id,age,condition) values ('500100',0,18); insert into road_condition_test (road_id,age,condition) values ('500100',1,18.3); insert into road_condition_test (road_id,age,condition) values ('500100',2,18.6); insert into road_condition_test (road_id,age,condition) values ('500100',3,19); insert into road_condition_test (road_id,age,condition) values ('500100',4,19); insert into road_condition_test (road_id,age,condition) values ('500100',5,19); insert into road_condition_test (road_id,age,condition) values ('500100',6,19); insert into road_condition_test (road_id,age,condition) values ('500100',7,null); insert into road_condition_test (road_id,age,condition) values ('500100',8,null); insert into road_condition_test (road_id,age,condition) values ('500100',9,null); insert into road_condition_test (road_id,age,condition) values ('500100',10,null); insert into road_condition_test (road_id,age,condition) values ('500100',11,null); insert into road_condition_test (road_id,age,condition) values ('500100',12,null); insert into road_condition_test (road_id,age,condition) values ('500100',13,null); insert into road_condition_test (road_id,age,condition) values ('500100',14,null); insert into road_condition_test (road_id,age,condition) values ('500100',15,null); commit;ROAD_ID AGE CONDITION ---------- ---------- ---------- 500080 0 20 500080 1 20 500080 2 20 500080 3 20 500080 4 19.6 500080 5 19.2 500080 6 18.8 500080 7 18.4 500080 8 18 500080 9 18 500080 10 18 500080 11 18 500080 12 500080 13 500080 14 500080 15 500100 0 18 500100 1 18.3 500100 2 18.6 500100 3 19 500100 4 19 500100 5 19 500100 6 19 500100 7 500100 8 500100 9 500100 10 500100 11 500100 12 500100 13 500100 14 500100 15問題：
我想編寫一個查詢，其中有一列名為
latest_known_condition.對於每條道路，該列將包含不為空
latest_known_condition的最新情況（根據）。age注意：不要將最新條件與最低條件混淆。由於 A) 檢查中的違規行為，和/或 B) 道路維修，狀況不會隨著時間的推移而嚴格下降（例如：道路
500100）。該列只會填充包括最新已知條件和之後的行。
ROAD_ID AGE CONDITION LATEST_KNOWN_CONDITION ---------- ---------- ---------- -------------------- 500080 0 20 500080 1 20 500080 2 20 500080 3 20 500080 4 19.6 500080 5 19.2 500080 6 18.8 500080 7 18.4 500080 8 18 500080 9 18 500080 10 18 500080 11 18 18 500080 12 18 500080 13 18 500080 14 18 500080 15 18 500100 0 18 500100 1 18.3 500100 2 18.6 500100 3 19 500100 4 19 500100 5 19 500100 6 19 19 500100 7 19 500100 8 19 500100 9 19 500100 10 19 500100 11 19 500100 11 19 500100 12 19 500100 13 19 500100 14 19 500100 15 19我怎樣才能做到這一點？
select road_id, age, condition, case when (condition is null or lead(condition) over (partition by road_id order by age) is null) then last_value(condition ignore nulls) over (partition by road_id order by age) end from road_condition_test order by road_id, age, condition; ROAD_ID AGE CONDITION LATEST_KNOWN_CONDITION ---------- ---------- ---------- ---------------------- 500080 0 20 500080 1 20 500080 2 20 500080 3 20 500080 4 19.6 500080 5 19.2 500080 6 18.8 500080 7 18.4 500080 8 18 500080 9 18 500080 10 18 500080 11 18 18 500080 12 18 500080 13 18 500080 14 18 500080 15 18 500100 0 18 500100 1 18.3 500100 2 18.6 500100 3 19 500100 4 19 500100 5 19 500100 6 19 19 500100 7 19 500100 8 19 500100 9 19 500100 10 19 500100 11 19 500100 12 19 500100 13 19 500100 14 19 500100 15 19