sql - stored - Las 2 principales ofertas con la suma de todas las ofertas

return cursor oracle

Question

¿Alguien puede decir cómo puedo obtener los resultados de la siguiente manera?

El uso de la función dense_rank donde rango <= 2 me dará las mejores 2 ofertas.

También estoy buscando obtener ''total_offer'', que debe ser la suma de ''offer1'' y ''offer2''. cuando no hay oferta2 (p. ej .: taurus), la ''oferta total'' debe ser ''oferta1'' y ''nulo'' para ''oferta2''

Entrada:

customer    make    zipcode offer notes  
mark        focus   101     250   cash  
mark        focus   101     2500  appreciation cash  
mark        focus   101     1000  cash  
mark        focus   101     1500  cash offer  

henry       520i    21405   500  cash offer  
henry       520i    21405   100  cash  
henry       520i    21405   750  appreciation cash  
henry       520i    21405   100  cash  

mark        taurus  48360   250    appreciation cash  

mark        mustang 730     500  cash  
mark        mustang 730     1000  Cash offer  
mark        mustang 730     1250  appreciation cash  

Salida deseada:

| CUSTOMER | MAKE    | ZIPCODE | TOP_OFFER1 | notes1 | TOP_OFFER2 | notes2 | Total_offer |  
| henry | 520i | 21405 | 750  | appreciation cash | 500 | cash offer | 1250    
| mark  | focus   | 101  2500 | appreciation cash | 1500 | cash offer | 4000  
| mark | mustang | 730 | 1250 | appreciation cash | 1000 | cash offer | 2250    
| mark  | taurus  | 48360 | 250 | appreciation cash | NULL       | 250 |   

Gracias

PD:

El siguiente enlace me dice que debe realizarse dense_rank para obtener las 2 mejores ofertas. ( Usando un procedimiento pl-sql o cursor para seleccionar los 3 primeros puestos )

Answer 1

Es mejor utilizar ROW_NUMBER lugar de DENSE_RANK (que puede devolver más de dos filas) más un LEAD para encontrar el segundo valor más alto

select customer, make, zipcode,
    TOP_OFFER1, 
    TOP_OFFER2, 
    TOP_OFFER1 + coalesce(TOP_OFFER2,0) as Total_offer
from
 ( 
   select customer, make, zipcode, 
      offer as TOP_OFFER1, -- max offer
      lead(offer) over (partition by customer, make, zipped
                        order by offer desc) as TOP_OFFER2, -- 2nd highest offer
      row_number() over (partition by customer, make, zipped
                        order by offer desc) as rn
  from tab
 ) dt
where rn = 1 -- only the row with the highest offer

Answer 2

Prueba este código ...

WITH subqueryfactoring AS
  (SELECT customer,
    make ,
    zipcode ,
    offer,
    lead(offer) over (partition BY customer, make , zipcode order by offer DESC) SecondLeadValue,
    row_number() over (partition BY customer, make , zipcode order by offer DESC ) rownumber
  FROM abc1
  )
SELECT customer,
  make ,
  zipcode,
  offer "Top Offer1 ",
  SecondLeadValue "Top offer 2",
  offer + COALESCE(SecondLeadValue,0) "Total Offer"
FROM subqueryfactoring
WHERE rownumber<2;

Answer 3

 with x as 
 (select row_number() over(partition by customer,make order by offer desc) rn,
  customer, make, zipcode, offer from tablename)
 , y as (select customer, make, zipcode, offer from x where rn <=2)
 , z as (select customer, make, zipcode, 
         case when rn = 1 then offer else 0 end as offer_1, 
         case when rn = 2 then offer else 0 end as offer_2 
         from y)
  select customer, make, zipcode, offer_1, offer_2, offer_1+offer_2 total_offer
  from z

Esto hace uso de cte''s recursivos para realizar su tarea.