描述
现在有一个需求,让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表,id为主键, type是枚举类型,枚举成员为(completed,no_completed),completed代表邮件发送是成功的,no_completed代表邮件是发送失败的。简况如下:
下面是一个用户(user)表,id为主键,is_blacklist为0代表为正常用户,is_blacklist为1代表为黑名单用户,简况如下:
现在让你写一个sql查询,每一个日期里面,正常用户发送给正常用户邮件失败的概率是多少,结果保留到小数点后面3位(3位之后的四舍五入),并且按照日期升序排序,上面例子查询结果如下:
结果表示:
2020-01-11失败的概率为0.500,因为email的第1条数据,发送的用户id为2是黑名单用户,所以不计入统计,正常用户发正常用户总共2次,但是失败了1次,所以概率是0.500;
2020-01-12没有失败的情况,所以概率为0.000.
(注意: sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5,sqlite四舍五入的函数为round)
SQL练习题65(较难):
select d.date,round(count(case when d.type='no_completed' then id end)/count(*),3)
from (SELECT a.*
from email a,user b,user c
where a.send_id=b.id
and a.receive_id=c.id
and b.is_blacklist=0
and c.is_blacklist=0) d--求出发送者和接受者都为正常的用户
group by d.date
order by d.date;