Leetcode_578_查询回答率最高的问题_SQL

原创已于 2022-02-17 14:51:20 修改 · 365 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

于 2022-02-16 12:24:08 首次发布

daily_algorithm 同时被 2 个专栏收录

230 篇文章

订阅专栏

SQL

27 篇文章

订阅专栏

本文探讨了一种有趣的SQL查询方法，通过sum函数结合过滤条件来高效统计答题比例。同时对比了不同的查询写法，包括使用if函数和子查询进行数据聚合，以找出回答最多的问题。这些技巧对于数据分析师和数据库管理员优化查询性能非常有用。

select
    question_id as survey_log
from surveylog
group by question_id
order by sum(action='answer')/sum(action='show') desc
limit 1;

很有意思的解法，sum中可以带过滤条件
也可以用sum(if(action = ‘answer’, 1, 0))

with t as (
    select question_id, count(question_id) as question_count
    from surveylog
    where action = 'show'
    group by question_id
), t1 as (
    select question_id, count(answer_id) as answer_count
    from surveylog
    where action = 'answer'
    group by question_id
)

select t.question_id as survey_log
from t join t1
on t.question_id = t1.question_id
order by t1.answer_count / t.question_count desc
limit 1