题解 | #某乎问答11月份日人均回答量#

1. 解题思路：

• 首先根据日期answer_date分组来分别统计每天回答问题数量issue的值和答题人数author的值，得到一张临时表如下：

SELECT answer_date, COUNT(issue_id) AS issue, COUNT(DISTINCT author_id) author FROM answer_tb GROUP BY answer_date;

• 接着再提取上表中的数据，根据题目给的公式：（回答问题数量issue / 答题人数author）= 人均回答量per_num，就可以求出11月每天的人均回答量。

2. 考察知识点：聚合函数COUNT、去重函数DISTINCT、ROUND函数、分组函数GROUP BY的用法以及临时表的构建

3. 完整参考代码:

SELECT a.answer_date, ROUND((a.issue/a.author), 2) AS per_num
FROM (SELECT answer_date, COUNT(issue_id) AS issue, COUNT(DISTINCT author_id) author FROM answer_tb GROUP BY answer_date) a;

或者还可以简化一下：

SELECT answer_date, ROUND(COUNT(issue_id)/COUNT(DISTINCT author_id), 2)per_num FROM answer_tb GROUP BY answer_date;