Oracle分析函数-如何获得最高的RANKing行？

Question

测试数据：

create table test (
  grp varchar2(16)
, mbr varchar2(16)
, reading1 number
, reading2 number
);

-- group A: 3 members, 1 duplicate set
-- group B: 2 members, 1 duplicate, one reading NULL
-- group C: 2 members, no repeats, no NULLs 
begin
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'x', '1.0', '2.0' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'y', '1.1', '2.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'z', '1.2', '2.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'x', '1.0', '2.0' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'y', '1.1', '2.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'z', '1.2', '2.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'B', 'y', '20.2', null ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'B', 'x', '20.4', '40.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'B', 'y', '20.2', null ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'B', 'x', '20.4', '40.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'C', 'r', '100.1', '200.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'C', 's', '100.2', '200.4' ) ;
end;
/

见[医]小提琴。

select * from test;
GRP  MBR  READING1  READING2  
A    x    1         2         
A    y    1.1       2.2       
A    z    1.2       2.4       
A    x    1         2         
A    y    1.1       2.2       
A    z    1.2       2.4       
B    y    20.2      NULL      
B    x    20.4      40.4      
B    y    20.2      NULL      
B    x    20.4      40.4      
C    r    100.1     200.2     
C    s    100.2     200.4  

问题：

编写一个执行以下所有操作的查询：

{1}查找唯一的行。

{2}查找每个组(grp)的最后2个成员(mbr)。假设:当成员按字母顺序排列时，最后一个成员是最后一个字母(例如如果我们有'x'，'y'，'z'，最后一个字母是'z')。

{3}执行以下计算:当行被分组时(根据它们的grp字母)，对于包含最后一个字母的每一行: reading1 -前面的reading2 (即包含字母'y‘的行的reading2 )和reading2 -前面的reading1。把NULLs当作0。

使用我们的样本/测试数据：

-- {1}
GRP  MBR  R1     R2     
A    x    1      2      
A    y    1.1    2.2    
A    z    1.2    2.4    
B    x    20.4   40.4   
B    y    20.2   0      
C    r    100.1  200.2  
C    s    100.2  200.4 

-- {2}
GRP  MBR  RESULT1  RESULT2  RANK_  
A    x    1        2        1      
A    y    -0.9     1.2      2      
A    z    -1       1.3      3      
B    x    18       39.2     1      
B    y    -20.2    -20.4    2      
C    r    100.1    180      1      
C    s    -100     100.3    2 

-- {3} required/final result 
grp  result1  result2
A      -1.0      1.3    -- (result1: 1.2-2.2)     (result2: 2.4-1.1)
B     -20.2    -20.4    -- (result1: 20.2-40.4)   (result2: 0-20.4)
C    -100.0    100.3    -- (result1: 100.2-200.2) (result2: 200.4-100.3)

现有代码：

此查询返回结果集{2}。

-- {2}
  select
    grp
  , mbr
  , r1 - lag( r2, 1, 0 ) over ( order by grp ) as result1
  , r2 - lag( r1, 1, 0 ) over ( order by grp ) as result2
  , rank() over ( partition by grp order by mbr ) as rank_
  from
  (
    select distinct
      grp
    , mbr
    , nvl( reading1, 0 ) r1
    , nvl( reading2, 0 ) r2
    from test
    order by grp, mbr
  ) ;

问:如果不使用硬编码值(例如WHERE子句中的rank_ =2)，如何获得结果集{3}？不确定(对于最终查询)是否需要秩().

Answer 1

我不明白避免WHERE rank_ =这样的需求的意义，但是在这里，没有RANK()，或者硬编码一个常量(仍然，硬编码是通过使用FIRST_VALUE完成的)：

select distinct grp,
  first_value(result1) over (partition by grp order by mbr desc) as result1,
  first_value(result2) over (partition by grp order by mbr desc) as result2
from (
select
  grp, mbr, 
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
);

GRP                 RESULT1    RESULT2
---------------- ---------- ----------
A                        -1        1.3
B                     -20.2      -20.4
C                      -100      100.3

当这在我看来更容易读懂的时候：

select grp, result1, result2 from (
select
  grp,
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2,
  rank() over (partition by grp order by mbr desc) as rank_
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
) where rank_ = 1
;