Início / user-123798

stefan's questions

Martin Hope
stefan
Asked: 2018-03-02 01:57:48 +0800 CST
  • 4

Dados de teste:

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

Veja 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

Problema:

Escreva uma consulta que faça o seguinte:

{1} Encontre linhas exclusivas.

{2} Encontre os últimos 2 membros (mbr) de cada grupo (grp). Suposição: quando os membros são colocados em ordem alfabética, o último membro é aquele com a última letra (por exemplo, se temos 'x','y','z', a última letra é 'z'). Não codifique a letra na consulta.

{3} Faça o seguinte cálculo: quando as linhas são agrupadas (de acordo com sua letra grp), para cada linha contendo a última letra: leitura1 - leitura anterior2 (ou seja, leitura2 da linha contendo a letra 'y') e leitura2 - leitura anterior leitura1. Trate NULLs como 0.

Usando nossos dados de amostra/teste:

-- {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)

Código existente:

Esta consulta retorna o conjunto de resultados {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
  ) ;

Pergunta: Como podemos chegar ao conjunto de resultados {3}, sem usar valores codificados (como rank_ = 2 em uma cláusula WHERE)? Não tenho certeza se RANK() é necessário (para a consulta final) ...

oracle oracle-12c