Getting the value of 'foo' for the closest previous row with higher 'bar'

Question

From this initial data:

select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9;
/*
FOO                    BAR                   
---------------------- ----------------------
1                      3                     
2                      2                     
3                      3                     
4                      3                     
5                      2                     
6                      0                     
7                      3                     
8                      2                      
*/

I want to get the value of foo, bar, prev_foo for each row, where prev_foo is the value of foo from the closest 'previous' row (defined by ordering on foo which is unique) such that bar in that row is greater than bar in the current row. In other words, I want this result:

/*
FOO                    BAR                    PREV_FOO                 
---------------------- ---------------------- ----------------------
1                      3                                           
2                      2                      1                   
3                      3                                          
4                      3                                           
5                      2                      4                     
6                      0                      5                    
7                      3                                           
8                      2                      7                     
*/

How do I achieve this?

Answer 1

with data as (
   select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9
)
select
  foo,
  bar,
  prev_foo
from
  data
model 
      dimension by (foo, bar)
      measures     (cast (null as number) as prev_foo,
                    cast (null as number) as store_foo
                   )
      rules        (
        store_foo[any, any] = cv(foo),
        prev_foo [any, any] = max(store_foo) [foo < cv(foo), bar > cv(bar)]
      );

Answer 2

Here is one way, not using analytics so pretty inefficient:

with w as (select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9)
select foo, bar, (select max(foo) from w where foo<ww.foo and bar>ww.bar) from w ww;

Answer 3

Untested:

with W as 
(select level as foo, mod(ora_hash(level),4) as bar 
   from dual 
connect by level<9)
select foo, bar, 
       case when prev_bar > bar then prev_foo else null end as prev_bar
  from (select foo, bar, 
               lag(foo) over (order by foo) as prev_foo,
               lag(bar) over (order by foo) as prev_bar
          from w)
 order by foo;

The with clause is the original query.

The inner most select uses analytics to get the previous (lag) value of foo and bar.

The outer query uses a case statement to implement your business rule.

Answer 4

Here is another answer that works for nine rows, but not for more. I'm posting it in case it sparks an idea.

WITH w AS (SELECT level as foo, mod(ora_hash(level),4) as bar FROM dual CONNECT BY Level<9)
SELECT foo, bar, Case When Highest > Bar Then 
   MAX(foo) OVER (PARTITION BY Highest 
     ORDER BY foo RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
   Else NULL End prev_foo
FROM 
   (SELECT foo, bar, max(bar) OVER (
      ORDER BY foo RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Highest FROM w)
ORDER BY foo;