网站建设套餐自助报价wordpress发信插件
前言
练习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
结果：
总结：
能运行就行。