I have a constr_events
table:
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
And a road_insp
table:
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
When I union the tables together, they look like this:
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
Question:
I would like to partition the data into groups. There would be a group for each construction event and the following inspections. Each lifecycle group would have an ID.
It would look like this:
+---------+----------------+-----------+--------------+-------------------+
| 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 |
+---------+----------------+-----------+--------------+-------------------+
There is a catch though. We don't always have a construction event for a road's initial construction. This is demonstrated in both of the roads in the sample data. The query would need to allow for this quirk by creating a group, even if there isn't an initial construction event.
How can I make this query?
count(case when type='CONSTRUCTION' then 1 end) over (partition by road_id order by condition_date)
should give you the LIFEYCLE_GROUP I think. – ypercubeᵀᴹ Nov 23 '17 at 19:11dense_rank()
androw_number()
instead ofcount() over
but still similar). Your answer could be improved ;) – ypercubeᵀᴹ Nov 24 '17 at 18:18