【Mysql】sql查询，每一个日期里面，正常用户发送给正常。。。

题目描述：sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序。

方法一（多表联结），先找出正常用户发送失败的次数。

select date,count(id) as num
from email
where send_id not in (select id from user where is_blacklist=1)
and receive_id not in(select id from user where is_blacklist=1)
and type="no_completed"
group by date

然后正常用户的失败次数除以正常用户的发邮件总数，以日期分组。

with failures_num as
(
    select date,count(id) as num
    from email
    where send_id not in (select id from user where is_blacklist=1)
    and receive_id not in(select id from user where is_blacklist=1)
    and type="no_completed"
    group by date
)

select email.date, round(failures_num.num/count(email.id),3) as p
from email join failures_num
             on email.date=failures_num.date
where email.send_id not in (select id from user where is_blacklist=1)
and email.receive_id not in(select id from user where is_blacklist=1)
group by email.date
order by email.date

方法二（窗口函数），上面的写的太繁琐了，即便我用with..as分开，代码量也是太多了。还是推荐用窗口函数。

select date , 
       round(
           sum(case when type="no_completed" then 1 else 0 end)/count(email.id),3
           ) as p
from email
where send_id not in(select id from user where is_blacklist=1)
and receive_id not in(select id from user where is_blacklist=1)
group by date
order by date

sum(case when type="no_completed" then 1 else 0 end)，当 type="no_completed"为真时值为1否则为0.sum()累加出发送失败的次数。
这里顺便再提一遍mysql的执行顺序：
开始-》from子句-》where子句-》group by子句-》having子句-》order by子句-》select子句-》limit子句-》最终结果

sql执行顺序：
from-》join-》on-》where-》group by-》avg，sum...-》having-》select-》distinct-》order by-》limit