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.
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-).
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. What can I try next?
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
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.
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
(UPDATED: CTEs dups
and nodups
were added after OP shown in comment there are duplicate tuples (PKN_EVENTNAME, RECEIVEDDATE)
.)
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).