sum+if

 

下面是3种答案，第三个是我的，然后前两个是评论区的， 1322. 广告效果 - 力扣（LeetCode）

还是sum+if用的太少了，可以注意多用下。第一个答案应该是最好的了。不等于的符号：<>

select ad_id,
ifnull(round(sum(action="Clicked")/sum(action<>"Ignored")*100,2),0.00) as ctr
from ads
group by ad_id
order by ctr desc,ad_id 

SELECT ad_id, IFNULL(ROUND(sum(if(action = 'Clicked',1,0))/(sum(if(action = 'Clicked',1,0))+sum(if(action = 'Viewed',1,0)))*100,2),0) ctr FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id;

 

select ad_id,round(ifnull(sum(case when action="Clicked" then 1 else 0 end)/sum(case when action in ("Clicked","Viewed") then 1 else 0 end),0)*100,2) ctr
from  Ads 
group by ad_id
order by ctr desc,ad_id