99，SQL训练之，力扣，578. 查询回答率最高的问题

凡梦_leo

已于 2024-09-26 21:55:19 修改

阅读量826

点赞数 11

分类专栏： SQL实战训练文章标签： leetcode 算法 sql 开窗函数排序 linux 数据库

于 2024-09-26 21:54:57 首次发布

本文链接：https://blog.csdn.net/qq_55006020/article/details/142578400

版权

SQL实战训练专栏收录该内容

120 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

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 最小的那个。

查询结果如下例所示。

示例 1：

输入：
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 回答率最高。

三，建表语句

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

四，分析

题解:

表:调查日志

表字段:日志id,问题状态,问题ID,问题答案,问题会话顺序,问题回答时间

求回答率最高的问题,如果有多个就取问题id最小的那个

问题回答率: 每个问题的回答次数/每个问题的显示次数

第一步扁平化表格以问题分组然后求和该问题回答过的列和该问题显示的列

    select question_id,
       sum(if(action='show',1,0)) as show_cnt,
       sum(if(action='answer',1,0)) as anser_cnt
from SurveyLog group by question_id

第二步，计算问题回答率

with t1 as (
    select question_id,
       sum(if(action='show',1,0)) as show_cnt,
       sum(if(action='answer',1,0)) as anser_cnt
from SurveyLog group by question_id
),t2 as (
select
    question_id, show_cnt, anser_cnt,
    anser_cnt/show_cnt as huidalv
    from t1
)
   select * from t2;

最后一步取回答率最高的行如果有多行就以问题排序 limit取第一

五，SQL解答

with t1 as (
    select question_id,
       sum(if(action='show',1,0)) as show_cnt,
       sum(if(action='answer',1,0)) as anser_cnt
from SurveyLog group by question_id
),t2 as (
select
    question_id, show_cnt, anser_cnt,
    anser_cnt/show_cnt as huidalv
    from t1
)
#    select * from t2;
   ,t3 as (
    select question_id, show_cnt, anser_cnt, huidalv,
       dense_rank()over(order by huidalv desc ) rn
    from t2
)
select question_id from t3 where rn=1 order by question_id limit 1;