Road lifecycles: Partition rows into groups

group byoracleoracle-12cselect

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?

Best Answer

Here's a rather clumsy solution (thank's for the ddl and sample data btw):

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;

The idea is to use different partitions for the row_number() functions (one with type, one without). If the difference between these changes it means that type has changed. We use this to create grp.

The case statement is used to compensate when type goes from CONSTRUCTION to INSPECTION.

This certainly can be improved in a number of ways, but will perhaps give you some ideas.

Related Solutions

Oracle – Fill Gaps Between Rows Using Previous Row’s Values

with
  date_range as (select min(insp_date) mind, max(insp_date) maxd from road_insp),
  insp_dates as (select add_months(trunc(mind, 'YYYY'), (level - 1)*12) as insp_date 
   from date_range connect by level <= extract(year from maxd) - extract(year from mind))
select
   ri.insp_id, ri.road_id, 
   nvl(case when extract(year from ri.insp_date) = extract(year from id.insp_date) 
     then ri.insp_date else id.insp_date end, ri.insp_date) as insp_date,
   ri.condition
from
  insp_dates id right join
  (select insp_id, road_id, insp_date, condition as condition,
     lead(insp_date) over (partition by road_id order by insp_date) as next_insp_date
  from road_insp) ri 
  on id.insp_date >= trunc(ri.insp_date, 'YYYY') 
    and id.insp_date < trunc(ri.next_insp_date, 'YYYY')
order by insp_id, road_id, insp_date
;

Generate dates between min and max insp_date.

Find the next insp_date for each inspection to have them in 1 row.

Join the generated dates to the original dataset on date ranges based on year, excluding the upper bound, because that is the next inspection with existing data. Outer join, so the last inspection for a road (where next inspection is null) is included as well.

If there is generated insp_date is in the same year as the original data, use the original data, otherwise use the generated insp_date.

   INSP_ID    ROAD_ID INSP_DATE   CONDITION
---------- ---------- ---------- ----------
         1        100 2004-04-01         .9
         1        100 2005-01-01         .9
         1        100 2006-01-01         .9
         1        100 2007-01-01         .9
         1        100 2008-01-01         .9
         1        100 2009-01-01         .9
         1        100 2010-01-01         .9
         2        100 2011-04-01         .7
         3        100 2012-03-01         .7
         3        100 2013-01-01         .7
         3        100 2014-01-01         .7
         3        100 2015-01-01         .7
         3        100 2016-01-01         .7
         4        100 2017-03-01         .6
         5        100 2018-03-01         .6
         6        200 2010-06-01         .4
         6        200 2011-01-01         .4
         7        200 2012-06-01         .3

Categorize rows as first, last or in between

You can use two window functions, min() and max() with the same window, over the partition by road_id:

select  event_id,
        road_id,
        event_type,
        lifecycle_number,
        case lifecycle_number
            when max(lifecycle_number) over (partition by road_id)
                then 'CURRENT LIFECYCLE'
            when min(lifecycle_number) over (partition by road_id)  
                then 'ORIGINAL LIFECYCLE'
                else 'PAST LIFECYCLE'
        end as LIFECYCLE_NAME
from    road_events re
order by event_id;

You could also use first_value() and last_value() window functions.

Test at dbfiddle.uk

Best Answer

Related Solutions

Oracle – Fill Gaps Between Rows Using Previous Row’s Values

Categorize rows as first, last or in between

Related Question