时间戳

首页 > 解决方案 > 每个距离与最大值的距离

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

问题描述

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

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

标签： sqloracleoracle12c

解决方案

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
;

推荐阅读