牛客sql练习记录：sql18、19、21、22、23

SQL18 分组计算练习题：

题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

思路：count、group by—性别；avg（sum30天内活跃天数）；avg（sum发帖数量）

select gender,university,
       count(gender) as user_num,
       select(
                sum(active_days_within_30) as sum_active
            )/30 as avg_active,
        select(
                sum(answer_cnt) as sum_cnt
            )/count(id) as avg_cnt
from user_profile
group by gender
        

结果：执行错误。

发现错误：①未对学校分组，每个学校、每种性别：group by 性别、大学；

                  ②直接用avg计算两个平均数即可，avg（30...）、avg（发帖）;应计算question_cnt字段，错误计算成了answer_cnt。

答案：

select gender,university,
       count(gender) as user_num,
       avg(active_days_within_30) as avg_active,
       avg(question_cnt) as avg_question_cnt
from user_profile
group by gender,university

结果：运行成功。

SQL19 分组过滤练习题

 题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

 思路：学校，avg（question），avg（answer），where......or

select university,
       avg(question_cnt) as avg_question_cnt,
       avg(answer_cnt) as avg_answer_cnt
from user_profile
where avg_question_cnt < 5
      or avg_answer_cnt <20

结果：

程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"
SQL_ERROR_INFO: "Unknown column 'avg_question_cnt' in 'where clause'"

经搜索查询得知：无法在Where条件中直接使用别名作判断条件.

解决：group by 大学之后，使用having进行过滤

——《SQL必知必会第四版》 p104

答案

select university,
       avg(question_cnt) as avg_question_cnt,
       avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt < 5
      or avg_answer_cnt <20

结果：运行成功。

思考：无法在where条件中直接使用别名，是否可以直接使用avg、max、min等聚集函数作为条件？

答案：不能

原因：

1、where子句本身设计的目的就是用来筛选符和条件的数据。所以在where时还没有得到最终的结果集。

2、having子句是跟在group by之后的，此时已经是有结果集了，因为针对数据的过滤交给了where去做。

来自：

数据库关于where子句为什么不能使用聚合函数的思考_为什么where后面不能用聚合函数_六十三吖的博客-CSDN博客

SQL查询语句的执行顺序如下：

FROM ：指定要查询的数据表或视图
JOIN ：将多个数据表或视图连接在一起
WHERE ：筛选符合指定条件的数据行
GROUP BY ：对查询结果按照指定列进行分组
HAVING ：对分组后的结果进行筛选
SELECT ：选择要查询的列和计算表达式
DISTINCT ：对查询结果进行去重
ORDER BY ：对查询结果进行排序
LIMIT ：指定查询结果的数量限制

SQL21 浙江大学用户题目回答情况

 题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

 思路:inner join question_XXXX_XXXX

select device_id,question_id,result
from user_profile
inner join question_practice_detail
    on user_profile.device_id = question_practice_detail.device_id
where university = '浙江大学'

程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"
SQL_ERROR_INFO: "Column 'device_id' in field list is ambiguous"

解决：将device_id精确定位

答案：

select user_profile.device_id,question_id,result
from user_profile
inner join question_practice_detail
    on user_profile.device_id = question_practice_detail.device_id
where university = '浙江大学'

感觉还有很多其他解法。

SQL22 统计每个学校的答过题的用户的平均答题数

运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

 思考：展示大学和平均答题数量—>平均答题数量=总答题数/总人数 ： avg_question = count（question_id）/ count(device_id) 

答案：

select university,
       count(question_id) / count(distinct question_practice_detail.device_id) as avg_answer_cnt
from user_profile
inner join question_practice_detail
  on user_profile.device_id = question_practice_detail.device_id
group by university
order by university

注意：question_id出现重复值，需要使用distinct计算唯一值。

SQL23 统计每个学校各难度的用户平均刷题数

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

 三张表：user_profile,question_practice_detail,question_detail

 思路：1.展示大学、难度、平均答题量

            2. 2.1不同学校—>group by university；

               2.2 不同难度—>group by difficult_level；

               2.3 不同难度平均答题量—>总答题数/总人数

           3.多表联结：列出所有表，然后定义表之间的关系

答案：

select university,difficult_level,
       count(question_practice_detail.question_id) / count( distinct question_practice_detail.device_id) as avg_answer
from user_profile,question_practice_detail,question_detail
where user_profile.device_id = question_practice_detail.device_id
      and question_practice_detail.question_id = question_detail.question_id
group by university,difficult_level

思考：表名太长了看着眼花，应该怎么解决？
经查找发现可以在from中可以变成 user_profile u（将表名简写成u），但是为什么可以这样？