Oracle
道路生命週期：將行劃分為組
我有一張
constr_events桌子：create table constr_events ( event_id number(4) not null unique, road_id number(4) not null, constr_date date not null, reset_condition number(9,2) not null ) ; insert into constr_events (event_id, road_id, constr_date, reset_condition) values (1,400,to_date('2016-11-01','YYYY-MM-DD'),1); insert into constr_events (event_id, road_id, constr_date, reset_condition) values (2,500,to_date('2009-11-01','YYYY-MM-DD'),1); insert into constr_events (event_id, road_id, constr_date, reset_condition) values (3,500,to_date('2031-11-01','YYYY-MM-DD'),1); select event_id, road_id, to_char(constr_date,'YYYY-MM-DD') as constr_date_formatted, reset_condition from constr_events order by road_id, constr_date; EVENT_ID ROAD_ID CONSTR_DATE_FORMATTED RESET_CONDITION ---------- ---------- --------------------- --------------- 1 400 2016-11-01 1 2 500 2009-11-01 1 3 500 2031-11-01 1還有一張
road_insp桌子：create table road_insp ( insp_id number(4) not null unique, road_id number(4) not null, insp_date date not null, condition number(9,2) not null ) ; insert into road_insp (insp_id, road_id, insp_date, condition) values (40,400,to_date('1960-05-01','YYYY-MM-DD'),.8); insert into road_insp (insp_id, road_id, insp_date, condition) values (41,400,to_date('2009-05-01','YYYY-MM-DD'),.3); insert into road_insp (insp_id, road_id, insp_date, condition) values (42,400,to_date('2012-05-01','YYYY-MM-DD'),.3); insert into road_insp (insp_id, road_id, insp_date, condition) values (43,400,to_date('2015-05-01','YYYY-MM-DD'),.2); insert into road_insp (insp_id, road_id, insp_date, condition) values (45,400,to_date('2019-05-01','YYYY-MM-DD'),.9); insert into road_insp (insp_id, road_id, insp_date, condition) values (50,500,to_date('1994-05-01','YYYY-MM-DD'),.6); insert into road_insp (insp_id, road_id, insp_date, condition) values (90,500,to_date('2006-05-01','YYYY-MM-DD'),.30); insert into road_insp (insp_id, road_id, insp_date, condition) values (67,500,to_date('2010-05-01','YYYY-MM-DD'),.9); insert into road_insp (insp_id, road_id, insp_date, condition) values (68,500,to_date('2014-05-01','YYYY-MM-DD'),.9); insert into road_insp (insp_id, road_id, insp_date, condition) values (69,500,to_date('2017-05-01','YYYY-MM-DD'),.9); insert into road_insp (insp_id, road_id, insp_date, condition) values (70,500,to_date('2020-05-01','YYYY-MM-DD'),.8); insert into road_insp (insp_id, road_id, insp_date, condition) values (71,500,to_date('2023-05-01','YYYY-MM-DD'),.8); insert into road_insp (insp_id, road_id, insp_date, condition) values (72,500,to_date('2026-05-01','YYYY-MM-DD'),.7); insert into road_insp (insp_id, road_id, insp_date, condition) values (73,500,to_date('2029-05-01','YYYY-MM-DD'),.6); insert into road_insp (insp_id, road_id, insp_date, condition) values (74,500,to_date('2032-05-01','YYYY-MM-DD'),.9); insert into road_insp (insp_id, road_id, insp_date, condition) values (75,500,to_date('2035-05-01','YYYY-MM-DD'),.9); commit; select insp_id, road_id, to_char(insp_date,'YYYY-MM-DD') as insp_date_formatted, condition as condition from road_insp order by road_id, insp_date; INSP_ID ROAD_ID INSP_DATE_FORMATTED CONDITION ---------- ---------- ------------------- ---------- 40 400 1960-05-01 .8 41 400 2009-05-01 .3 42 400 2012-05-01 .3 43 400 2015-05-01 .2 45 400 2019-05-01 .9 50 500 1994-05-01 .6 90 500 2006-05-01 .3 67 500 2010-05-01 .9 68 500 2014-05-01 .9 69 500 2017-05-01 .9 70 500 2020-05-01 .8 71 500 2023-05-01 .8 72 500 2026-05-01 .7 73 500 2029-05-01 .6 74 500 2032-05-01 .9 75 500 2035-05-01 .9當我將這些表合併在一起時，它們看起來像這樣：
select road_id, to_char(insp_date,'YYYY-MM-DD') as condition_date, condition as condition, 'INSPECTION' as type from road_insp union all select road_id, to_char(constr_date,'YYYY-MM-DD') as condition_date, reset_condition, 'CONSTRUCTION' as type from constr_events order by road_id, condition_date ROAD_ID CONDITION_DATE CONDITION TYPE ---------- -------------- ---------- ------------ 400 1960-05-01 .8 INSPECTION 400 2009-05-01 .3 INSPECTION 400 2012-05-01 .3 INSPECTION 400 2015-05-01 .2 INSPECTION 400 2016-11-01 1 CONSTRUCTION 400 2019-05-01 .9 INSPECTION 500 1994-05-01 .6 INSPECTION 500 2006-05-01 .3 INSPECTION 500 2009-11-01 1 CONSTRUCTION 500 2010-05-01 .9 INSPECTION 500 2014-05-01 .9 INSPECTION 500 2017-05-01 .9 INSPECTION 500 2020-05-01 .8 INSPECTION 500 2023-05-01 .8 INSPECTION 500 2026-05-01 .7 INSPECTION 500 2029-05-01 .6 INSPECTION 500 2031-11-01 1 CONSTRUCTION 500 2032-05-01 .9 INSPECTION 500 2035-05-01 .9 INSPECTION問題：
我想將數據分組。每個施工事件和以下檢查都會有一個*小組。*每個生命週期組都有一個 ID。
它看起來像這樣：
+---------+----------------+-----------+--------------+-------------------+ | ROAD_ID | CONDITION_DATE | CONDITION | TYPE | LIFEYCYCLE_GROUP | <<---- +---------+----------------+-----------+--------------+-------------------+ | 400 | 1960-05-01 | .8 | INSPECTION | 400-LIFECYCLE-1 | | 400 | 2009-05-01 | .3 | INSPECTION | 400-LIFECYCLE-1 | | 400 | 2012-05-01 | .3 | INSPECTION | 400-LIFECYCLE-1 | | 400 | 2015-05-01 | .2 | INSPECTION | 400-LIFECYCLE-1 | | | | | | | | 400 | 2016-11-01 | 1 | CONSTRUCTION | 400-LIFECYCLE-2 | | 400 | 2019-05-01 | .9 | INSPECTION | 400-LIFECYCLE-2 | +---------+----------------+-----------+--------------+-------------------+ | 500 | 1994-05-01 | .6 | INSPECTION | 500-LIFECYCLE-1 | | 500 | 2006-05-01 | .3 | INSPECTION | 500-LIFECYCLE-1 | | | | | | | | 500 | 2009-11-01 | 1 | CONSTRUCTION | 500-LIFECYCLE-2 | | 500 | 2010-05-01 | .9 | INSPECTION | 500-LIFECYCLE-2 | | 500 | 2014-05-01 | .9 | INSPECTION | 500-LIFECYCLE-2 | | 500 | 2017-05-01 | .9 | INSPECTION | 500-LIFECYCLE-2 | | 500 | 2020-05-01 | .8 | INSPECTION | 500-LIFECYCLE-2 | | 500 | 2023-05-01 | .8 | INSPECTION | 500-LIFECYCLE-2 | | 500 | 2026-05-01 | .7 | INSPECTION | 500-LIFECYCLE-2 | | 500 | 2029-05-01 | .6 | INSPECTION | 500-LIFECYCLE-2 | | | | | | | | 500 | 2031-11-01 | 1 | CONSTRUCTION | 500-LIFECYCLE-3 | | 500 | 2032-05-01 | .9 | INSPECTION | 500-LIFECYCLE-3 | | 500 | 2035-05-01 | .9 | INSPECTION | 500-LIFECYCLE-3 | +---------+----------------+-----------+--------------+-------------------+不過有一個問題。我們並不總是為道路的初始建設舉辦建設活動。這在樣本數據中的兩條道路中都得到了證明。即使沒有初始構造事件，查詢也需要通過創建組來允許這種怪癖。
如何進行此查詢？
@ypercubeᵀᴹ 的解決方案似乎可以解決問題：
count(case when type='CONSTRUCTION' then 1 end) over (partition by road_id order by condition_date)
這是一個相當笨拙的解決方案（感謝 ddl 和範例數據順便說一句）：
select road_id, condition_date, condition, type , to_char(road_id) || '-LIFECYCLE-' || to_char(dense_rank() over (partition by road_id order by grp) - case when type = 'CONSTRUCTION' then 1 else 0 end) as lifecycle_group from ( select road_id, condition_date, condition, type , (row_number() over (partition by road_id order by condition_date) - row_number() over (partition by road_id, type order by condition_date)) as grp from ( select road_id, insp_date as condition_date , condition as condition, 'INSPECTION' as type from road_insp union all select road_id, constr_date as condition_date , reset_condition, 'CONSTRUCTION' as type from constr_events ) ) order by road_id, condition_date, grp;這個想法是為 row_number() 函式使用不同的分區（一個有類型，一個沒有）。如果這些變化之間的差異意味著類型已經改變。我們用它來創建grp。
當類型從 CONSTRUCTION 變為 INSPECTION 時，case 語句用於補償。
這當然可以通過多種方式改進，但也許會給你一些想法。