Oracle SQL：筛选仅相差很小时间的重复行

Oracle SQL：筛选仅相差很小时间的重复行

您可以利用

range

分析函数的子句：

with dups as (  select t1.*       , row_number() over (partition by PKN_EVENTNAME, RECEIVEDDATEorder by id         ) as dup  from PARQUIMETERS_ALARMS t1), nodups as (  select * from dups where dup = 1), t as (  select nodups.ID, nodups.PKN_EVENTNAME, nodups.RECEIVEDDATE       , count(*) over (partition by nodups.PKN_EVENTNAMEorder by nodups.RECEIVEDDATErange between interval '10' second preceding and current row         ) as cnt  from nodups)select * from t where cnt = 1

（已更新：CTE，

dups

并且

nodups

在注释中显示的OP之后添加了重复的元组

(PKN_EVENTNAME, RECEIVEDDATE)

。）

说明：清除通过

nodups

CTE传递的数据后，该

where

条件仅过滤在最近10 s中仅存在单行的行（显然是当前行）。