LeetCode-SQL-1322. 广告效果

什么你竟然不会敲代码

于 2020-03-27 16:04:15 发布

阅读量496

点赞数

分类专栏：数据库 LeetCode

本文链接：https://blog.csdn.net/m0_37302219/article/details/105143350

版权

LeetCode 同时被 2 个专栏收录

204 篇文章

订阅专栏

数据库

142 篇文章

订阅专栏

本文通过SQL查询，分析了广告的点击率（CTR），展示了如何从数据库中提取广告互动数据，包括点击、查看和忽略等行为，并计算每条广告的CTR，为广告效果评估提供了数据支持。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Create table If Not Exists Ads (ad_id int, user_id int, action ENUM('Clicked', 'Viewed', 'Ignored'))
Truncate table Ads
insert into Ads (ad_id, user_id, action) values ('1', '1', 'Clicked')
insert into Ads (ad_id, user_id, action) values ('2', '2', 'Clicked')
insert into Ads (ad_id, user_id, action) values ('3', '3', 'Viewed')
insert into Ads (ad_id, user_id, action) values ('5', '5', 'Ignored')
insert into Ads (ad_id, user_id, action) values ('1', '7', 'Ignored')
insert into Ads (ad_id, user_id, action) values ('2', '7', 'Viewed')
insert into Ads (ad_id, user_id, action) values ('3', '5', 'Clicked')
insert into Ads (ad_id, user_id, action) values ('1', '4', 'Viewed')
insert into Ads (ad_id, user_id, action) values ('2', '11', 'Viewed')
insert into Ads (ad_id, user_id, action) values ('1', '2', 'Clicked')

select ad_id,
case when clickNum+viewNum=0 then 0.00
else round(clickNum/(clickNum+viewNum)*100,2) end ctr from
(select sum(if(action='Clicked',1,0)) as clickNum,
sum(if(action='Viewed',1,0)) as viewNum
,ad_id from Ads a group by ad_id) as t
order by ctr desc,ad_id