Oracle 分析函式 - 如何獲得最高 RANKing 行？

測試數據：

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

請參閱dbfiddle。

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 - 前面閱讀1。將 NULL 視為 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}？完全不確定是否需要 RANK()（對於最終查詢）…

我看不到諸如避免之類的要求的意義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
;

引用自：https://dba.stackexchange.com/questions/199118