牛客SQL搞人心态的一天
异常的邮件概率
http://www.nowcoder.com/questionTerminal/d6dd656483b545159d3aa89b4c26004e
select
date,
cast( count(case when type='no_completed' then 1 else null end)*1.0 / count(id) as decimal(10,3)) 'p'
from email
where send_id in (select id from user where is_blacklist=0)
and receive_id in (select id from user where is_blacklist=0)
group by date;
两个in的效率不高,大规模数据可以用send_id和receive_id分别join两次user(虽然也挺亏的)
还有结果在sqlite跑不起来,小数点没有保留三位,虽然sqlite是可以用cast函数的。mysql结果正确。