How to add a moving window to rank () using Oracle SQL

Question

I need to rank the values ​​during a time window. I got some direction from Tony Hasler's blogpost at https://tonyhasler.wordpress.com/2012/10/24/model-clause-use-cases/#comment-5116 , but the decision was to add a windowing clause to non-window functions, such as the median, does not work with the rank () or percent_rank () functions, which are windowless analytic functions.

Working example with median () function:

with a as ( select 'a' sector, trunc(sysdate) dt, 64 v from dual union all select 'a' sector, trunc(sysdate)-1 dt, 2 from dual union all select 'a' sector, trunc(sysdate)-2 dt, 4 from dual union all select 'a' sector, trunc(sysdate)-3 dt, 128 from dual union all select 'a' sector, trunc(sysdate)-4 dt, 8 from dual union all select 'a' sector, trunc(sysdate)-5 dt, 16 from dual union all select 'a' sector, trunc(sysdate)-6 dt, 32 from dual union all select 'a' sector, trunc(sysdate)-7 dt, 256 from dual union all select 'a' sector, trunc(sysdate)-8 dt, 1 v from dual union all select 'a' sector, trunc(sysdate)-9 dt, 512 from dual union all select 'b' sector, trunc(sysdate) dt, 3 from dual union all select 'b' sector, trunc(sysdate)-1 dt, 27 from dual union all select 'b' sector, trunc(sysdate)-2 dt, 9 from dual union all select 'b' sector, trunc(sysdate)-3 dt, 81 from dual ) select * from a model partition by (sector) dimension by (dt) measures (v, 0 mov_rank) rules ( mov_rank[ANY] = median(v)[dt between CV()-3 and CV()] ) order by sector, dt ; 

The example does not work if we replace the median rank (), as in:

 with a as ( select 'a' sector, trunc(sysdate) dt, 64 v from dual union all select 'a' sector, trunc(sysdate)-1 dt, 2 from dual union all select 'a' sector, trunc(sysdate)-2 dt, 4 from dual union all select 'a' sector, trunc(sysdate)-3 dt, 128 from dual union all select 'a' sector, trunc(sysdate)-4 dt, 8 from dual union all select 'a' sector, trunc(sysdate)-5 dt, 16 from dual union all select 'a' sector, trunc(sysdate)-6 dt, 32 from dual union all select 'a' sector, trunc(sysdate)-7 dt, 256 from dual union all select 'a' sector, trunc(sysdate)-8 dt, 1 v from dual union all select 'a' sector, trunc(sysdate)-9 dt, 512 from dual union all select 'b' sector, trunc(sysdate) dt, 3 from dual union all select 'b' sector, trunc(sysdate)-1 dt, 27 from dual union all select 'b' sector, trunc(sysdate)-2 dt, 9 from dual union all select 'b' sector, trunc(sysdate)-3 dt, 81 from dual ) select * from a model partition by (sector) dimension by (dt) measures (v, 0 mov_rank) rules ( mov_rank[ANY] = rank() over (order by v)[dt between CV()-3 and CV()] ) order by sector, dt ; 

I would appreciate any help.

Thanks.

Answer 1

It might be a little “old fashioned,” but you could get an equivalent result using self-connectivity instead of analytics or model , as in something like:

 with a as ( select 'a' sector, trunc(sysdate) dt, 64 v from dual union all select 'a' sector, trunc(sysdate)-1 dt, 2 from dual union all select 'a' sector, trunc(sysdate)-2 dt, 4 from dual union all select 'a' sector, trunc(sysdate)-3 dt, 128 from dual union all select 'a' sector, trunc(sysdate)-4 dt, 8 from dual union all select 'a' sector, trunc(sysdate)-5 dt, 16 from dual union all select 'a' sector, trunc(sysdate)-6 dt, 32 from dual union all select 'a' sector, trunc(sysdate)-7 dt, 256 from dual union all select 'a' sector, trunc(sysdate)-8 dt, 1 v from dual union all select 'a' sector, trunc(sysdate)-9 dt, 512 from dual union all select 'b' sector, trunc(sysdate) dt, 3 from dual union all select 'b' sector, trunc(sysdate)-1 dt, 27 from dual union all select 'b' sector, trunc(sysdate)-2 dt, 9 from dual union all select 'b' sector, trunc(sysdate)-3 dt, 81 from dual ) select a.sector, a.dt, av, count(case when self.v < av then self.v end) + 1 mov_rank from a, a self where self.sector = a.sector and self.dt between a.dt - 3 and a.dt + 3 group by a.sector, a.dt, av order by a.sector, a.dt, av;