# 最近无播放天数=最近播放日期-视频的最近播放日期 # 先近一个月获取每个视频的视频完播率、点赞数、评论数、转发数、视频最近播放日期、最大播放日期等信息作为临时表A WITH A AS( select a.video_id ,avg(if(timestampdiff(second,start_time ,end_time) >= duration ,1,0)) as all_rate -- 完播率 ,sum(if_like) as like_cnt -- 点赞数 ,count(comment_id) as comment_cnt -- 评论数 ,sum(if_retweet) as r...

-- 统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。 -- 3、筛选出题目要求的国庆头3天对应的数据 select * from ( -- 2、计算每类视频每天对应的近7天的点赞总量以及最大转发量 select tag ,dt ,sum(like_cnt) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d ,max(retweet_cnt) over(partit...

-- 计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 -- 涨粉率=(加粉量 - 掉粉量) / 播放量 -- 计算涨粉率和总粉丝数量 select author ,month ,round(fans_cnt/play_cnt,3) as fans_growth_rate ,sum(fans_cnt) over(partition by author order by month) as total_fans from ( -- 1、先获取每个作者每个月的播放量、涨粉量 select author ,left(start_time,7) as month ,count(1) as p...

-- 统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）。 -- 转发率＝转发量÷播放量。结果按转发率降序排序。 select tag ,sum(if_retweet) as retweet_cut ,round(sum(if_retweet)/count(start_time),3) as retweet_rate from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id where ...

-- 计算各类视频的平均播放进度，将进度大于60%的类别输出 -- 播放进入= 播放时长/视频时长*100% ，当播放时长大于视频时长时，播放进入记录为100% -- 保留两位小数 select tag ,concat(round(avg(if(timestampdiff(second,start_time,end_time) > duration,1,timestampdiff(second,start_time,end_time)/duration))*100,2),'%') as avg_play_progress from tb_user_video_log a join tb_...

-- 2021年有播放记录的每个视频的完播率 -- 完播率=完成播放次数/总播放次数 select a.video_id ,round(sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0))/count(1),3) as avg_comp_play_rate from tb_user_video_log a inner join tb_video_info b on a.video_id = b.video_id where year(start_time) = '2021' group by a.video_...

-- 统计连续回答问题的天数大于等于3天的用户及其等级 select b.author_id ,author_level ,count(1) as day__cnt from ( -- 1、先获取回答情况表中每个用户对应的日期窗口排序后对应的日期 select author_id ,answer_date ,date_sub(answer_date,interval (rank() over(partition by author_id order by answer_date)) day) as date_2 from ( select author_id ,answer_date from...

select answer_date ,author_id ,count(issue_id) as answer_cnt from answer_tb group by answer_date ,author_id having count(issue_id) >= 3 order by answer_date ,author_id

-- 高质量回答情况表中对应的用户等级 select level_cut ,count(1) as num from ( select a.* ,(case when author_level <=2 then '1-2级' when author_level <=4 then '3-4级' else '5-6级' end) as level_cut from answer_tb a left join author_tb b on a.author_id = b.author_id where char_len >= 100 ) c group by level_cut o...

select answer_date ,round(count(issue_id)/count(distinct author_id),2) as 'per_num' from answer_tb where left(answer_date,7) = '2021-11' group by answer_date order by answer_date

-- 每个科目最大在线人数，意味着先统计进入，再统计退出，进入为1，退出为-1 select b.course_id ,course_name ,max(sum_cnt) as max_num from ( select course_id ,dt ,sum(cnt) over(partition by course_id order by dt) as sum_cnt from ( select user_id ,course_id ,in_datetime as dt ,1 as cnt from attend_tb union (select user_id ,course_id ,ou...

-- 每个科目的出勤率 出勤率=出勤（在线时长10分钟及以上）/包名人数 -- 需要注意存在同一用户同一课程多次进入的情况 -- 计算每个用户每门课对应的课程名称、报名人数及出勤记录，如果在线时长10分钟及以上就记为1，否则为0 select a.course_id ,course_name ,round(100*count(distinct if(timestampdiff(minute,in_datetime,out_datetime) >= 10 ,b.user_id,null))/count(distinct if(if_sign=1,a.user_id,null)),2) as...

select course_name ,round(avg(timestampdiff(minute,in_datetime,out_datetime)),2) as avg_Len from attend_tb b left join course_tb a on a.course_id = b.course_id group by course_name order by avg_Len desc

-- 直播开始时（19:00）的在线人数 = 在这之前进入的人数+在这个时点进入的人数 select b.course_id ,course_name ,count(distinct user_id) as online_num from attend_tb b left join course_tb a on b.course_id = a.course_id where right(in_datetime,8) <= '19:00:00' and right(out_datetime,8) >= '19:00:00' group by b.course_id ,course_n...