主页 / dba / 问题 / 199118
Accepted
stefan
Asked: 2018-03-02 01:57:48 +0800 CST

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

  • 772

测试数据：

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() （对于最终查询）...

oracle oracle-12c

1 个回答

  1. Best Answer

    我看不出诸如 avoiding 之类的要求的意义所在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
;
    • 3

相关问题