5

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?

2
  • Is the range of possible values for bar always known prior to executing the statement or is it dependant on the statement? Your initial data suggests it's always in (0,1,2,3). Mar 12, 2012 at 7:02
  • It varies - the example is much simplified from my real world problem :-) Mar 12, 2012 at 7:03

4 Answers 4

4
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)]
      );
0
3

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

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.

3
  • This doesn't take account of cases where prev_foo is further back than 1 step - compare your results with mine or René's after increasing the size of the test data (eg connect by level<15) Mar 13, 2012 at 13:49
  • I didn't test it, and in none of the test data has prev_foo further back than one step.
    – Adam Musch
    Mar 13, 2012 at 23:25
  • Yes, that's my bad, sorry. Thanks for contributing. Mar 14, 2012 at 7:20
1

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;  
1
  • It was fun to take a crack at. Mar 13, 2012 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.