CODEWITHSUNDEEP
postgresql
Tharun
Tharun

Reputation: 311

Deleting entries with very small time interval

I have a table where it gets entries updated by time for every resource. For simplicity lets say the table has these essential columns

-resourceid
-entrytime

For some reasons the there has been multiple entries on the table for many resourceid (s) within few milliseconds and i want to remove the second entry that has been recorded so.

I queried to find such entries by making a self join on the table to find the entries. 

select b.resourceid,b.entrytime 
from RESOURCE_DATA_TABLE a 
   join RESOURCE_DATA_TABLE b on a.resourceid=b.resourceid 
where b.collectiontime > a.entrytime 
  and b.entrytime < (a.entrytime + interval '4 millisecond');

But how do I delete them on a single shot.

Upvotes: 0

Views: 46

Answers (2)

Rakesh Soni
Rakesh Soni

Reputation: 10887

Try this

Delete 
FROM RESOURCE_DATA_TABLE r
WHERE 
    EXISTS (

      select * 
      from RESOURCE_DATA_TABLE a 
      join RESOURCE_DATA_TABLE b on a.resourceid=b.resourceid 
      where b.collectiontime > a.entrytime 
      and b.entrytime < (a.entrytime + interval '4 millisecond')
      and b.resourceid = r.resourceid
      and b.entrytime = r.entrytime 

)

OR this 

Delete 
FROM RESOURCE_DATA_TABLE r
WHERE 
    EXISTS (
       Select * from 
      (select b.resourceid,b.entrytime 
      from RESOURCE_DATA_TABLE a 
      join RESOURCE_DATA_TABLE b on a.resourceid=b.resourceid 
      where b.collectiontime > a.entrytime 
      and b.entrytime < (a.entrytime + interval '4 millisecond')
      ) temp 
      where temp.resourceid = r.resourceid
      and temp.entrytime = r.entrytime 

)

Upvotes: 0

user330315
user330315

Reputation:

This gets a bit easier if you use window functions to find the rows:

select * 
from (
  select resourceid, entrytime, 
         entrytime - lag(entrytime) over (partition by resourceid order by entrytime) as diff, 
         row_number() over (partition by resourceid order by entrytime) as rn
  from resource_data_table
) t
where diff < interval '4 millisecond';

Now you can use that do delete the rows:

delete from resource_data_table
where (resourceid, entrytime) in (select resourceid, entrytime 
                                  from (
                                    select resourceid, entrytime, 
                                           entrytime - lag(entrytime) over (partition by resourceid order by entrytime) as diff, 
                                           row_number() over (partition by resourceid order by entrytime) as rn
                                    from resource_data_table
                                  ) t
                                  where diff < interval '4 millisecond' 
                                     and rn > 1)

Upvotes: 1

Related Questions