如何使用Oracle SQL向rank()添加移动窗口

我需要在移动时间窗口上对值进行排名.我从托尼·哈斯勒的一篇博文中得到了一些指导
https://tonyhasler.wordpress.com/2012/10/24/model-clause-use-cases/#comment-5116但是将窗口子句添加到非窗口函数(如中位数)的解决方案不适用于作为分析非窗口函数的rank()或percent_rank()函数.

使用median()函数的工作示例：

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,3 from dual union all
    select 'b' sector,27 from dual union all
    select 'b' sector,9 from dual union all
    select 'b' sector,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
;

如果我们用rank()替换中位数,则该示例不起作用,如下所示：

with a as (
    select 'a' sector,0 mov_rank)
rules 
(
    mov_rank[ANY] = rank() over (order by v)[dt between CV()-3 and CV()]
)
order by sector,dt
;

我将不胜感激任何帮助.

谢谢.

解决方法

这可能有点“过时”,但您可以使用自联接而不是分析或模型来获得相同的结果,如：

with a as (
    select 'a' sector,81 from dual 
)
select
    a.sector,a.dt,a.v,count(case when self.v < a.v 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.v
order by 
    a.sector,a.v;

如何使用Oracle SQL向rank()添加移动窗口

解决方法

猜你在找的Oracle相关文章