我有一張路檢表：
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
我想撰寫一個排除整條道路的查詢——如果道路的狀況隨著時間的推移而上升。例如，排除 road 3180，因為條件從 9 變為 17（例外）。
問題：
如何使用 Oracle SQL 做到這一點？
樣本資料：db<>fiddle
uj5u.com熱心網友回復：
這是一個選項：
- 找到“下一個”
condition_rating值（在同一個內road_id- 這是partition by子句，按 排序insp_date） - 回傳
road_id“下一個”和“當前”之間的差異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>
uj5u.com熱心網友回復：
基于 OPs 自己的答案，這使預期的結果更加清晰。
在我永遠避免自我加入的沖動中，我會選擇嵌套視窗函式：
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是null，則條件不成立，因此將其case映射為“不錯”。- 這
case只是為了模仿filter子句：https ://modern-sql.com/feature/filter MATCH_RECOGNIZE可能是這個問題的另一種選擇，但由于缺少 '^' 和 '$' 我擔心回溯可能會導致更多問題，這是值得的。OVER如果嵌套視窗函式使用兼容的子句，通常不會對性能造成太大影響，就像在這個查詢中一樣。
uj5u.com熱心網友回復：
這是一個類似于@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<>小提琴
編輯：
這是一個類似于@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<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/415970.html
標籤：