前言
练习sql语句,所有题目来自于力扣(https://leetcode.cn/problemset/database/)的免费数据库练习题。
今日题目:
1322.广告效果
表:Person
列名 | 类型 |
---|---|
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)来衡量,公式如下:
编写解决方案找出每一条广告的 ctr ,ctr 要 保留两位小数 。
返回结果需要按 ctr 降序、按 ad_id 升序 进行排序。
我那不值一提的想法:
- 首先梳理表内容,题干一共给了一张广告表,记录了广告id,用户id,用户行为。
- 其次分析需求,需要找到每条广告的点击率,点击率的计算公式是 所有广告点击数量/(所有广告点击数量+所有广告浏览数量),那么这道题跟着这个公式求解就可以了。
- 这道题首先要去掉所有忽略广告的行为,然后进行公式求解
with ctr as (
select ad_id,round(sum(if(action = "Clicked",1,0))*100/count(ad_id),2) as res
from Ads
where action != "Ignored"
group by ad_id
)
查找出所有的ad_id,并左连接上一个临时表
select distinct a.ad_id,ifnull(c.res,0) as ctr
from Ads a
left join ctr c
on a.ad_id = c.ad_id
order by ctr desc,ad_id
结果:
总结:
能运行就行。