【场景】：截止当前 【分类】：窗口函数、sum 分析思路 难点： 1.截止当前 （1）统计每个用户的播放量、加粉量、掉粉量 [条件]：year(start_time) = 2021 [使用]：group by author,month；count （2）计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 当月的总粉丝量 = (上个月)(加粉量 - 掉粉量) + 这个月(加粉量 - 掉粉量) 需要用到聚合窗口函数 [使用]：sum(follow_add-follow_sub) over(partition by author order by month) 求解代码 ...

【场景】：近一个月 【分类】：日期函数、date_sub 分析思路 难点： 1.近一个月的处理 （1）统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据） 找到10月2日这个截至日期 [使用]：date_sub(start_time,interval 30 day) （2）转发次数和播放记录 [条件]：start_time > NewDate [使用]：count 扩展 前往查看：*********** 求解代码 方法一： with 子句 # 统计在有用户互动的最近一个月（按包含当天在内的近30天算，比...

【场景】：播放进度 【分类】：字符串拼接、concat、日期函数 分析思路 难点： 1.百分比处理 （1）统计每条视频的播放时间 [使用]：timestampdifftime(second,start_time,end_time) （2）利用if函数计算每条视频的播放率，再利用avg()函数计算各类视频的平均完播率 找到进度大于60%的类别 [条件]：avg(if(times <= d,times/d*100,100)) > 60 [使用]：concat(round(avg(if(times <= d,times/d*100,100)),2),'%') 扩...

【场景】：平均 【分类】：日期函数、timestampdiff、select if 分析思路 难点： 1.判断是否完整播放视频，用到日期函数 做这种题的时候，如果要用到2个count，分别测试count列的值，再做除法 判断是否是2021年的播放记录；判断是否完整播放视频 [条件]：if(timestampdiff(second,start_time,end_time)>= duration,1,null)；where year(start_time) = 2021 [使用]：group by video_id 求解代码 方法一： select video_id...

【场景】：大小写转换 【分类】：字符串函数、upper() 分析思路 难点： 1.相同表做连接搜索不同的值 （1）统计被作答的试卷的类型和作答次数 （2）筛选出试卷作答数小于3的类别tag，统计将其转换为大写后对应的原本试卷作答数 [条件]：where a.answer_cnt <3 and upper(a.tag)!= a.tag。 [使用]：join 连接条件：upper(a.tag) = b.tag。需要查询出转换大写后的作答次数，如果转换后tag并没有发生变化，不输出该条结果。 扩展： 前往查看：*********** 求解代码 方法一： 表与自身做连接 + up...

【场景】：字符串长度 【分类】：字符串函数、char_length() 分析思路 难点： 1.如何计算字符串的长度 扩展： 前往查看：MySQL 字符串长度 char_length、length 求解代码 方法一： 使用 char_length() # 用于统计字符串的个数 select uid, if(char_length(nick_name) > 13,concat(substring(nick_name,1, 10),'...'),nick_name) as nick_name from user_info where char_length(nick_nam...

【场景】：截取字符串 【分类】：字符串函数、按分隔符截取 分析思路 难点： 1.按分隔符 嵌套截取 扩展： 前往查看：MySQL 字符串截取 求解代码 方法一： 使用like 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...

【场景】：前几条数据 【分类】：限量查询、limit 分析思路 难点： 1.取数据集中的某段数据使用limit函数 扩展： 前往查看：MySQL limit函数 限量查询 求解代码 方法一： select uid, nick_name, register_time from user_info order by register_time limit 3

【场景】：模糊查询、在...之间 【分类】：比较运算符、like、between 分析思路 难点： 1.最近一次活跃（答题或作答试卷）在2021年9月的用户信息 条件 需要查询出每个用户最近活跃（答题或作答试卷）的日期才能判断这个日期是否在2021年9月 （1）合并用户答题或作答试卷记录 [使用]：union （2）统计用户最近一次活跃记录 [条件]：max(time) as max_time ... group by uid （3）找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息 最近一次活跃（答题或作答...

【场景】：存在空值 【分类】：空值处理、if 函数、ifnull函数 分析思路 难点： 1.如何处理未完成的默认试卷最大考试时长和0分处理？ ifnull(expr1,expr2）: 如果expr1不为空结果为expr1，expr1为空结果为expr2 if(expr1 is null, expe1,expr2） （1）先对空值做处理 未完成的默认试卷最大考试时长和0分处理 [使用]：ifnull(timestampdiff(minute,start_time,submit_time),duration) [使用]：ifnull(score,0) （2）统计每个0级用户所有的高难度试卷...

【场景】：次日留存率 【分类】：时间函数、日期相差一天 分析思路 难点： 1.计算平均次日留存率，需要知道第一天和第二天的用户数量 题解中写出了3种得到第二天日期的方法 使用 date_add(expr, interval 1 day) 使用 date_sub(expr, interval 1 day) 使用 datediff(expr1,expr2) 使用 timestampdiff(day,expr1,expr2) 扩展 前往查看：MySQL 日期函数、时间函数在实际场景中的应用 求解代码 方法一： 使用 date_add(expr, interval 1 day) select ...

【场景】：时间函数 【分类】：时间函数、年月、日 分析思路 难点： 1.如何得到2021年8月 2.如何得到日 题解中写出了4种得到20221年8月和日的方法 使用year、month、day 使用date_format 使用substring 使用like 扩展 前往查看：MySQL 日期函数、时间函数在实际场景中的应用 求解代码 方法一： 使用year、month、day select day(date) as day, count(device_id) as question_cnt from question_practice_detail where mont...

【场景】：多个条件 【分类】：条件函数、if嵌套 分析思路 难点： 1.如何根据条件输出内容 一个条件2个结果可以使用if、case when；多个条件多个结果可以使用case when，还有if嵌套 （1）分别查看这两个年龄段用户数量 [使用]：if()或者case when；group by 扩展 前往查看：MySQL 条件函数 case when、if、ifnull 求解代码 方法一： 条件函数：if() select device_id, gender, if(age <20, '20岁以下', if(age >=25, '25...

【场景】：查找A或B 【分类】：行合并不去重 分析思路 难点： 1.先输出学校为山东大学的用户，后输出性别男的用户 2.如何不去重，使用union all 而不是where or ( select 查询结果 [试卷ID;性别;年龄;gpa] from 从哪张表中查询数据[用户信息表] where 查询条件 [山东大学] ) union all ( select 查询结果 [试卷ID;性别;年龄;gpa] from 从哪张表中查询数据[用户信息表] where 查询条件 [男性] ) 求解代码 方法一： union all (#学校为山东大学 select device_id,...