找工作好难玉玉了😇 - 个人主页动态

2024-01-22 13:27

# 先找到有记录的，新增一个得分等级字段 # 再按用户等级和得分等级分组，统计占比 # 暴力法，考虑所有等级情况，分别计数 WITH temp_0 AS( SELECT uid, exam_id, score, level, # 使用case when语句为不同区间赋值 CASE WHEN score >= 90 THEN '优' WHEN score < 90 AND score >= 75 THEN '良' WHEN score < 75 AND score >= 60 THEN '中' ELSE '差' END score_grade, # 计算每个等级的用...

0 点赞评论收藏

2024-01-22 11:34

江苏大学 Python

题解 | #根据指定记录是否存在输出不同情况#

# 把 当有任意一个0级用户未完成试卷数大于2 作为一个条件，临时表，如果有，那统计数量是大于0的 # 总体上输出全部用户的指标，如果是第一种情况，就只筛选出等级为0的 # 统计是否有满足条件的 WITH temp_0 AS( SELECT uid FROM user_info LEFT JOIN exam_record USING(uid) WHERE level = 0 # 选择0级用户 GROUP BY uid HAVING SUM(IF(score, 0, 1)) > 2 # 未完成的记录数大于2 ) SELECT uid, # 未完成数计算，这里要判断是否有开始的记录，如果没有...

0 点赞评论收藏

2024-01-21 22:17

江苏大学 Python

题解 | #筛选昵称规则和试卷规则的作答记录#

# 找到昵称满足的，c开头的试卷， # 计算按用户和试卷分组的均分 SELECT uid, exam_id, ROUND(AVG(avg_score), 0) avg_score FROM ( # 设为子查询是因为不能在这里对其分组 SELECT uid, exam_id, AVG(score) OVER(PARTITION BY uid, exam_id) avg_score FROM exam_record a JOIN user_info b USING(uid) JOIN examination_info c USING(exam_id) WHERE (nick_name LIKE '...

0 点赞评论收藏

2024-01-21 21:37

江苏大学 Python

题解 | #筛选限定昵称成就值活跃日期的用户#

# 前两个条件可以从用户表得到，谓词、成就值 # 最后一个要筛选用户uid，新增一列是用户最近的一次活跃时间 # 临时表，左连接三个表，新增每个用户的试卷最新记录时间和练习题最新记录时间 WITH temp_0 AS( SELECT a.uid uid, #DATE_FORMAT(b.start_time, '%Y%m') exam_t, #DATE_FORMAT(c.submit_time, '%Y%m') ques_t, MAX(b.start_time) OVER(PARTITION BY uid) max_exam_t, MAX(c.submit_time) OVER(PARTITIO...

0 点赞评论收藏

2024-01-20 19:43

江苏大学 Python

题解 | #0级用户高难度试卷的平均用时和平均得分#

SELECT uid, # 计算均分，当有score的时候，就返回实际的，没有就是0 ROUND(AVG(IF(score, score, 0)), 0) avg_score, # 计算均用时，使用TIMESTAMPDIFF计算时间差，分钟为单位 ROUND(AVG(IF(score, TIMESTAMPDIFF(MINUTE, start_time, submit_time), duration)), 1) avg_time_took FROM exam_record c JOIN user_info a USING(uid) JOIN examination_info b USING(ex...

0 点赞评论收藏

2024-01-20 19:20

江苏大学 Python

题解 | #每月及截止当月的答题情况#

# 计算每月的月活用户数，其他都以此推算而来 # 难点在于新增用户，需要模仿矩阵 WITH temp_0 AS( SELECT uid, exam_id, DATE_FORMAT(start_time, "%Y%m") start_month, # 定义新增用户，将单个用户首次登录的时间定义为最初时间，只有等于该时间的才算是首次登录1，其他都是老用户登录 IF(start_time = MIN(start_time) OVER(PARTITION BY uid), 1, 0) first_time FROM exam_record ) # 查询，直接统计月活用户数，对每月的...

0 点赞评论收藏

2024-01-20 17:01

江苏大学 Python

题解 | #每份试卷每月作答数和截止当月的作答总数。#

# 先算出每个试卷的每月的做答数 # 再使用窗口函数进行累加 WITH temp_0 AS( SELECT exam_id, start_month, COUNT(start_month) month_cnt FROM ( SELECT exam_id, DATE_FORMAT(start_time, "%Y%m") start_month FROM exam_record ) b GROUP BY exam_id, start_month ) SELECT exam_id, start_month, month_cnt, SUM(month_cnt) OVER(PARTI...

