前言
练习sql语句,所有题目来自于力扣(https://leetcode.cn/problemset/database/)的免费数据库练习题。
今日题目:
1934.确认率
表:Signups
列名 | 类型 |
---|---|
user_id | int |
time_stamp | datetime |
User_id是该表的主键。每一行都包含ID为user_id的用户的注册时间信息。
表:Confirmations
列名 | 类型 |
---|---|
user_id | int |
time_stamp | datetime |
action | ENUM |
(user_id, time_stamp)是该表的主键。user_id是一个引用到注册表的外键。action是类型为(‘confirmed’, ‘timeout’)的ENUM该表的每一行都表示ID为user_id的用户在time_stamp请求了一条确认消息,该确认消息要么被确认(‘confirmed’),要么被过期(‘timeout’)。
用户的 确认率 是 ‘confirmed’ 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。
编写一个SQL查询来查找每个用户的 确认率 。
我那不值一提的想法:
- 首先梳理表内容,题干一共给了两张表,一张登录表,记录了用户id,时间戳,一张确认表,记录了用户id,时间戳,和响应状态(包括确认,超时)
- 其次分析思路,需要查询用户的确认率,确认率也就是确认的消息除以请求消息的总数,如果没有任何请求消息,则确认率为0
- 这道题难度不大,就是求各用户的
action = "confirmed"
的总数除以各用户count(action)
的总数。 - 一开始我不知道咋了,脑壳发卡了,用的笨办法
with all_count as (
select s.user_id,count(c.user_id) as all_counts
from Confirmations c
right join Signups s
on c.user_id = s.user_id
group by user_id)
,
con_count as (
select s.user_id,count(c.action) as con_counts
from Confirmations c
right join Signups s
on c.user_id = s.user_id and action = "confirmed"
group by user_id)
select a.user_id,ifnull(round(c.con_counts/a.all_counts,2),0) as confirmation_rate
from all_count a
left join con_count c
on a.user_id = c.user_id
- 后来突然反应过来不就if判断语句就可以了吗
select s.user_id,round(ifnull(sum(if(c.action = "confirmed",1,0))/count(c.action),0),2) as confirmation_rate
from Signups s
left join Confirmations c
on s.user_id = c.user_id
group by s.user_id
结果:
总结:
能运行就行。