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

发布时间：2020-12-12 13:15:52 所属栏目：百科来源：网络整理

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

我需要在移动时间窗口上对值进行排名.我从托尼·哈斯勒的一篇博文中得到了一些指导
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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!