Oracle SQL:筛选仅相差很小时间的重复行 [英] Oracle sql: filtering repeated rows that only differ by a tiny amount of time

问题描述

我有一个带有事件警报的Oracle表，由于奇怪而未知的情况，有时会重复发生条件警报，所以我被要求创建一个服务来从该Oracle表中删除重复的警报.

I have an Oracle table with event alarms, and due to a weird and unknown -to me- condition alarms get repeated sometimes so I was asked to create a service that will remove repeated alarms from that Oracle table.

重复考虑一个警报(表中的一行)的条件是，存在另一个具有相同的PKN_EVENTNAME和RECEIVEDDATE的警报，与前一个警报仅相差很小的时间(例如，假设为10秒)-上或下-).

The condition to consider an alarm (a row in the table) as repeated is that there is another one with the same exact PKN_EVENTNAME and RECEIVEDDATE only differs from the previous one by a tiny amount of time (let's say 10 seconds for example -up or down-).

我首先要做的是创建一个Oracle sql语句，该语句将按PKN_EVENTNAME将所有警报分组，在每个组中将重复的警报分开(以供以后删除).

What I'm trying to do first is to create an Oracle sql statement that will group all alarms by PKN_EVENTNAME separating in each group the ones that are repeated (for later deletion).

我想我正在路上，但是我被卡住了.

I think I'm on the way, but I'm stuck.

¿有任何帮助吗?

到目前为止，我的SQL:

My sql so far:

select t1.ID, t1.PKN_EVENTNAME, t1.RECEIVEDDATE 
from PARQUIMETERS_ALARMS t1 
where 
  exists
     (select 'x' 
      from   PARQUIMETERS_ALARMS t2 
      where  t1.id <> t2.id and                                              -- Not the same row
             trunc(t2.RECEIVEDDATE) = trunc(t1.RECEIVEDDATE)                 -- Same date
             and abs(t1.RECEIVEDDATE - t2.RECEIVEDDATE) * 24 * 60 * 60 < 10)  -- < 10 sec

通过@Tejash更正，我在Visual Studio Oracle SQL浏览器中看到了不同的结果，但是我无法理解它们.我不清楚结果是否已经是要删除的记录(重复的警报)还是什么.

With @Tejash corrections I see different results in Visual Studio Oracle SQL browser, but I'm not being able to understand them. I don't see clear if results are already records to be deleted (repeated alarms) or what.

推荐答案

您可以利用解析函数的 range 子句:

You can utilize range clause of analytic functions:

with dups as (
  select t1.*
       , row_number() over (
           partition by PKN_EVENTNAME, RECEIVEDDATE
           order 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_EVENTNAME
           order by nodups.RECEIVEDDATE
           range between interval '10' second preceding and current row
         ) as cnt
  from nodups
)
select * from t where cnt = 1

(已更新:注释中显示的OP后，添加了CTE dups 和 nodups ，其中有重复的元组(PKN_EVENTNAME，RECEIVEDDATE).)

(UPDATED: CTEs dups and nodups were added after OP shown in comment there are duplicate tuples (PKN_EVENTNAME, RECEIVEDDATE).)

说明:清除通过 nodups CTE传递的数据后， where 条件仅过滤过去10 s中仅有一行的行(显然是当前行).

Explanation: after cleaning data passing through nodups CTE, the where condition filters only rows for which there is only single row in last 10 s (which is obviously the current row).

这篇关于Oracle SQL:筛选仅相差很小时间的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！