求确认率
action是类型为(‘confirmed’, ‘timeout’)的ENUM;
要么被确认(‘confirmed’),要么被过期(‘timeout’);
确认率= ‘被确认’ / ‘总数’
sum(c.action = ‘confirmed’) / count(c.action)
<=> 分组后 avg( c.action = ‘confirmed’ )
c.action = ‘confirmed’ 后,值转为了0、1,
avg求平均,即 1的总数/ 0和1总数,即确认的总数/总的总数
即计算到了确认率
select s.*,c.user_id,c.time_stamp,if(c.action = 'confirmed',1,0)action
from Signups s left join Confirmations c
on s.user_id = c.user_id
所以左连接后,要么可以用sum/count;
select s.user_id, ifnull(round(sum(c.action = 'confirmed')/count(c.action),2),0) confirmation_rate
from Signups s left join Confirmations c
on s.user_id = c.user_id
group by s.user_id
要么avg()
select s.user_id, round(ifnull(avg(c.action = 'confirmed '),0),2) confirmation_rate
from Signups s left join Confirmations c
on s.user_id = c.user_id
group by s.user_id
因为这里要查找每个用户的确认率 。包括没有请求任何确认消息的用户,确认率设为 0。所以要用到用户全表,所有用户都要体现;
Confirmations表只是发了消息的用户表,不齐全,所以要左连接全用户表,没有发消息的用户右边为null替换为0。