sql count为空时显示0_使用SQL查询广告CTR等

SQL 是作为开发工程师、数据工程师必须要深入掌握的一项技能。我也一直在提升这部分的能力，从入门到精通。除了阅读一些 SQL 经典书籍，刷题也是一个不错的选择。于是，我将做过的题目与解法，汇总到这里，和大家一起学习。

SQL 练习题系列和「体验碎片」系列一样，会不定期进行更新。

问题1：写一条SQL语句来查询每一条广告的 CTR(广告效果点击通过率(Click-Through Rate：CTR))。

解法：ifnull 和 case when

1.每一条广告 CTR 计算公式：CTR = 点击广告的人数/(点击广告的人数+ 浏览广告的人数)2.使用 group by 对 ad_id 进行分组聚合，使用 case when 先把 action为 clicked 的数量统计出来，如果action为'Clicked' 则为 1，否则为 0，在统计 action 不为 ignored 的数量，如果 action 为 'Ignored' 则为 0，否则为1；3.使用 round 保留两位小数4.由于部分广告没有点击量，会出现 null 值，使用 ifnull 公式，如果该广告点击率为 null 值，就赋值为 0；5.使用 order by 对 CTR 降序 ,对 ad_id 升序。

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

问题2：编写一个 SQL 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

思路：

1.查找每个月每个国家的事务数和总金额，需要使用 substr 或者 DATE_FORMAT 将日期转化为月份；2.统计已经批准的事务数，使用 count 和 case when 函数对于不符合 state = 'approved' 的数标记为 null值；3.统计已经批准的金额，使用 sum 和 case when 函数对于符合 state = 'approved' 的数按照该金额计算，对于不符合 state = 'approved' 的数标记为 0。

select       substr(trans_date,1,7) month,     --  DATE_FORMAT(trans_date, '%Y-%m'),     country,     count(id) trans_count,     count(case when state = 'approved' then id else null end ) approved_count,     sum(amount) trans_total_amount,     sum(case when state = 'approved' then amount else 0 end ) approved_total_amountfrom Transactions group by  substr(trans_date,1,7),countryorder by  substr(trans_date,1,7),country

问题3：请编写 SQL 语句来找到当选者的名字

预期结果：

解法：

1.获胜者是 Vote 表中出现最多次的 CandidateId，先做一个临时表，对 CandidateId 分组计数；2.对于计数 count(*) 结果降序排序；3.使用 limit 1 取出第一名；4.使用子查询，或两表连接，返回第一个人的名字。

子查询：in

select Namefrom  Candidate where id in(select   CandidateIdfrom (select CandidateId,count(id)from Votegroup by CandidateIdorder by count(id) desc limit 1) t)

两表连接：join

select Namefrom (select CandidateId from Votegroup by CandidateIdorder by count(id) desclimit 1) as a join Candidate bon a.CandidateId = b.id