0 点赞评论收藏

2024-01-20 16:39

江苏大学 Python

题解 | #对试卷得分做min-max归一化#

# 先对每个试卷进行分数的归一化计算，然后按用户和试卷进行分组，计算均值，只保留整数 WITH temp_0 AS( SELECT b.id, b.uid, a.exam_id, b.score, MAX(score) OVER(PARTITION BY exam_id) max_score, MIN(score) OVER(PARTITION BY exam_id) min_score FROM examination_info a JOIN exam_record b USING(exam_id) WHERE a.difficulty = 'hard' AND b.score IS NOT...

0 点赞评论收藏

2024-01-19 21:47

江苏大学 Python

题解 | #试卷完成数同比2020年的增长率及排名变化#

# 把21年和22年的先都放成示例的样子 # 临时表0，把各类试卷的20年和21年上半年的完成数提取出来 WITH temp_0 AS( SELECT tag, SUM(CASE WHEN DATE_FORMAT(submit_time, "%Y%m") BETWEEN 202001 AND 202006 THEN 1 ELSE 0 END) exam_cnt_20, SUM(CASE WHEN DATE_FORMAT(submit_time, "%Y%m") BETWEEN 202101 AND 202106 THEN 1 ELSE 0 END) ex...

0 点赞评论收藏

2024-01-18 16:21

江苏大学 Python

题解 | #未完成率较高的50%用户近三个月答卷情况#

# 先找到sql试卷未完成率较高的前一半用户，筛选出level是6、7的 # 这些用户的有作答记录的近三个月，分组统计每月的答卷数和完成数 WITH temp AS ( # 联结表 SELECT a.uid, c.start_time, c.score, a.level, b.tag FROM exam_record c LEFT JOIN user_info a USING(uid) LEFT JOIN examination_info b USING(exam_id) # WHERE b.tag = 'SQL' #AND a.level IN (6,7) ), # 找到较高的50%的用户，...

0 点赞评论收藏

2024-01-18 12:39

江苏大学 Python

题解 | #近三个月未完成试卷数为0的用户完成情况#

# 检查每个用户近三个月的作答月份， 再检查这些月份的作答数和完成数是否一致，不一致按完成数输出 WITH temp AS ( SELECT uid, DATE_FORMAT(start_time, "%Y-%m") start_month, submit_time, DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE_FORMAT(start_time, "%Y%m") DESC) AS month_rank FROM exam_record ) SELECT uid, COUNT(start_month...

0 点赞评论收藏

2024-01-15 16:05

江苏大学 Python

题解 | #连续两次作答试卷的最大时间窗#

SELECT uid, days_window, ROUND((total/max_diff) * days_window, 2) AS avg_exam_cnt FROM ( SELECT uid, COUNT(start_time) total, DATEDIFF(MAX(start_time), MIN(start_time))+1 max_diff, MAX(DATEDIFF(next_time, start_time)+1) AS days_window FROM ( SELECT uid, start_time, LEAD(start_time, 1) OVER(PARTITION...

0 点赞评论收藏

2024-01-15 13:27

江苏大学 Python

题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

# 计算时差 # 按时差排两个序，正序、倒序 # 链接分组查询 WITH TD AS ( SELECT exam_id, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS cost, duration, release_time, ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(SECOND, start_time, submit_time) DESC) AS slow, ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY T...

0 点赞评论收藏

2024-01-14 22:48

江苏大学 Python

题解 | #每类试卷得分前3名#

SELECT * FROM (SELECT a.tag tid, b.uid uid, ROW_NUMBER() OVER(PARTITION BY a.tag ORDER BY MAX(b.score) DESC, MIN(b.score) DESC, MAX(b.uid) DESC) ranking FROM examination_info a JOIN exam_record b USING(exam_id ) GROUP BY tid, uid) T HAVING ranking <= 3 # WHERE ranking <= 3 ROW_NUMBER() 唯一排名 1,...

0 点赞评论收藏

2024-01-12 17:20

江苏大学 Python

题解 | #每个6/7级用户活跃情况#

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 y...

0 点赞评论收藏

创作者周榜

关注他的用户也关注了：