大数据第60天—MySQL之最高回答率-杨大伟

需求:请编写SQL查询来找到具有最高回答率的问题。

展示效果:

survey_log
285

survey_log 表中获得回答率最高的问题,survey_log 表包含这些列:id, action, question_id, answer_id, q_num, timestamp。id 表示用户 id;action 有以下几种值:"show","answer","skip";当 action 值为 "answer" 时 answer_id 非空,而 action 值为 "show" 或者 "skip" 时 answer_id 为空;q_num 表示当前会话中问题的编号。

1 Create table If Not Exists 18_survey_log (uid int, action varchar(255), question_id int, answer_id int, q_num int, timestamp int);
2 Truncate table 18_survey_log;
3 insert into 18_survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 285, null, 1, 123);
4 insert into 18_survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'answer', 285, 124124, 1, 124);
5 insert into 18_survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 369, null, 2, 125);
6 insert into 18_survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'skip', 369, null, 2, 126);

最终SQL:

 1 -- 方法一
 2 SELECT 
 3     question_id as survey_log
 4 FROM
 5    (SELECT 
 6          question_id,
 7          SUM(case when action="answer" THEN 1 ELSE 0 END) as num_answer,
 8          SUM(case when action="show" THEN 1 ELSE 0 END) as num_show
 9     FROM 
10          18_survey_log
11     GROUP BY 
12          question_id
13     ) as tbl
14 ORDER BY
15     (num_answer / num_show) DESC
16 LIMIT 1;
17 
18 -- 方法二
19 SELECT 
20     question_id AS 'survey_log'
21 FROM
22     survey_log
23 GROUP BY
24     question_id
25 ORDER BY
26     COUNT(answer_id) / COUNT(IF(action = 'show', 1, 0)) DESC
27 LIMIT 1;
原文地址:https://www.cnblogs.com/shui68home/p/13732142.html