前言
这篇是进阶sql题目的记录，由于上一篇文章已经写将近一万字，有点长，就把剩下的再开一篇，免得总是重新发布
SQL126 平均活跃天数和月活人数
本题目要求统计，并且是多行，就需要使用group by查询
首先需要统计月份，这个需要format格式化出月份，统计每个月份里平均的活跃天数（各人活跃天数和/选取去重的人数），
月度活跃人数（在这个月且submit_time不为空的人数）
这里使用 count(distinct(date_format(submit_time,‘%Y-%m’))统计月份，但是发现数量对不上
经过反复尝试后发现这里不需要distinct去重，因为count自带去重，并且也去掉null了
select date_format(submit_time,'%Y%m') AS month,round(count(submit_time)/count(distinct(uid)),2) AS avg_active_days,count(distinct(uid)) AS mau
from exam_record
where year(submit_time)=2021 AND submit_time is not null
group by date_format(submit_time,'%Y%m')
之后发现有个用例通不过，检查后发现这里有一个用户在一天做了两种卷子
于是需要组合去重
select date_format(submit_time,'%Y%m') AS month,
round((count(distinct uid,date_format(submit_time,'%Y%m%d')))/count(distinct uid),2)
AS avg_active_days,
count(distinct uid) AS mau
from exam_record
where year(submit_time)=2021
group by date_format(submit_time,'%Y%m')
这里distinct不写括号也可以
round这里括号比较多，需要注意
SQL127 月总刷题数和日均刷题数
类似于上一道题，统计每个月的总题目数和日均刷题数量，group by肯定要用，但是第三行要求总的数量
这里求日均需要求这个月的天数，使用这个函数DAY(LAST_DAY(yourColumnName))
select date_format(submit_time,'%Y%m') AS submit_month
,count(date_format(submit_time,'%Y%m')) AS month_q_cnt
,round(count(date_format(submit_time,'%Y%m'))/DAY(LAST_DAY(submit_time)),3) AS avg_day_q_cnt
from practice_record
where year(submit_time) = 2021
group by date_format(submit_time,'%Y%m')
这里有一个错误
SQL_ERROR_INFO: "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘practice_record.submit_time’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
查阅得知这个/DAY(LAST_DAY(submit_time))因为day(last_day(submit_time)运算结果还是跟submit_time同样的一串数列，只有加上avg(),min()或max()运算才变成了一个数值作为分母使用
这样输出正确了
之后需要在最后一行输出总和
看到都是用union，union all做的，这两个分别是合并重复和不合并的，都是把两个查询结果上下合到一个表里
select date_format(submit_time,'%Y%m') AS submit_month
,count(date_format(submit_time,'%Y%m')) AS month_q_cnt
,round(count(date_format(submit_time,'%Y%m'))/avg(DAY(LAST_DAY(submit_time))),3) AS avg_day_q_cnt
from practice_record
where year(submit_time) = 2021
group by date_format(submit_time,'%Y%m')
union all
select '2021汇总' as submit_month,
count(submit_time) as month_q_cnt,
round(count(submit_time)/max(31),3) as avg_day_q_cnt
from practice_record
where year(submit_time) = 2021
order by submit_month
这里31加max是为了做分母，用30会报错，和放在下面是做不到的，需要另外计算
SQL128 未完成试卷数大于1的有效用户
这题目涉及两个表的统计，那么就是inner join 后再group by
开始是这样写的，但是发现count里为空条件和不为空条件查出来一样，好像是无效的，查找后发现可以这么写
if成立就是1，算一行，但是好像只能在mysql里运行，网站报错
于是改用sum
select r.uid
,sum(if(r.submit_time is null , 1 ,0) ) incomplete_cnt
,sum(if(r.submit_time is not null, 1 ,0) ) complete_cnt
,group_concat(distinct concat(date_format(r.start_time,'%Y-%m-%d'),':',i.tag) order by start_time separator ';') detail
from exam_record r inner join examination_info i
on r.exam_id = i.exam_id
where year(r.start_time)=2021
group by r.uid
having incomplete_cnt>1 AND incomplete_cnt<5 AND complete_cnt>=1
order by incomplete_cnt desc
SQL129 月均完成试卷数不小于3的用户爱作答的类别
这里要求把月均完成试卷>=3的用户作答的类别进行统计并且desc排列
这里的月均意思是这个用户完成的试卷数/完成书卷的月份数量
首先查出月均不小于3的用户
select uid
from exam_record er inner join examination_info ei
on er.exam_id =ei.exam_id
group by uid
having count(submit_time)/count(distinct(month(submit_time)))>=3
之后发现需要外面嵌套一层语句，那么在内层内联就没必要了
select ei.tag,
count(ei.tag) AS tag_cnt
from exam_record er inner join examination_info ei
on er.exam_id =ei.exam_id
where er.uid in
(select uid
from exam_record
group by uid
having count(submit_time)/count(distinct(month(submit_time)))>=3)
group by ei.tag
order by tag_cnt desc
SQL130 试卷发布当天作答人数和平均分
这个题目需要进行三表联查，
首先去exammination_info找出sql类别试卷的试卷标号，这里想到笛卡尔积的知识，就是会产生较多数据，那么一定要加好筛选条件
select ei.exam_id,
count(er.uid) as uv,
round(sum(score)/count(er.uid),1) AS avg_score
from user_info ui join exam_record er join examination_info ei
on ui.uid = er.uid and er.exam_id = ei.exam_id
where ei.tag='SQL' AND ui.level>5 AND day(er.start_time)=day(ei.release_time)
group by ei.exam_id
order by uv
运行结果是错误如下
这里多了一个人，但平均分却是正确的
之后改掉三处错误
1.使用avg计算平均分
2.count需要去重，count没有去重功能
3. 排序规则用逗号并列
select ei.exam_id,
count(distinct ui.uid) as uv,
round(avg(er.score),1) AS avg_score
from user_info ui join exam_record er join examination_info ei
on ui.uid = er.uid and er.exam_id = ei.exam_id
where ei.tag='SQL' AND ui.level>5 AND day(er.start_time)=day(ei.release_time)
group by ei.exam_id
order by uv DESC ,avg_score
SQL131 作答试卷得分大于过80的人的用户等级分布
题目要求计算出score大于80的SQL试卷的人里等级的统计，不同的等级的人的数量desc
select ui.level AS level,count(ui.uid) AS level_cnt
from user_info ui join examination_info ei join exam_record er
on ui.uid = er.uid AND ei.exam_id = er.exam_id
where ei.tag='SQL' AND er.score>80
group by ui.level
order by level_cnt desc
这里有一次错误，是发现多统计一个六级的，原因是socre是大于80而不是大于等于
SQL132 每个题目和每份试卷被作答的人数和次数
这道题目要求统计题目和试卷的人数次数，就是group by 两种，这里考虑使用union组合两个查询到一个表里
相比union，union all不会合并重复列，不会排序，效率会相对高一点
select exam_id AS tid,count(distinct(uid)) AS uv ,count(uid) AS pv
from exam_record
group by exam_id
order by uv desc,pv desc
union
select question_id AS tid,count(distinct(uid)) AS uv ,count(uid) AS pv
from practice_record
group by question_id
order by uv desc,pv desc
这里报错
SQL_ERROR_INFO: “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘union\nselect question_id AS tid,count(distinct(uid)) AS uv ,count(uid) AS pv\nfro’ at line 5”
解决办法是对临时表命名，并且放到嵌套语句里
select * from
(select exam_id AS tid,count(distinct(uid)) AS uv ,count(uid) AS pv
from exam_record
group by exam_id
order by uv desc,pv desc) a
union
select * from
(select question_id AS tid,count(distinct(uid)) AS uv ,count(uid) AS pv
from practice_record
group by question_id
order by uv desc,pv desc) b
SQL133 分别满足两个活动的人
这里是两种活动，尝试在select里进行判断，没有成功，那么就只能写两个语句，然后union all 在一起
SELECT uid, 'activity1' as activity
FROM exam_record
WHERE YEAR(start_time)=2021
GROUP BY uid
HAVING MIN(score)>=85
UNION ALL
SELECT DISTINCT uid, 'activity2' as activity
FROM exam_record
JOIN examination_info USING(exam_id)
WHERE YEAR(start_time)=2021 and difficulty='hard' and score>80
and TimeStampDiff(SECOND, start_time, submit_time)<=duration*30
ORDER BY uid;
这道题目有几个需要注意的点
1.分数都大于85可以写最小值大于85
2.可以不用on而是用using来连接表
3.这里小于持续时间的一半，如果用分钟作为单位，duration除2，会有问题，而用秒，另外一边*30就对了，猜测是小数点的问题
SQL134 满足条件的用户的试卷完成数和题目练习数
这道题目比较复杂，有四个表，考虑join过多不方便，做一个嵌套查询
select ui.uid AS uid,
count(er.uid) AS exam_cnt,
count(pr.submit_time) AS question_cnt
from
user_info ui join exam_record er join practice_record pr
on ui.uid =er.uid AND pr.uid = er.uid
where er.exam_id in
(select ei.exam_id
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id
where difficulty = 'hard' AND tag ='SQL'
)
AND ui.level>=7 AND year(er.submit_time) =2021
group by er.uid
having avg(er.score)>80
order by exam_cnt asc,question_cnt desc
结果少了一个，并且统计数有问题，于是考虑嵌套多个表进去，但是最后还是一个，在排查后发现是join有的人做了卷但没做题，join的时候就会被排除掉，于是改用left join保证做卷的人都保存,这里注意left join 出来后计算时要去重，讨论区一个回答解释的非常好
select er.uid,count(distinct er.exam_id) exam_cnt,count(distinct p.id) question_cnt
from exam_record er
left join practice_record p
on er.uid=p.uid and year(er.submit_time)=2021 and year(p.submit_time)=2021
where er.uid in
(select er.uid
from exam_record er left join user_info u on er.uid = u.uid
left join examination_info ex on er.exam_id=ex.exam_id
where level = 7 and tag="SQL" and difficulty="hard"
group by er.uid
having avg(score)>80)
group by er.uid
order by exam_cnt ,question_cnt desc
最终可运行的代码如上，需要注意的问题非常多
- 去重需要组合，比如问题这里用问题id和提交时间，或者单独用id去重也可以
- 这种可能丢失记录的表用left join很重要，用之后必须用where条件筛选，或者distinct筛选，否则会多很多重复笛卡尔积
- on后跟条件比where后跟条件要好，因为这里一些记录的做题记录为空，那么where里写会直接删除这些记录，on后可以保留下有字段值为空的记录，符合题目要统计为做题数为0的要求
- 左连的顺序很重要，要把可能为空的放在左边，并且左连一次跟一个on的条件
SQL135 每个6/7级用户活跃情况
这道题仍然是四个表，这次我们就直接使用左连接
但是结果是弄了很久，查出来有问题，一直报错in后只能跟一个字段，但是也排查不出来，只好重写
这道作为一个难度最高的题目，用原表进行连接非常费力，而用临时表进行连接就好很多
首先把查出的试卷信息放在一个表，题目信息放在一个表，合成一个临时表，然后左连到用户信息表上
select u_i.uid as uid,
count(distinct act_month) as act_month_total,
count(distinct case when year(act_time) = 2021 then act_day end) as act_days_2021,
count(distinct case when year(act_time) = 2021 and tag = 'exam' then act_day end) as act_days_2021_exam,
count(distinct case when year(act_time) = 2021 and tag = 'question' then act_day end) as act_days_2021_question
from user_info u_i
left join (select uid,
start_time as act_time,
date_format(start_time, '%Y%m') as act_month,
date_format(start_time, '%Y%m%d') as act_day,
'exam' as tag
from exam_record
union all
select uid,
submit_time as act_time,
date_format(submit_time, '%Y%m') as act_month,
date_format(submit_time, '%Y%m%d') as act_day,
'question' as tag
from practice_record
) exam_and_practice
on exam_and_practice.uid = u_i.uid
where u_i.level >= 6
group by uid
order by act_month_total desc, act_days_2021 desc
这里的as tag可以为这个临时表起一个别名，可以之后在select里使用
SQL136 每类试卷得分前3名
这里先想到union，但又看了看，是用group by,这里统计的实际是得分排名，uid如果也跟在select后，就会提示报错，可以把uid写在group by后面
select ei.tag ,uid
from examination_info ei left join exam_record er
on ei.exam_id = er.exam_id
group by ei.tag,uid
结果
之后要进行排序筛选,这里直接使用order by score会提示不在full_group by
之后查到了一个函数，ROW_NUMBER() OVER()，用法是这样的
select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t
它的功能就是分组排序，生成rank，用在原sql里后如下
select ei.tag ,uid, row_number() over(partition by ei.tag order by max(er.score) desc,min(er.score) desc,uid desc)
from examination_info ei left join exam_record er
on ei.exam_id = er.exam_id
group by ei.tag,uid
查询结果
之后筛选一下就好，这里用一个嵌套查询最简单
select * from (select ei.tag as tid,uid, row_number() over(partition by ei.tag order by max(er.score) desc,min(er.score) desc,uid desc) as ranking
from examination_info ei left join exam_record er
on ei.exam_id = er.exam_id
group by ei.tag,uid) t1 where t1.ranking <4
SQL137 第二快/慢用时之差大于试卷时长一半的试卷
这道题首先可以看出要用group by exam_id,然后要用having筛选duration小于这道卷的作答时间的第二快和慢之差，这个考虑放在子查询中
SQL153 修复串列了的记录
这道题要求首先找出然后拆分，那么应该用逗号作为分隔符，有逗号的就是错误录入的，这里一开始想成了要修改，复杂了，这里是查出就行,如下图为拆分字符串方法的规则
select exam_id,substring_index(tag,",",1) as tag,substring_index(substring_index(tag,",",2),",",-1) as difficulty,substring_index(tag,",",-1) as duration from examination_info where tag like"%,%"
后记
后续在这里发布
1747
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
