如何在windowing_clause依赖于另一个变量的情况下按一个变量获取SQL oracle nth_value中的顺序？

Question

我想得到最大，第二和第三列的值在几个窗口长度的基础上的时间。

例如，我有这样的数据

X   | DATE    |   Y  
--------------------
N1  | 2019-01 |  100
N1  | 2019-02 | (null)
N1  | 2019-03 |   30
N1  | 2019-04 |   40
N1  | 2019-05 |   70
N2  | 2019-01 |   80
N2  | 2019-02 |   60
N2  | 2019-03 |   40
N2  | 2019-04 |   50
N2  | 2019-05 |   30

我希望得到过去三个月内Y的最大值和第二最大值，如下所示：

X   | DATE    | MAX(Y) | SEC(Y)  
-------------------------------
N1  | 2019-01 |  100   | (null)
N1  | 2019-02 |  100   | (null)
N1  | 2019-03 |  100   |   30
N1  | 2019-04 |   40   |   30
N1  | 2019-05 |   70   |   40
N2  | 2019-01 |   80   | (null) 
N2  | 2019-02 |   80   |   60
N2  | 2019-03 |   80   |   60
N2  | 2019-04 |   60   |   50
N2  | 2019-05 |   50   |   40

我能做到的最大限度

select distinct
X, DATE, 
max(y) over (partition by X, DATE order by DATE RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW)

如果数据是按日期值排序的，我应该怎么做才能获得第二个(或另一个nth_value)？在order by子句后添加Y无效...或者，也许我应该重新组织输入数据？

Answer 1

一种方法是两级窗口函数：

select t.*,
       max(case when y < max_3mon then y end) over
           (partition by X order by dte RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW) as max2_3mon
from (select t.*, 
              max(y) over (partition by X order by dte RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW) as max_3mon
      from t 
     ) t
order by x, dte;

Here是一个db<>fiddle。

这基本上计算子查询中的最大值。然后，它计算不等于该值的最大值。请注意，我更改了partition by，因此查询将实际执行您想要的操作。

编辑：

上面有一些如意算盘--即计算出的3个月平均值是恒定的。Oracle让这变得有点棘手，但横向联接解决了这个问题：

select t.*, max(t.y) over (partition by t.x order by t.dte RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW) as max_3mon,
       max2.y as max2_3mon
from t left join lateral
     (select distinct t2.y
      from t t2
      where t2.x = t.x and
            t2.dte >= t.dte - interval '2' month and
            t2.dte <= t.dte
      order by t2.y
      offset 1 row fetch first 1 row only
     ) max2
     on 1=1;

db<>fiddle也有这个正确的解决方案。

Answer 2

这里有一种方法可以做到这一点。首先，我创建了示例表(我又添加了一个X来测试特殊情况:例如，当有两个或更多的值并列为最高值，或者并列为第二高值时，等等)

在查询中，MATCH_RECOGNIZE部件为输入表中的每一行生成“前面的行”。ROW_NUMBER()计算给出排名，最后的透视操作将列中的前三个值排成一列。(我不相信有必要转向，但这是你的问题-你知道你的业务需求是什么。在任何情况下，旋转只是锦上添花，它与问题的其余部分或解决方案无关。)

这可以很容易地推广到"top-n“-除了最后一部分(旋转)，它也可以很容易地推广到"n”，但"n“需要硬编码，它不能是用户输入(绑定变量)。如果你真的需要这样做，对于变量"n"，你将需要动态SQL (最好不要去管它)。

示例数据：

create table sample_data (x, dt, y) as
    select 'N1', date '2019-01-13',  100 from dual union all
    select 'N1', date '2019-02-11', null from dual union all
    select 'N1', date '2019-03-01',   30 from dual union all
    select 'N1', date '2019-04-15',   40 from dual union all
    select 'N1', date '2019-05-31',   70 from dual union all
    select 'N2', date '2019-01-01',   80 from dual union all
    select 'N2', date '2019-02-28',   60 from dual union all
    select 'N2', date '2019-03-16',   40 from dual union all
    select 'N2', date '2019-04-15',   50 from dual union all
    select 'N2', date '2019-05-23',   30 from dual union all
    select 'N5', date '2019-03-20',  120 from dual union all
    select 'N5', date '2019-03-24',   85 from dual union all
    select 'N5', date '2019-04-15',  120 from dual union all
    select 'N5', date '2019-04-22',  140 from dual
;

查询：

with
  mr (x, ref_dt, y, grp, rn) as (
    select x, ref_dt, y, grp, 
           row_number() over (partition by x, grp 
                              order by y desc nulls last)
    from   sample_data
    match_recognize(
      partition by x
      order     by dt desc
      measures  a.dt as ref_dt, match_number() as grp
      all rows per match
      after match skip to next row
      pattern   ( a b* )
      define    b as dt >= add_months(a.dt, -3)
    )
  )
, top_three (x, ref_dt, y, grp, rn) as (
    select x, ref_dt, y, grp, rn
    from   mr
    where  rn <= 3
  )
select x, ref_dt, first, second, third
from   top_three
pivot  (max(y) for rn in (1 as first, 2 as second, 3 as third)) 
order  by x, ref_dt
;

输出：

X  REF_DT          FIRST     SECOND      THIRD
-- ---------- ---------- ---------- ----------
N1 2019-01-13        100                      
N1 2019-02-11        100                      
N1 2019-03-01        100         30           
N1 2019-04-15         40         30           
N1 2019-05-31         70         40         30
N2 2019-01-01         80                      
N2 2019-02-28         80         60           
N2 2019-03-16         80         60         40
N2 2019-04-15         60         50         40
N2 2019-05-23         60         50         40
N5 2019-03-20        120                      
N5 2019-03-24        120         85           
N5 2019-04-15        120        120         85
N5 2019-04-22        140        120        120