大数据第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 表示当前会话中问题的编号。

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

 

最终SQL:

-- 方法一
SELECT 
    question_id as survey_log
FROM
   (SELECT 
         question_id,
         SUM(case when action="answer" THEN 1 ELSE 0 END) as num_answer,
         SUM(case when action="show" THEN 1 ELSE 0 END) as num_show
    FROM 
         18_survey_log
    GROUP BY 
         question_id
    ) as tbl
ORDER BY
    (num_answer / num_show) DESC
LIMIT 1;

-- 方法二
SELECT 
    question_id AS 'survey_log'
FROM
    survey_log
GROUP BY
    question_id
ORDER BY
    COUNT(answer_id) / COUNT(IF(action = 'show', 1, 0)) DESC
LIMIT 1;