【数据库】LeetCode的SQL题目：广告效果

1.题目

一家公司正在运营这些广告并想计算每条广告的效果。

广告效果用点击通过率（Click-Through Rate：CTR）来衡量，公式如下:

写一条SQL语句来查询每一条广告的 ctr ，

ctr 要保留两位小数。结果需要按 ctr 降序、按 ad_id 升序 进行排序。

查询结果格式, 如下例所示：

对于 ad_id = 1, ctr = (2/(2+1)) * 100 = 66.67
对于 ad_id = 2, ctr = (1/(1+2)) * 100 = 33.33
对于 ad_id = 3, ctr = (1/(1+1)) * 100 = 50.00
对于 ad_id = 5, ctr = 0.00, 注意 ad_id = 5 没有被点击 (Clicked) 或查看 (Viewed) 过
注意我们不关心 action 为 Ingnored 的广告
结果按 ctr（降序），ad_id（升序）排序

 

2.思路和答案

根据公式，当点击量和显示量为0时，点击通过率 CTR=0，否则 CTR = 总点击量 / （总点击量 + 总显示量）。

所以这里SQL公式为：SUM(action='Clicked')/(SUM(action='Clicked' OR action='Viewed')

保留两位小数用到 ROUND() 方法，用法为：ROUND("字段名",小数位)

然后根据 ad_id 分组统计。

SELECT ad_id,IFNULL(ROUND(100*SUM(action='Clicked')/(SUM(action='Clicked' OR action='Viewed')),2),0) AS ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id ASC;

 

3.结果