问题描述
有人可以告诉我如何获得结果如下。
Can someone please tell how can I get the results as below.
使用dense_rank函数，其中rank< = 2将给我前2个提供。
Using dense_rank function where rank <=2 will give me top 2 offers.
我也希望得到'total_offer'，它应该是'offer1'和'offer2'的总和。当没有offer2（例如：金牛座）时，'offer'应该是'offer1'，并且'offer2'为'null'
I am also looking to get 'total_offer' which should be sum of 'offer1' and 'offer2'. when there is no offer2 ( eg:taurus) 'total offer' should be 'offer1' and 'null' for 'offer2'
输入：
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
所需输出：
| 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 |
感谢
PS：
以下链接告诉我，需要执行dense_rank才能获得前2个优惠。
（）
The link below tells me that dense_rank need to be performed to get top 2 offers.(Using a pl-sql procedure or cursor to select top 3 rank)
推荐答案
您最好使用 ROW_NUMBER 代替 DENSE_RANK （可能会返回两行以上）以及 LEAD 第二最高价值
You better use ROW_NUMBER instead of DENSE_RANK (which might return more than two rows) plus a LEAD to find the 2nd highest value
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
这篇关于前2名优惠与所有优惠的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！