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

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

解题思路：
1）按日期分组group by
2）计算正常用户发邮件给正常用户的总次数，失败的次数
3）失败次数，总次数做商，采用ROUND函数保留小数点后三位
4）采用日期升序 ORDER BY ASC

首先贴上我的一开始写的傻答案:
分别求出总次数和失败次数作为临时表t1，t2，联结后做商……

SELECT t1.date, ROUND((t2.cot_fail /t1.cot_all),3) AS p
FROM (SELECT date,COUNT(*) AS cot_all
        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) t1
INNER JOIN (SELECT date,COUNT(*) AS cot_fail
            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) t2
ON t1.date = t2.date
ORDER BY t1.date ASC;

使用两个子查询太繁琐了，效率也很低，看了其他大佬的代码，发现可以采用CASE表达式简化
借鉴@多吃早睡

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

借鉴cekio,巧妙采用case和avg函数的结合，简化计算邮件异常的概率，同时采用where子查询

SELECT date,ROUND(AVG(CASE WHEN type = 'no_completed' THEN 1 ELSE 0 END),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 ASC;

补充知识：关于mysql中取小数的函数
1）ROUND(X[,D])
Rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X. D defaults to 0 if not specified.(x保留小数点后D位，D位右边的数四舍五入，D默认为0)
如：

mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1

注：当D为负数，从小数点左边开始数截断D位（归0)

mysql> SELECT ROUND(23.298, -1);
        -> 20

2)TRUNCATE(X[,D])
截断至小数点后D位，不进行四舍五入

mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9
mysql> SELECT TRUNCATE(122,-2);
       -> 100
mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1028

3）FROMATE(X,D[,local]
Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.(X保留小数点后D位，四舍五入，最后转换位string的格式)

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
        -> '12.332,20'

format(X,D[,local])中的local我不太清楚是什么意思，懂的大神讲解一下？