SQL入门学习笔记

目录

补充

查询结果限制返回行数

Where in 和Not in 

操作符混合运用

查看学校名称中含北京的用户 

计算男生人数以及平均GPA 

2021年10月24日 

窗口函数

每个学校答过题的用户平均答题数量情况 

 查找山东大学男生的GPA

 计算25岁以上和以下的用户数量

计算用户的平均次日留存率 

统计每种性别的人数

2021年10月25日

综合练习

2021年10月26日

综合练习

2021年11月1日

得分不小于平均分的最低分

平均活跃天数和月活人数（format格式化日期）

月总刷题数和日均刷题数（计算每月天数，字段中添加总计,last_day,union all,concat）

---

前言

        记录总结每天学习的内容

补充

查询结果限制返回行数

 现在查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。

 

根据输入，你的查询应返回以下结果：

 

select device_id from user_profile limit 2

Where in 和Not in 

题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

user_profile

 

根据输入，你的查询应返回以下结果：

select device_id ,gender, age, university, gpa
from user_profile
where university IN ("北京大学","复旦大学","山东大学")

select device_id ,gender, age, university, gpa 
from user_profile
where university NOT IN ("浙江大学")

操作符混合运用

select device_id,gender,age,university,gpa from user_profile 
where (gpa > 3.5 and university = '山东大学') or (gpa > 3.8 and university = '复旦大学')

查看学校名称中含北京的用户 

select device_id,age,university from user_profile where university like '%北京%'

计算男生人数以及平均GPA 

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

user_profile

根据输入，你的查询应返回以下结果：

select sum(gender = 'male') 'male_num',avg(gpa) 'avg_gpa' 
from user_profile 
where gender = 'male'
#sum(gender = 'male'),符合条件记为1，最后相加

2021年10月24日 

窗口函数

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出相应数据。

 根据示例，你的查询结果应参考以下格式：

        

select device_id,university,gpa
from 
(select device_id,university,gpa,
row_number() 
over(partition by university order by gpa) row_A from user_profile) user_A
#窗口函数，对university字段进行分组，对gpa字段进行排序，类似于：
#user_A:
#device_id 	university	  gpa    row_A
#  ...	      XX大学1	  ...	  1
#  ...	      XX大学1	  ...	  2
#  ...	      XX大学1	  ...	  3
#  ...	      XX大学2	  ...	  1
#  ...	      XX大学2	  ...	  2
#  ...	      XX大学2	  ...	  3
#  ...

where row_A = 1
#对每组只取row_A = 1的部分：

#user_A where row_A = 1:
#device_id 	university	gpa    row_A
#  ...	     XX大学1	...	    1
#  ...	     XX大学2	...  	1

每个学校答过题的用户平均答题数量情况 

用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。

 答题情况明细表 question_practice_detail，其中question_id是题目编号，result是答题结果。

 说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以不同用户的个数，根据示例，你的查询应返回以下结果（结果保留1位小数）：

select university,
round(count(result)/count(distinct up.device_id),4) 'avg_answer-cnt' 
from user_profile up join question_practice_detail qpd on up.device_id = qpd.device_id 
group by university
#count(distinct up.device_id)去除重复的id

 查找山东大学男生的GPA

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

根据示例，你的查询应返回以下结果：

select device_id,gender,age,gpa from user_profile where university = "山东大学" 
union all #使用where或者union会直接去重
select device_id,gender,age,gpa from user_profile where gender = "male"

 计算25岁以上和以下的用户数量

 题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

user_profile

本题注意：age为null 也记为 25岁以下

根据示例，你的查询应返回以下结果：

select (case when age >= 25 then '25岁及以上' else '25岁以下' end) age_cut, 
count(device_id) 
from user_profile group by age_cut

计算用户的平均次日留存率 

 现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据

根据示例，你的查询应返回以下结果：

