Exclude group of records—if number ever goes up-CodePudding

I have a road inspection table:

INSPECTION_ID         ROAD_ID       INSP_DATE CONDITION_RATING
--------------------- ------------- --------- ----------------
506411                3040          01-JAN-81               15
508738                3040          14-APR-85               15
512461                3040          22-MAY-88               14
515077                3040          17-MAY-91               14 -- all ok

505967                3180          01-MAY-81               11
507655                3180          13-APR-85                9
512374                3180          11-MAY-88               17 <-- goes up; NOT ok
515626                3180          25-APR-91             16.5

502798                3260          01-MAY-83               14
508747                3260          13-APR-85               13
511373                3260          11-MAY-88               12
514734                3260          25-APR-91               12  -- all ok

12 rows selected.

I want to write a query that will exclude rows if the road's condition ever goes up over time. For example, exclude road 3180, since the condition goes from 9 to 17 (an anomaly).

Question:

How can I do that using Oracle SQL?

Sample data: db<>fiddle

CodePudding user response：

Here's one option:

find "next" condition_rating value (within the same road_id - that's the partition by clause, sorted by insp_date)
return road_id whose difference between the "next" and "current" condition_rating is less than zero

SQL> with temp as
  2    (select road_id,
  3            condition_rating,
  4            nvl(lead(condition_rating) over (partition by road_id order by insp_date),
  5                condition_rating) next_cr
  6     from test
  7    )
  8  select distinct road_id
  9  from temp
 10  where condition_rating - next_cr < 0;

   ROAD_ID
----------
      3180

SQL>

CodePudding user response：

Here's an answer that's similar to @Littlefoot's answer:

with insp as (
select 
    road_id,
    condition_rating,
    insp_date,
    case when   condition_rating   >   lag(condition_rating,1) over(partition by road_id order by extract(year from insp_date), condition_rating desc)   then 'Y'   end as condition_goes_up
from 
    test_data
)

select
    insp.*
from
    insp
left join
    (
    select distinct
        road_id,
        condition_goes_up
    from
        insp
    where
        condition_goes_up = 'Y'
    ) insp_flag
    on insp.road_id = insp_flag.road_id
where
    insp_flag.condition_goes_up is null
order by
    insp.road_id,
    extract(year from insp.insp_date),
    condition_rating desc

db<>fiddle (includes test data)