邮件发送成功概率_

表: 邮件表

+------------+--------------+
|  字段名称   | 类型         | 
+------------+--------------+
| 编号        |    int      |
| 寄信人编号   |    int      |
| 收信人编号   |     int     |
| 枚举类型    | varchar(512) |
| 日期        | date(255)    |
+------------+--------------+

编号 是该表的主键。
该表的每一行记录“邮件APP”的邮件数据，包含了编号、寄信人编号、收信人编号、
枚举类型（completed 表示邮件发送成功，no_completed 表示邮件发送失败）。

表: 用户表

+-------------+--------------+
| 字段名称     | 类型         |
+-------------+--------------+
| 用户编号     | int(11)    |
| 是否为黑名单  | varchar(512) |
+-------------+---------------+

用户编号 是该表的主键。 
用户编号 是 “邮件表” 表中 用户编号 的外键。
该表的每一行记录了“邮件APP”的所有用户信息，包括用户编号 、是否为黑名单。
其中，是否为黑名单中值为 0 表示是白名单用户，值为 1 表示是黑名单用户。

编写一个SQL查询,查询邮件发送和接收方都为白名单时，每天邮件发送失败的概率是多少？并请按照日期升序排列。

查询结果如下例所示。

示例 1:

输入: 表：邮件表
+----+-------+-------+--------------+-------------+
| 编号 | 寄信人编号 | 收信人编号 | 枚举类型         | 日期          |
+----+-------+-------+--------------+-------------+
| 1  | 2     | 3     | completed    | 2020-01-11  |
| 2  | 1     | 3     | completed    | 2020-01-11  |
| 3  | 1     | 4     | no_completed | 2020-01-11  |
| 4  | 3     | 1     | completed    | 2020-01-12  |
| 5  | 3     | 4     | completed    | 2020-01-12  |
| 6  | 4     | 1     | completed    | 2020-01-12  |
+----+-------+-------+--------------+-------------+

表：用户表
+------+---------+
| 用户编号 | 是否为黑名单  |
+------+---------+
| 1    | 0       |
| 2    | 1       |
| 3    | 0       |
| 4    | 0       |
+------+---------+ 输出: +------------+---------+
|    日期    | 失败率   |
+------------+---------+
| 2020-01-01 | 0.5     |
| 2020-01-12 | 0       |
+------------+---------+

输入

CREATE TABLE 邮件表 
(
    编号 INT,
    寄信人编号 INT,
    收信人编号 INT,
    枚举类型 VARCHAR(512),
    日期 DATE
);

CREATE TABLE 用户表
(
    用户编号 INT,
    是否为黑名单 VARCHAR(512)
);
INSERT INTO 邮件表 (编号, 寄信人编号, 收信人编号, 枚举类型, 日期) VALUES (1, 2, 3, 'completed', '2020-01-11');
INSERT INTO 邮件表 (编号, 寄信人编号, 收信人编号, 枚举类型, 日期) VALUES (2, 1, 3, 'completed', '2020-01-11');
INSERT INTO 邮件表 (编号, 寄信人编号, 收信人编号, 枚举类型, 日期) VALUES (3, 1, 4, 'no_completed', '2020-01-11');
INSERT INTO 邮件表 (编号, 寄信人编号, 收信人编号, 枚举类型, 日期) VALUES (4, 3, 1, 'completed', '2020-01-12');
INSERT INTO 邮件表 (编号, 寄信人编号, 收信人编号, 枚举类型, 日期) VALUES (5, 3, 4, 'completed', '2020-01-12');
INSERT INTO 邮件表 (编号, 寄信人编号, 收信人编号, 枚举类型, 日期) VALUES (6, 4, 1, 'completed', '2020-01-12');
INSERT INTO 用户表 (用户编号, 是否为黑名单) VALUES (1, '0');
INSERT INTO 用户表 (用户编号, 是否为黑名单) VALUES (2, '1');
INSERT INTO 用户表 (用户编号, 是否为黑名单) VALUES (3, '0');
INSERT INTO 用户表 (用户编号, 是否为黑名单) VALUES (4, '0');

select 日期 ,round(avg(if(枚举类型="no_completed",1,0)),4) as 失败率 from 邮件表 x join 用户表 y on x.寄信人编号=y.用户编号 join 用户表 z on x.收信人编号=z.用户编号 where y.是否为黑名单=0 and z.是否为黑名单=0 group by 1 order by 1

邮件发送成功概率

输入

输出

问题信息

热门推荐

相关试题