排除一组记录--如果数量增加的话

Question

我有一个道路检查表：

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

---

我想写一个查询，如果道路状况随着时间的推移而上升，它将排除整个道路。例如，排除道路3180，因为条件从9变为17(异常(。

问题：

如何使用Oracle SQL做到这一点？

---

样本数据：db<gt；小提琴

Answer 1

这里有一个选项：

• 查找"；下一个"；condition_rating值(在同一个road_id中——这是partition by子句，按insp_date排序(
• 返回CCD_ 6；下一个"；以及"；当前"；condition_rating小于零

---

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>

Answer 2

基于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()为查询认为由case表达式标记坏行的最后一行给出null：condition_rating < next_cr-如果next_cr是null，则条件将不为真，因此case将其映射为"0"；不错">
• case只是模仿filter子句：https://modern-sql.com/feature/filter
• MATCH_RECOGNIZE可能是解决这个问题的另一个选项，但由于缺少"^"one_answers"$"，我担心回溯可能会导致更多问题，这是值得的
• 如果嵌套的窗口函数使用兼容的OVER子句，那么它们的性能通常不会受到太大的影响，就像在这个查询中一样

Answer 3

这里有一个类似@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<gt；小提琴

---

编辑：

这是一个与@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<gt；小提琴