Analytical function range window for max date interval

Question

I am trying to get 10 minute interval data from latest date of each group or partition.

Pseudo code SQL:

Select
count(1) Over( partition by col1, col2, col3 
               Order by Col_Date Desc 
               Range Max(Col_Date) Between  Max(Col_Date) - 10(24*60) ) col_upd
From 
Table_1;

Values out of of this particular range will have need assign number to set for delete.

2014-01-05 01:20:00  -- Max date 
2014-01-05 01:15:13
2014-01-05 01:12:13
2014-01-05 01:07:13  -- 1) these last two rows should be set for
2014-01-05 01:06:13  -- 2) delete or assign same id

Is there any analytical function way to approach this?

score 0 · Answer 1 · answered Jan 08 '15 at 18:14

You haven't given table structures, but if I make up a dummy table like:

create table t42 (id number, grp_id number, dt date);

insert into t42 values (1, 1, timestamp '2014-01-05 01:20:00'); 
insert into t42 values (2, 1, timestamp '2014-01-05 01:15:13');
insert into t42 values (3, 1, timestamp '2014-01-05 01:12:13');
insert into t42 values (4, 1, timestamp '2014-01-05 01:07:13');
insert into t42 values (5, 1, timestamp '2014-01-05 01:06:13');

Then this will give you the age of each row in the group compared to its (analytic) max:

select grp_id, id, dt, max(dt) over (partition by grp_id) - dt as age
from t42
order by id;

    GRP_ID         ID DT                           AGE
---------- ---------- ------------------- ------------
         1          1 2014-01-05 01:20:00            0 
         1          2 2014-01-05 01:15:13 .00332175926 
         1          3 2014-01-05 01:12:13 .00540509259 
         1          4 2014-01-05 01:07:13 .00887731481 
         1          5 2014-01-05 01:06:13 .00957175926

And you can use that as an inner query and filter out records up to 10 minutes old:

select grp_id, id, dt
from (
  select grp_id, id, dt, max(dt) over (partition by grp_id) - dt as age
  from t42
)
where age > (10*60)/(24*60*60)
order by id;

   GRP_ID         ID DT                
---------- ---------- -------------------
         1          4 2014-01-05 01:07:13 
         1          5 2014-01-05 01:06:13

And you can then use those up delete/update as needed. It's not clear from your question if your group/partition is already being calculated from an inner query; if so you can just use that instead of my t42 table. (Changing column names etc., of course).

Analytical function range window for max date interval

1 Answers1