代码之家 › 专栏 › 技术社区 › Tomas Greif

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

oracle12c oracle sql

Tomas Greif · 技术社区 · 8 年前

我想计算每一个可能的距离到最大值的距离。例如:

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处。

样本数据:

--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

3 回复 | 直到 8 年前

mathguy 8 年前

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
;

Ponder Stibbons 8 年前

你们两个都可以 max() min() 结合 case when :

select t.*, 
       min(case when val = mv then dst end) over (partition by mv order by dst) v1,
       max(case when val = mv then dst end) over (partition by mv order by dst) v2
  from (select t.*, max(val) over (order by dst) mv from tmp_maxval t) t 
  order by dst

结果:

       DST        VAL         MV         V1         V2
---------- ---------- ---------- ---------- ----------
         1          3          3          1          1
         2          2          3          1          1
         3          1          3          1          1
         4          2          3          1          1
         5          4          4          5          5
         6          2          4          5          5
         7          2          4          5          5
         8          5          5          8          8
         9          5          5          8          9
        10          1          5          8          9

解释逻辑和文字 第一次发生 最小() ,但示例中的第三行建议 ;-) 在您提供的数据中,您可以观察第9-10行中的差异。选择你想要的。

-1

Gordon Linoff 8 年前

select mv.*, max(mv.value) over (order by mv.distance) as max_value
from ap_risk.tmp_maxval mv;

我想这回答了你的问题。如果您想要距离本身:

select mv.*,
       min(case when max_value = value then distance end) over (order by distance) as first_distance_at_max_value
from (select mv.*, max(mv.value) over (order by mv.distance) as max_value
      from ap_risk.tmp_maxval mv
     ) mv;