如果数量增加,则排除记录组

这里有一个选项:

• 查找“下一个” condition_rating 值(在相同范围内 road_id -那就是 partition by 子句,排序依据 insp_date )
• 回来 道路id “下一个”和“当前”之间的差异 条件_评级 小于零

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>

基于OP自己的回答,这使得预期结果更加清晰。

出于避免自联接的永久冲动,我选择了嵌套窗口函数:

SELECT road_id, condition_rating, insp_date
  FROM ( SELECT prev.*
              , COUNT(CASE WHEN condition_rating < next_cr THEN 1 END) OVER(PARTITION BY road_id) bad
           FROM (select t.*
                      , lead(condition_rating) over (partition by road_id order by insp_date) next_cr
                  from t
                ) prev
       ) tagged
WHERE bad = 0
ORDER BY road_id, insp_date

注释

• lead() 给予 null 的查询考虑的最后一行 case 标记坏行的表达式: condition_rating < next_cr 如果 next_cr 是 无效的 ,条件将不成立,因此 案例 将其映射为“不错”。
• 这个 案例 只是为了模仿 filter 条款: https://modern-sql.com/feature/filter
• MATCH_RECOGNIZE 可能是这个问题的另一个选择,但由于缺少“^”和“$”,我担心回溯可能会导致更多问题,这是值得的。
• 嵌套的窗口函数如果使用兼容的,通常不会对性能造成很大影响 OVER 子句,如此查询中所示。

以下是一个与@Littlefoot的答案相似的答案:

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 insp_date)   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

--Note: I removed the ORDER BY, because I think the window function already orders the rows the way I want.

db<>fiddle

编辑:

这是一个与@Markus Winand所做的类似的版本:

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

select
    insp_tagged.*
from
    (
    select
        insp.*,
        count(condition_goes_up) over(partition by road_id) as condition_goes_up_count
    from
        insp
    ) insp_tagged
where
    condition_goes_up_count = 0 

我最终选择了那个选项。

db<>fiddle