今日真题
题目介绍： 广告效果 ads-performance
难度简单8收藏分享切换为英文关注反馈
SQL架构
表:
代码语言：javascript复制Ads --------------- ---------
| Column Name | Type |
--------------- ---------
| ad_id | int |
| user_id | int |
| action | enum |
--------------- ---------
(ad_id, user_id) 是该表的主键
该表的每一行包含一条广告的 ID(ad_id)，用户的 ID(user_id) 和用户对广告采取的行为 (action)
action 列是一个枚举类型 ('Clicked', 'Viewed', 'Ignored') 。一家公司正在运营这些广告并想计算每条广告的效果。
广告效果用点击通过率（Click-Through Rate：CTR）来衡量，公式如下:
写一条SQL语句来查询每一条广告的
代码语言：javascript复制ctr，
代码语言：javascript复制ctr要保留两位小数。结果需要按
代码语言：javascript复制ctr降序、按
代码语言：javascript复制ad_id升序 进行排序。
查询结果示例如下：
代码语言：javascript复制Ads 表:
------- --------- ---------
| ad_id | user_id | action |
------- --------- ---------
| 1 | 1 | Clicked |
| 2 | 2 | Clicked |
| 3 | 3 | Viewed |
| 5 | 5 | Ignored |
| 1 | 7 | Ignored |
| 2 | 7 | Viewed |
| 3 | 5 | Clicked |
| 1 | 4 | Viewed |
| 2 | 11 | Viewed |
| 1 | 2 | Clicked |
------- --------- ---------
结果表:
------- -------
| ad_id | ctr |
------- -------
| 1 | 66.67 |
| 3 | 50.00 |
| 2 | 33.33 |
| 5 | 0.00 |
------- -------
对于 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（升序）排序精简
代码语言：javascript复制sql
SELECT ad_id,
ROUND(IFNULL(SUM(action = 'Clicked') /
(SUM(action = 'Clicked') SUM(action = 'Viewed')) * 100, 0), 2) AS ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id ASC;笨方法
代码语言：javascript复制sql
select a.ad_id,ifnull(ctr,0) ctr
from Ads a
left join (
select ad_id,round(sum(if(action='Clicked',1,0))/count(*)*100,2) ctr
from Ads
where action !='Ignored'
group by ad_id
)t1
on a.ad_id= t1.ad_id
group by ad_id,ctr
order by ctr desc,ad_id- 已经有灵感了？在评论区写下你的思路吧！
