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.