题目:
编写一段 SQL 来查找:在被报告为垃圾广告的帖子中,被移除的帖子的每日平均占比,四舍五入到小数点后 2 位。
Actions 表:
Removals 表:
Result 表:
2019-07-04 的垃圾广告移除率是 50%,因为有两张帖子被报告为垃圾广告,但只有一个得到移除。
2019-07-02 的垃圾广告移除率是 100%,因为有一张帖子被举报为垃圾广告并得到移除。
其余几天没有收到垃圾广告的举报,因此平均值为:(50 + 100) / 2 = 75%
注意,输出仅需要一个平均值即可,我们并不关注移除操作的日期。
建表语句:
1 Create table If Not Exists actions_1132 ( 2 user_id int, 3 post_id int, 4 action_date date, 5 action enum('view', 'like', 'reaction', 'comment', 'report', 'share'), 6 extra varchar(6) 7 ); 8 Create table If Not Exists removals_1132 ( 9 post_id int, 10 remove_date date 11 ); 12 Truncate table actions_1132; 13 insert into actions_1132 (user_id, post_id, action_date, action, extra) values 14 ('1', '1', '2019-07-01','view',null), 15 ('1', '1', '2019-07-01','like',null), 16 ('1', '1', '2019-07-01','share',null), 17 ('2', '2', '2019-07-04','view',null), 18 ('2', '2', '2019-07-04','report','spam'), 19 ('3', '4', '2019-07-04','view',null), 20 ('3', '4', '2019-07-04','report','spam'), 21 ('4', '3', '2019-07-02','view',null), 22 ('4', '3', '2019-07-02','report','spam'), 23 ('5', '2', '2019-07-03','view',null), 24 ('5', '2', '2019-07-03','report','racism'), 25 ('5', '5', '2019-07-03','view',null), 26 ('5', '5', '2019-07-03','report','racism'); 27 Truncate table removals_1132; 28 insert into removals_1132 (post_id, remove_date) values ('2', '2019-07-20'),( '3', '2019-07-18');
解题思路:
①先筛选出actions表中被报道为垃圾信息即extra = 'spam'的数据;
1 select post_id, action_date 2 from actions_1132 3 where extra = 'spam'
②将第一步查询出来的表通过post_id与 removals左连接;
1 select * 2 from ( 3 select post_id, action_date 4 from actions_1132 5 where extra = 'spam' 6 ) as a 7 left join removals_1132 b 8 on a.post_id = b.post_id
③然后再根据第二步查询出的临时表,以action_date为分组,统计出删除比率;
1 select action_date,count(distinct b.post_id) / count(distinct a.post_id) as rate 2 from ( 3 select post_id, action_date 4 from actions_1132 5 where extra = 'spam' 6 ) as a 7 left join removals_1132 b 8 on a.post_id = b.post_id 9 group by action_date
④最后根据上一步的临时表,计算出被移除的帖子的每日平均占比;
1 select round(sum(rate)/ count(*)*100, 2) as average_daily_percent 2 from ( 3 select action_date,count(distinct b.post_id) / count(distinct a.post_id) as rate 4 from ( 5 select post_id, action_date 6 from actions_1132 7 where extra = 'spam' 8 ) as a 9 left join removals_1132 b 10 on a.post_id = b.post_id 11 group by action_date 12 ) as temp标签:07,1132,力扣,2019,MySQL,date,post,id From: https://www.cnblogs.com/liu-myu/p/17312342.html