SELECT 
COUNT(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 left outer join question_practice_detail as q2
on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1

原表自己和自己左连接。连接方式为按device_id相等，q2.date - q1.date = 1天。

count()计算时加入distinct排除掉同一用户在某一天重复登录的可能性。

统计每种性别的人数

 根据示例，你的查询应返回以下结果：

select substring_index(profile,',',-1) as gender,count(device_id)
from user_submit
group by gender

1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；

2、POSITION(substr  IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；

3、LEFT(str, length)：从左边开始截取str，length是截取的长度；

4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；

5、SUBSTRING_INDEX(str  ,substr  ,n)：返回字符substr在str中第n次出现位置之前的字符串;

6、SUBSTRING(str  ,n ,m)：返回字符串str从第n个字符截取到第m个字符；

7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；

8、LENGTH(str)：计算字符串str的长度。

2021年10月25日

综合练习

题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

user_profile：

根据示例，你的查询应返回以下结果：

 此题阴险之处在于复旦大学的某位学生的question_id,result,date等信息都没有填写，全都是none。这就导致如果过早对date进行筛选就会直接筛去了这名复旦大学学生的信息，所以需要对date单独进行判断筛选。（来人，让俺砍了那厮！）

select a.device_id device_id,university,
sum(if(b.question_id and month(b.date) = 8,1,0)) question_cnt,
sum(if(b.result= 'right' and month(b.date) = 8,1,0)) right_question_cnt 
#date为8月的记为1，非8月的全都记为0，最后全部相加求和即是所求的答题数量。
from user_profile a left join question_practice_detail b on a.device_id = b.device_id 
where university = '复旦大学'
group by a.device_id#要求每个人...即需要分组

2021年10月26日

综合练习

题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

  question_practice_detail：

question_detail：

根据示例，你的查询应返回以下结果：

 根据题目描述：

        “浙江大学的用户” ➡ 筛选，where/having

        “不同难度题目下” ➡ 分组，group by

        “升序输出” ➡ 升序排列，order by

考虑到可能存在缺值的情况会干扰查询结果，决定使用内连接。

select difficult_level,
sum(result = 'right')/count(bc.question_id) correct_rate 
from user_profile a join 
(select b.device_id,c.question_id,b.result,difficult_level 
from question_practice_detail b join question_detail c 
on b.question_id = c.question_id) bc 
#首先对question_practice_detail和question_detail进行内连接
on a.device_id = bc.device_id 
where university = '浙江大学' #根据题意应先筛选大学后分组
group by bc.difficult_level 
order by correct_rate

2021年11月1日

得分不小于平均分的最低分

请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。

示例数据（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

示例输出数据：

 

本题筛选较多，由于avg()这类聚合函数会提前过滤掉很多数据，只能多次查询 

方法一，利用窗口函数：

select min(t.score) min_score_over_avg from
(SELECT er.score,
avg(er.score) over() avg_score from exam_record er
left join examination_info ei
on er.exam_id=ei.exam_id
where ei.tag='SQL'
and er.score is not null)t
where t.score>=t.avg_score

 eg.：

avg(score) over() avg_score:

id	score
1	56
2	52
3	46
4	46

👇

id	avg_score
1	50
2	50
3	50
4	50

avg(score):

id	score
1	56
2	52
3	46
4	46

👇

id	avg_score
1	50

方法二：使用聚合函数。

SELECT min(er.score) min_score_over_avg  from exam_record er
left join examination_info ei
on er.exam_id=ei.exam_id
where ei.tag='SQL'
and er.score>=
(SELECT avg(er.score) from exam_record er
left join examination_info ei
on er.exam_id=ei.exam_id
where ei.tag='SQL')

平均活跃天数和月活人数（format格式化日期）

用户在牛客试卷作答区作答记录存储在表exam_record中，内容如下：

（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

 请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau，上面数据的示例输出如下：

解释：2021年7月有2人活跃，共活跃了3天（1001活跃1天，1002活跃2天），平均活跃天数1.5；2021年9月有4人活跃，共活跃了5天，平均活跃天数1.25，结果保留2位小数。

注：此处活跃指有交卷行为。

select DATE_FORMAT(start_time,"%Y%m") month,
round(count(distinct uid,date_format(start_time,"%Y%m%d"))/count(distinct uid),2) avg_active_days,
count(distinct uid) as mau
from exam_record
where submit_time is not NULL
and YEAR(submit_time) = "2021"
group by month

主要难的一点是天数的计算。
到底是count(distinct uid,date_format(start_time,"%Y%m"))还是count(start_time)作为分子呢。
关键是理解题目的意思是：天数。
假设一个uid 比如1001在2021-07-06这一天有二个记录，
如果是count(start_time)那么就是天数是2
但是如果是count(distinct uid,date_format(start_time,"%Y%m"))天数就是1了，
因为只有2021-07-06只有一天存在！
所以关键是明白一天二个不同时间段是一天，还是二天。

月总刷题数和日均刷题数（计算每月天数，字段中添加总计,last_day,union all,concat）

请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt，以及该年的总体情况，示例数据输出如下：

 解释：2021年8月共有2次刷题记录，日均刷题数为2/31=0.065（保留3位小数）；2021年9月共有3次刷题记录，日均刷题数为3/30=0.100；2021年共有5次刷题记录（年度汇总平均无实际意义，可忽略）

select submit_month,month_q_cnt,avg_day_q_cnt 
from 
(select date_format(submit_time,"%Y%m") submit_month,
count(question_id) month_q_cnt,
round(count(question_id)/(day(last_day(date_format(submit_time,"%Y%m%d")))),3) avg_day_q_cnt 
from practice_record where year(submit_time) = "2021" 
group by submit_month
union all 
select concat(date_format(submit_time,"%Y"),"汇总") submit_month,
count(question_id) month_q_cnt,
round(count(question_id)/31,3) avg_day_q_cnt 
from practice_record where year(submit_time) = "2021") a 
order by submit_month

注：

1.date时间格式化时候%Y表示四位数年份，而%y表示两位数年份。（发现一位大佬的博客：sql时间格式化,时间日期相关的方法小总结_zzzgd_666的博客-CSDN博客_sql日期格式化）

2.表示所在月份天数：day(last_day(date_format(submit_time,"%Y%m%d")))

3.使用union all 

4.使用concat() 进行合并

完成试卷数大于1的有效用户（if(),over(partition by),group_concat()）

现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分），示例数据如下：

还有一张试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间），示例数据如下： 

请统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序。示例数据的输出结果如下：

解释：2021年的作答记录中，除了1004，其他用户均满足有效用户定义，但只有1002未完成试卷数大于1，因此只输出1002，detail中是1002作答过的试卷{日期:tag}集合，日期和tag间用:连接，多元素间用;连接。

select 
    t.uid,t.incomplete_cnt,t.complete_cnt
    # 进行group_concat()的时候，发现有重复记录，因此做一个去重
    ,group_concat(distinct t.new_con separator ';') as detial
from 
(
    select 
        uid,tag,start_time
        # 两个sum()窗口函数可以计算出没要记录的未完成与完成题目数，方便后续条件筛选，比较清晰
        ,sum(if(submit_time is null,1,0))over(partition by uid) as incomplete_cnt 
        ,sum(if(submit_time is not null,1,0))over(partition by uid) as complete_cnt
        ,concat(substring(start_time,1,10),':',tag) as new_con
    from exam_record t1 
    left join examination_info t2 on t1.exam_id=t2.exam_id
    where year(start_time)='2021' 
    order by start_time
) t 
where t.incomplete_cnt >1 and t.incomplete_cnt <5 and t.complete_cnt >=1 
group by t.uid
order by t.incomplete_cnt desc

 注：

1.为了防止sum函数的聚合作用导致数据丢失而采用窗口函数形式针对uid字段进行操作，故加入over(partition by uid)。

2.group_concat()表示将每组中的某一字段的所有列拼接在一起。

月均完成试卷数不小于3的用户作答（where ... in）

现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分），示例数据如下：

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间），示例数据如下：

请从表中统计出 月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出，示例输出如下：

 解释：用户1002和1005月均完成试卷数均为3，其他用户均小于3；1002和1005作答过的试卷tag分布结果按作答次数降序排序依次为C++、SQL、算法。

select tag, count(start_time) as tag_cnt
from exam_record er inner join examination_info ei
on er.exam_id = ei.exam_id
where uid in 
(select uid
from exam_record er 
group by uid, month(start_time)
having count(submit_time) >= 3)
group by tag
order by tag_cnt desc

使用where uid in 嵌套 单独对uid字段进行筛选。

试卷发布当天作答人数和平均分（三表连接）

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间），示例数据如下：

释义：用户1001昵称为牛客1号，成就值为3100，用户等级是7级，职业方向为算法，注册时间2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间） 示例数据如下：

 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分） 示例数据如下：

 请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序，示例数据结果输出如下：

 解释：只有一张SQL类别的试卷，试卷ID为9001，发布当天（2021-09-01）有1001、1002、1003、1005作答过，但是1003是5级用户，其他3位为5级以上，他们三的得分有[70,80,85,90]，平均分为81.3（保留1位小数）。

select ei.exam_id,
count(distinct uid) as uv, 
round(avg(score), 1) as avg_score 
from user_info ui 
join exam_record er using(uid) 
join examination_info ei 
on er.exam_id = ei.exam_id and date_format(ei.release_time, '%Y%m%d') = date_format(er.start_time, '%Y%m%d') 
where ui.level > 5 and ei.tag = 'SQL' 
group by ei.exam_id 
order by uv DESC, avg_score ASC

使用 A join B using(...) join C on ...来对三个表进行内连接

作答试卷得分大于过80的人的用户等级分布

 现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

 试卷作答信息表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

 

统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序，示例数据结果输出如下：

解释：9001为SQL类试卷，作答该试卷大于80分的人有1002、1003、1005共3人，6级两人，5级一人。

select ui.level level,count(level) level_cnt 
from user_info ui join exam_record er using(uid) 
join examination_info ei on er.exam_id = ei.exam_id 
where ei.tag = "SQL" and er.score > 80 
group by level 
order by level_cnt desc

每个题目和每份试卷被作答的人数和次数（union all）

现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

 题目练习表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

请统计每个题目和每份试卷被作答的人数和次数，分别在试卷区和题目区按uv & pv降序显示，示例数据结果输出如下：

 解释：试卷区有3人共练习3次试卷9001，1人作答3次9002；刷题区有3人刷5次8001，有2人刷2次8002

select * from (select er.exam_id tid,count(distinct er.uid) uv,
sum(if(er.start_time is not null,1,0)) pv 
from exam_record er 
group by tid 
order by uv desc,pv desc) a 
union all 
select * from (select pr.question_id tid,count(distinct pr.uid) uv,
sum(if(pr.submit_time is not null,1,0)) pv 
from practice_record pr 
group by tid 
order by uv desc,pv desc) b

注：order by不能直接出现在union的子句中，但是可以出现在子句的子句中。同时需要为子句命名。

分别满足两个活动的人（新建字段统计，TIMESTAMPDIFF计算时间差）

为了促进更多用户在牛客平台学习和刷题进步，我们会经常给一些既活跃又表现不错的用户发放福利。假使以前我们有两拨运营活动，分别给每次试卷得分都能到85分的人（activity1）、至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）发了福利券。

现在，需要你一次性将这两个活动满足的人筛选出来，交给运营同学。请写出一个SQL实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

 

示例数据输出结果： 

 解释：用户1001最小分数81不满足活动1，但29分59秒完成了60分钟长的试卷得分81，满足活动2；1003最小分数86满足活动1，完成时长都大于试卷时长的一半，不满足活动2；用户1004刚好用了一半时间（30分钟整）完成了试卷得分85，满足活动1和活动2

(select e1.uid,"activity1" as activity
from exam_record as e1
where year(start_time)=2021
group by e1.uid
having min(e1.score) >= 85)
UNION ALL
(select e1.uid,"activity2" as activity
from exam_record as e1
left join examination_info as e2
on e1.exam_id=e2.exam_id
where YEAR(start_time)=2021
and e2.difficulty="hard"
and e1.score>80
and TIMESTAMPDIFF(SECOND,e1.start_time, e1.submit_time) <= e2.duration*30)
order by uid

注：

1.上半部分无需进行连接即可操作。

2."activity1" as activity 进行新建字段。

3.timestampdiff函数中不能是minute而具体到秒。TIMESTAMPDIFF(SECOND,e1.start_time, e1.submit_time)

满足条件的用户的试卷完成数和题目练习数

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

 题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年完成的试卷数和题目练习数，示例数据输出如下：

解释：用户1001、1003、1004、1006满足高难度SQL试卷得分平均值大于80，但只有1001、1003是7级红名大佬；1001完成了1套试卷1001，练习了2次题目；1003完成了2套试卷9001、9002，未练习题目（因此计数为0）；结果按完成试卷数升序，按题目练习数降序。

with user_id as (select uf.uid
from user_info uf
left join exam_record er on uf.uid=er.uid
left join examination_info ef on er.exam_id=ef.exam_id
left join practice_record r on uf.uid=r.uid
where tag = 'SQL' and difficulty = 'hard' and level = 7
group by uf.uid having avg(er.score)>80) 
select 
    t.uid
    ,count(distinct t1.submit_time,t1.exam_id) as exam_cnt
    ,count(distinct t2.submit_time,t2.question_id) as question_cnt
from user_id t 
left join exam_record t1 on t.uid=t1.uid and year(t1.start_time)='2021'
left join practice_record t2 on t.uid=t2.uid and year(t2.submit_time)='2021'
group by t.uid
order by exam_cnt,question_cnt desc 

注：

1. with user_id as (select uf.uid
from user_info uf
left join exam_record er on uf.uid=er.uid
left join examination_info ef on er.exam_id=ef.exam_id
left join practice_record r on uf.uid=r.uid
where tag = 'SQL' and difficulty = 'hard' and level = 7
group by uf.uid having avg(er.score)>80)

使用with ... as ()使得红字部分相当于python中的全局变量，得到的表可以被后面的查询调用。

2.关于count(distinct t1.submit_time,t1.exam_id) ，防止一个用户做多次同样的题没被统计。

3.user_id t 
left join exam_record t1 on t.uid=t1.uid and year(t1.start_time)='2021'
left join practice_record t2 on t.uid=t2.uid and year(t2.submit_time)='2021'

将全局可被调用的表user_id 和 exam_record以及practice_record左连接，其目的在于分别筛选出start_time为2021的部分。由于exam_record比practice_record的uid字段内容不同，左连接后会有空值出现，直接筛选会出错，而使用内连接会丢失后面需要的值。