MySQL基础练习题31-确认率

目录

题目

准备数据

分析数据

总结

---

题目

用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。

编写一个SQL查询来查找每个用户的 确认率 。

准备数据

## 创建库
create database db;
use db;

## 创建Signups表
Create table If Not Exists Signups (user_id int, time_stamp datetime);

## 创建Confirmations表
Create table If Not Exists Confirmations (user_id int, time_stamp datetime, action ENUM('confirmed','timeout'));

## 向表中插入数据
Truncate table Signups;
insert into Signups (user_id, time_stamp) values ('3', '2020-03-21 10:16:13');
insert into Signups (user_id, time_stamp) values ('7', '2020-01-04 13:57:59');
insert into Signups (user_id, time_stamp) values ('2', '2020-07-29 23:09:44');
insert into Signups (user_id, time_stamp) values ('6', '2020-12-09 10:39:37');
Truncate table Confirmations;
insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-01-06 03:30:46', 'timeout');
insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-07-14 14:00:00', 'timeout');
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-12 11:57:29', 'confirmed');
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-13 12:58:28', 'confirmed');
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-14 13:59:27', 'confirmed');
insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-01-22 00:00:00', 'confirmed');
insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-02-28 23:59:59', 'timeout');

signups表

confirmations表

分析数据

用户 6 没有请求任何确认消息。确认率为 0。
用户 3 进行了 2 次请求，都超时了。确认率为 0。
用户 7 提出了 3 个请求，所有请求都得到了确认。确认率为 1。
用户 2 做了 2 个请求，其中一个被确认，另一个超时。确认率为 1 / 2 = 0.5。

 第一步：将action那一列进行一个if判断，只得出两种结果，当action = 'confirmed'时，结果为1；否则结果为0

select *,
       if(action = 'confirmed',1,0) as cn
from signups s  left join confirmations c on s.user_id = c.user_id;

第二步： 使用avg（）对结果进行一个计算。

with t1 as (
    select s.user_id,c.action,
           if(action = 'confirmed',1,0) as cn
    from signups s  left join confirmations c on s.user_id = c.user_id
)select user_id, round(avg(cn),2) confirmation_rate from t1
group by user_id;

总结

• 遇到 只出现两种结果的列，可以用if()转换成1，0来为单独的列后边计算

• 保留小数点后几位，使用round(x,保留位数)函数:四舍五入保留小数位.