sql - 每个距离与最大值的距离

Question

我想计算每个可能距离的最大值的距离。举个例子:

Row  Distance Value
1     1        2     --> 1 (Distance from Row 1)
2     2        3     --> 2 (Distance from Row 2)
3     3        3     --> 2 (Distance from Row 2)
4     4        1     --> 2 (Distance from Row 2)
5     5        5     --> 5 (Distance from Row 5)
6     6        1     --> 5 (Distance from Row 5)

说明:第 6 行的值为 5，因为第 1 行到第 6 行之间的最大值第一次出现在距离 5 处。

我曾尝试使用一些 Windows 函数，但无法弄清楚如何将它们组合在一起。

样本数据:

--drop table tmp_maxval;
create table tmp_maxval (dst number, val number);
insert into tmp_maxval values(1, 3);
insert into tmp_maxval values(2, 2);
insert into tmp_maxval values(3, 1);
insert into tmp_maxval values(4, 2);
insert into tmp_maxval values(5, 4);
insert into tmp_maxval values(6, 2);
insert into tmp_maxval values(7, 2);
insert into tmp_maxval values(8, 5);
insert into tmp_maxval values(9, 5);
insert into tmp_maxval values(10,1);
commit;

我认为可以解决这个问题的函数:

select t.*, 
       max(val) over(order by dst), 
       case when val >= max(val) over(order by dst) then 1 else 0 end ,        
       case when row_number() over(partition by val order by dst) = 1 then 1 else 0 end as first_occurence
from 
  ap_risk.tmp_maxval t

Answer 1

select dst, val, 
       max(case when flag is null then dst end) over (order by dst) 
         as first_occurrence
from   (
         select dst, val, 
                case when val <= max(val) over (order by dst 
                             rows between unbounded preceding and 1 preceding)
                then 1 end as flag
         from tmp_maxval
       )
order by dst
;

       DST        VAL FIRST_OCCURRENCE
---------- ---------- ----------------
         1          3                1
         2          2                1
         3          1                1
         4          2                1
         5          4                5
         6          2                5
         7          2                5
         8          5                8
         9          5                8
        10          1                8

或者，如果您使用的是 Oracle 12.1 或更高版本， MATCH_RECOGNIZE可以快速完成这项任务:

select dst, val, first_occurrence
from   tmp_maxval t
match_recognize(
  order by dst
  measures a.dst as first_occurrence
  all rows per match
  pattern  (a x*)
  define   x as val <= a.val
)
order by dst
;