SELECT*FROM (SELECT*FROM ActionsWHERE extra='spam') AS t LEFT JOIN Removals rON t.post_id=r.post_id;
找到按日期分组每组年内部的比例。注意两个表都要去重
SELECTCOUNT(DISTINCT r.post_id) /COUNT(DISTINCT t.post_id) AS daily_percentFROM (SELECT*FROM ActionsWHERE extra='spam') AS t LEFT JOIN Removals rON t.post_id=r.post_idGROUP BY action_date;
SELECTROUND(AVG(daily_percent)*100, 2) AS average_daily_percentFROM (SELECTCOUNT(DISTINCT r.post_id) /COUNT(DISTINCT t.post_id) AS daily_percentFROM (SELECT*FROM ActionsWHERE extra='spam' ) AS t LEFT JOIN Removals rON t.post_id=r.post_idGROUP BY action_date) AS t;