1 题目描述

SurveyLog 表:

+-------------+------+  
| Column Name | Type |  
+-------------+------+  
| id          | int  |  
| action      | ENUM |  
| question_id | int  |  
| answer_id   | int  |  
| q_num       | int  |  
| timestamp   | int  |  
+-------------+------+

这张表可能包含重复项 action 是一个 ENUM(category) 数据, 可以是"show", "answer" 或者"skip" 这张表的每一行表示: ID = id 的用户对 question_id 的问题在 timestamp 时间进行了 action 操作如果用户对应的操作是"answer", answer_id 将会是对应答案的 id, 否则, 值为 null q_num 是该问题在当前会话中的数字顺序

回答率 是指: 同一问题编号中回答次数占显示次数的比率

编写一个解决方案以报告回答率 最高的问题. 如果有多个问题具有相同的最大回答率, 返回 question_id 最小的那个

2 测试用例

输入:
SurveyLog table:

+----+--------+-------------+-----------+-------+-----------+  
| id | action | question_id | answer_id | q_num | timestamp |  
+----+--------+-------------+-----------+-------+-----------+  
| 5  | show   | 285         | null      | 1     | 123       |  
| 5  | answer | 285         | 124124    | 1     | 124       |  
| 5  | show   | 369         | null      | 2     | 125       |  
| 5  | skip   | 369         | null      | 2     | 126       |  
+----+--------+-------------+-----------+-------+-----------+

输出:

+------------+  
| survey_log |  
+------------+  
| 285        |  
+------------+

解释:
问题 285 显示 1 次, 回答 1 次. 回答率为 1.0
问题 369 显示 1 次, 回答 0 次. 回答率为 0.0
问题 285 回答率最高

3 解题思路

使用 sum() if() 分组统计 question_id 状态为 show 和 answer 数量信息

select question_id,  
       sum(if(action = 'answer', 1, 0)) as answerSum,  
       sum(if(action = 'show', 1, 0))   as showSum  
from SurveyLog  
group by question_id;

查询结果

+-----------+---------+-------+
|question_id|answerSum|showSum|
+-----------+---------+-------+
|285        |1        |1      |
|369        |0        |1      |
+-----------+---------+-------+

计算 question_id 的回答率, 对数据进行排序 ratio desc, question_id asc

select question_id,  
       sum(if(action = 'answer', 1, 0)) / sum(if(action = 'show', 1, 0)) as ratio  
from SurveyLog  
group by question_id  
order by ratio desc, question_id asc;

查询结果

+-----------+------+
|question_id|ratio |
+-----------+------+
|285        |1.0000|
|369        |0.0000|
+-----------+------+

回答率在最终答案中不要求展示, 将回答率的计算放在 order by 中

select question_id as survey_log  
from SurveyLog  
group by question_id  
order by sum(if(action = 'answer', 1, 0)) / sum(if(action = 'show', 1, 0)) desc, question_id asc  
limit 1;

查询结果

+----------+
|survey_log|
+----------+
|285       |
+----------+

LeetCode--578. 查询回答率最高的问题

1 题目描述

2 测试用例

3 解题思路