题解 | #异常的邮件概率#

COUNT()在计算总数的时候对待 NULL 值的方式是，指定列的值为空的行被 COUNT() 函数忽略，，但若不指定列，而在 COUNT() 函数中使用星号“*”，则所有记录都不忽略。用NULL将不是no_completed的置换掉，然后用聚合函数去统计，这里用IF或者CASE都可以。

# SELECT date, ROUND((COUNT(IF(e.type = 'no_completed', 1, NULL)) / COUNT(type)), 3) AS p
# FROM email e JOIN user AS u ON e.send_id = u.id AND u.is_blacklist = 0 JOIN user AS u1 ON e.receive_id = u1.id AND u1.is_blacklist = 0
# GROUP BY date
# ORDER BY date

SUM 函数忽略计算中的 NULL 值。

SELECT date, ROUND((SUM(CASE e.type WHEN 'no_completed' THEN 1 ELSE NULL END) / COUNT(type)), 3) AS p
FROM email e JOIN user AS u ON e.send_id = u.id AND u.is_blacklist = 0 JOIN user AS u1 ON e.receive_id = u1.id AND u1.is_blacklist = 0
GROUP BY date
ORDER BY date