sql 2

题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

在这里插入代码片

select device_id,gender,age,university
from user_profile
where age  is not null
1
2
3

题目：现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。

select  device_id,gender,age,university,gpa
from user_profile
where gender='male' and gpa>3.5
1
2
3

题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。
在这里插入代码片

select count(id) as male_num,avg(gpa) as avg_gpa
from user_profile
where gender='male'
1
2
3

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

用户信息表：user_profile
30天内活跃天数字段（active_days_within_30）
发帖数量字段（question_cnt）
回答数量字段（answer_cnt）
题目完整展示
个人理解 count id 试一下

在这里插入代码片

select gender,university,
count(id)as user_num, 
avg(active_days_within_30)as avg_active_days,
avg(question_cnt)as avg_question_cnt
from user_profile
group by gender,university
1
2
3
4
5
6

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

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
1
2
3
4
5
6

题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。
多项查找需用group by?
https://www.baidu.com/baidu?tn=monline_4_dg&ie=utf-8&wd=%E5%A4%9A%E9%A1%B9%E6%9F%A5%E6%89%BE%E9%9C%80%E7%94%A8group+by%3F
select 多项需要用group by吗
https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=monline_4_dg&wd=%20select%20%E5%A4%9A%E9%A1%B9%E9%9C%80%E8%A6%81%E7%94%A8group%20by%E5%90%97&oq=%25E5%25A4%259A%25E9%25A1%25B9%25E6%259F%25A5%25E6%2589%25BE%25E9%259C%2580%25E7%2594%25A8group%2520by%253F&rsv_pq=b1f7f1fb00000302&rsv_t=7a33AB%2BgO2ru4Osdp5Di4dLNC3ME4Cn6KOf9Av%2BKsw0ukSbxAXUt%2FbQM1K%2FgKVA43REe&rqlang=cn&rsv_dl=tb&rsv_enter=0&rsv_sug3=2&rsv_sug1=5&rsv_sug7=100&rsv_btype=t&prefixsug=%2520select%2520%25E5%25A4%259A%25E9%25A1%25B9%25E9%259C%2580%25E8%25A6%2581%25E7%2594%25A8group%2520by%25E5%2590%2597&rsp=0&rsv_sug4=38266
分组查询
https://blog.csdn.net/weixin_44302236/article/details/123241495
现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

select question_practice_detail.device_id,
question_practice_detail.question_id,question_practice_detail.result
from question_practice_detail inner join user_profile 
on
user_profile.device_id=question_practice_detail.device_id and user_profile.university='浙江大学'
order by question_id
1
2
3
4
5
6

select question_practice_detail.device_id,
question_practice_detail.question_id,question_practice_detail.result
from question_practice_detail inner join user_profile 
on
user_profile.device_id=question_practice_detail.device_id and university='浙江大学'
order by question_id
1
2
3
4
5
6

在这里插入代码片

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

1
2
3
4
5
6

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

用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。
不能对包含聚合或子查询的表达式执行聚合函数
http://www.baidu.com/link?url=9aOFWRKmWfzzHYDuN9BQEG13Mt2Lt5s-gyjvf4i9RuYEra4QSmXaQlgl_XasXX5onsIvkvZVtCUCbUmDS9WrDVIOe92MlgyWxT_-bAxPWj_&wd=&eqid=9b8da91c0001ef6c0000000662eb8ee2

https://www.manongdao.com/article-1360915.html
https://ask.csdn.net/questions/686735

select count(qpd.question_id) ,//count能起别名吗
count(device_id),
count()
avg_answer_cnt,
university
from user_profile up inner join
question_partice_detail qpd 
on up.device_id=qpd.device_id 
group by university
1
2
3
4
5
6
7
8
9

https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=monline_4_dg&wd=count%E8%83%BD%E8%B5%B7%E5%88%AB%E5%90%8D%E5%90%97&oq=%25E5%2588%2586%25E7%25BB%2584%25E6%259F%25A5%25E8%25AF%25A2&rsv_pq=c5a903d700003a87&rsv_t=4d952JYX615ksi72jSBiebE24cm%2FiVNTV2ON7kSmCQsoVkY6h3NNXfSnGGdYWzf4S7tu&rqlang=cn&rsv_dl=tb&rsv_enter=0&rsv_btype=t&inputT=9163&rsv_sug3=61&rsv_sug1=48&rsv_sug7=100&rsv_sug2=0&rsv_sug4=9846
https://segmentfault.com/q/1010000009271827/
https://class.imooc.com/course/qadetail/89089
如何引用count的数字

https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=monline_4_dg&wd=%20%E5%A6%82%E4%BD%95%E5%BC%95%E7%94%A8count%E7%9A%84%E6%95%B0%E5%AD%97&oq=count%25E8%2583%25BD%25E8%25B5%25B7%25E5%2588%25AB%25E5%2590%258D%25E5%2590%2597&rsv_pq=cf95b89000040273&rsv_t=be8aKsHgH59PG17cMOwbJskNih8AFMvtESq93WOCpeIrvfrZauafUBPbiulMwr%2B7CxD5&rqlang=cn&rsv_dl=tb&rsv_enter=0&rsv_btype=t&rsv_sug3=63&rsv_sug1=54&rsv_sug7=100&prefixsug=%2520%25E5%25A6%2582%25E4%25BD%2595%25E5%25BC%2595%25E7%2594%25A8count%25E7%259A%2584%25E6%2595%25B0%25E5%25AD%2597&rsp=0&rsv_sug4=86133
count统计后的数字如何引用
https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=monline_4_dg&wd=count%E7%BB%9F%E8%AE%A1%E5%90%8E%E7%9A%84%E6%95%B0%E5%AD%97%E5%A6%82%E4%BD%95%E5%BC%95%E7%94%A8&oq=%25E5%25A6%2582%25E4%25BD%2595%25E5%25BC%2595%25E7%2594%25A8count%25E7%259A%2584%25E6%2595%25B0%25E5%25AD%2597&rsv_pq=8f57c6f70002c9a7&rsv_t=48d8I0Jth9FQMVNTabSBb3pPF94CCyOc7rs8XlwHPHyR84k89uHXOu9yc%2FAvx01n4pkn&rqlang=cn&rsv_dl=tb&rsv_enter=0&rsv_btype=t&rsv_sug3=69&rsv_sug1=60&rsv_sug7=100&prefixsug=count%25E7%25BB%259F%25E8%25AE%25A1%25E5%2590%258E%25E7%259A%2584%25E6%2595%25B0%25E5%25AD%2597%25E5%25A6%2582%25E4%25BD%2595%25E5%25BC%2595%25E7%2594%25A8&rsp=0&rsv_sug4=3622

select university,
count(qpd.question_id)/ count(distinct qpd.device_id) as avg_answer_cnt
from user_profile up inner join
question_practice_detail qpd 
on up.device_id=qpd.device_id 
group by university
1
2
3
4
5
6

左连接
表

select *
from(
select * from student_table where sex='男')t1
left join
(select * from student_table where sex='女')t2
on t1.birth=t2.birth and t1.name=t2.name;
1
2
3
4
5
6

在这里插入代码片

select * from student_table where sex='男'
1

select * from student_table where sex='女'
1

右连接
内连接

select *
from(
select * from student_table where sex='男')t1
inner join
(select * from student_table where sex='女')t2
on t1.birth=t2.birth and t1.name=t2.name;
1
2
3
4
5
6

带条件查询

select count(t1.birth)as c1

from(
select * from student_table where sex='男')t1
inner join
(select * from student_table where sex='女')t2
on t1.birth=t2.birth and t1.name=t2.name;
1
2
3
4
5
6
7