在被报告为垃圾广告的帖子中,被移除的帖子的每日平均占比,四舍五入到小数点后 2 位。
首先给出亮眼的答案!
SELECT ROUND(AVG(IFNULL(b.remove, 0) / a.spam) * 100, 2) AS average_daily_percent
FROM
(
SELECT action_date,
COUNT(DISTINCT post_id) AS spam
FROM actions
WHERE extra = 'spam'
GROUP BY action_date
) AS a
LEFT JOIN
(
SELECT action_date,
post_id,
COUNT(DISTINCT post_id) AS remove
FROM actions
WHERE extra = 'spam'
AND post_id IN (SELECT post_id FROM Removals)
GROUP BY action_date
) AS b
ON a.action_date=b.action_date
拆解一下题目,首先我们要找出有垃圾邮件的日期,然后求出占比,然后对这几天进行求平均,最后两位小数。
第一步,求出分子和分母,分子就是在removals表里的那些然后group by count,要注意去重,分母就是类似的原理
第二步,将两张表连起来,这里用了左连接,所以要注意用ifnull(学到了!)
第三步,求平均即可