代码挺长的, 具体我一段段说明一下  子查询1: 注意: 需要尽量避免在select ( ) from table1括号中嵌套子查询, 因为外层表table1中, 每有一行, 都会执行一次内部的子查询, 这里有两种解决办法, 一种是用with as 提前将max(out_time) 保存为变量, 然后在查询的时候直接使用该变量, 方法二: 窗口函数的妙用, 窗口函数的执行顺序在group by之后, 可以对分组后的最大值, 再取最大, 此时获取到: ul.uid, 第一次登录日期, 今天(最大日期) select ul.uid, min(date(ul.in_time)) as f_login...

留存率的计算可以说是数据分析师, 或者数据开发必备的技能了, 很多公司都会用到这个指标, 该题还有进阶版的: 计算次日至7日内的新增用户留存率  思路: 根据uid分组, 取min(in_time)最小值, 得到用户的首次登录日期, 作为衍生表t1 select ul.uid, min(date(ul.in_time)) min_date from tb_user_log ul group by ul.uid 因为题目要求, out_time跨天也算该日活跃, 那么第二个子查询, 只需要关键字, uid, in_time, out_time即可, 这里其实可以直接用衍生表t1的uid左连接tb...

考查编码和窗口函数的巧妙使用  具体思路: 根据观看进入时间, 和退出时间进行编码,  select ul.artical_id, ul.in_time as view_time, 1 as `code` from tb_user_log ul where ul.artical_id != 0 # 这里需要使用union all, 可能会存在同一个视频观看时间相同的情况 select ul.artical_id, ul.out_time as view_time, -1 as `code` from tb_user_log ul where ul.artical_id != 0  可得: 每个...

最近一个月发布视频中热度最高  难点:  新鲜度: 1 / (最近无播放天数 + 1), 首先要获取到当前时间, 也就是 tb_user_video_log 的最大播放时间; 具体思路: 根据 video_id 进行分组, 取到的 max(end_time) 只是对应 video_id 的 max 时间, 而不是当前时间, 此时就可以用到max() over() 窗口函数了, 窗口函数执行顺序在 group by 之后, 对 group by 后的 max(end_time) 再 max(max(ul.end_time)) over(), 如下即可  datediff(max(max(ul.e...

题解: 如注释, 考察窗口函数的使用, 以及对sql执行顺序的理解   需要注意的是: rows需要往前推6行(题目给出的条件是前7天都是连续的) 第一次查询可以将时间限定为10.1往前推7天,以减少数据扫描select * from ( select vi.tag, date(ul.start_time) as start_time, # 注意: 窗口函数和groupby可以同时使用, 但是窗口函数执行顺序在groupby之后 # rows 3 preceding窗口向前便宜多少行 sum(sum(ul.if_like)) over(partition by vi.tag order by ...

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量  截止当月的总粉丝量, 虽然在在where 条件中过滤2021年的数据, 但是在2021年之前如果有粉丝的话, 那么该计算方式就是只能计算2021年截止当月的累计粉丝量 窗口函数sum() over()的使用, partition by 表示对哪个分组, order by中表示对该列累加 该题考查对执行顺序的理解, 是先执行group by 再执行窗口函数的, 这点要注意   select vi.author, date_format(ul.start_time, '%Y-%m') as month, round(sum( case ...

关于本题, 其实没有啥难度, 关键在于他对近一个月的转发量, 中一个月定义不清晰 开始我想的是 start_time >= date_sub(now(), interval 30 day), 但是一看测试用例发现行不通, 必须以题目提供的最大观看时间往前推一个月 select vi.tag, round(sum(ul.if_retweet), 3) as retweet_cut, round(sum(ul.if_retweet) / count(1), 3) as retweet_rate from tb_user_video_log ul left join tb_video_info...

平均播放进度大于60%的视频类别:  开始想了半天, 代码也写出来了, 但是测试用例死活就是通过不了  select vi.tag, concat(round(avg( if((vl.end_time - vl.start_time) > vi.duration, 1, (vl.end_time - vl.start_time)/vi.duration) ) * 100, 2), '%') as avg_play_progress from tb_user_video_log vl join tb_video_info vi on vl.video_id=vi.video_id grou...

1. 注意点: timestampdiff(format, start, end) format为second, day, hour...都行, 2. 直接时间相减, 个人感觉不太严谨. 尽量避免直接使用时间加减, 容易出问题 select uv.video_id, round(sum(if(timestampdiff(second, uv.start_time, uv.end_time) >= vi.duration, 1, 0)) / count(1), 3) as avg_comp_play_rate from tb_user_video_log uv join tb_video_...

浙江大学不同难度题目正确率  分析: 浙江大学, 不同难度, 正确率;  题目只需要浙江大学的, 所以可以连表后直接在where中进行过滤, 不同难度正确率,根据难度分组, 该难度答对的题数/该难度总题数即可   if(qpd.result='right', 1, 0)这样其实代码更简洁, 但是个人更习惯用case when 语法  select qd.difficult_level, sum(case when qpd.result = 'right' then 1 else 0 end) / count(1) as correct_rate from question_practice_d...

刷个sql放松一哈

题意: 统计复旦8月练题情况  注意点: 如果直接用left join question_practice_detail 后在where中进行过滤month(qpd.date)=8, 则学生信息中,只要8月份没有答题的也会被过滤掉 可以使用 left join question_practice_detail qpd on up.device_id=qpd.device_id and month(qpd.date)=8, 这样会先过滤b表8月份有答题的, 而不会把第一张表中8月份无答题的过滤掉 case when 用法万能, 是我的最爱, 但是这个连接是全连接, 效率会低点select up....

常规题 需要注意的地方: 只能用 question_practice_detail 连接 user_profile 表, 因为存在用户表不一定有答题记录的情况 select up.university, count(1) / count(distinct qpd.device_id) as avg_answer_cnt from question_practice_detail qpd left join user_profile up on up.device_id=qpd.device_id group by up.university order by up.university asc...

因为涉及到SQL优化的问题, 所以具体列出来   方法一: 先用user_profile连接question_practice_detail然后再where进行过滤, 最后排序即可   select      up.device_id,     qpd.question_id,     qpd.result from user_profile up left join question_practice_detail...