前2名优惠与所有优惠的总和 [英] Top 2 offers with sum of all offers

查看:342
本文介绍了前2名优惠与所有优惠的总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

有人可以告诉我如何获得结果如下。

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个优惠。
使用pl -sql procedure or cursor to select top 3 rank

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名优惠与所有优惠的总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