场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。问题：统计2021年11月文每天的人均浏览章时长（秒数），结果保留1位小数，并按时长由短到长排序。问题切分筛选条件：2021年11月，文章内容页要求的是：每天的人均浏览章时长（秒数）隐藏条件：结果保留1位小数，并按时长由短到长排序重点问题怎北计算每天的人均浏览章时长（秒数）？group by 日期每天的人均浏览章时长=浏览总时长/总浏览人数浏览总时长=sum(timestampdiff(second,in_time,out_time)...

问题切分筛选条件：近一个月发布的视频要求的是：热度最高的top3视频隐藏条件：结果中热度保留为整数，并按热度降序排序重点问题什么是近一个月发布的视频？最近播放日期为2021-10-03，记作当天日期，即max(date(end_time))近一个月播放就是datediff(date(release_time),select max(date(end_time) from tb_user_video_log) <= 29热度怎么计算？热度=(100*视频完播率+5*点赞数+3*评论数+2*转发数)*新鲜度新鲜度=1/(最近无播放天数+1)视频完播率=视频完播数/视频播放数=sum(if(t...

问题切分筛选条件：2021年国庆头3天要求的是：每类视频每天的近一周总点赞量和一周内最大单天转发量隐藏条件：结果按视频类别降序、日期升序排序重点问题怎么计算每类视频每天的近一周总点赞量和一周内最大单天转发量？看到近一周想到滑动窗口函数先聚合每天的视频点赞总数和转发总数，再开窗进行计算，最后筛选出国庆的前三天整合代码select *from    (select        tag,        dt,        sum(like_cnt) over...

问题切分筛选条件：2021年要求的是：每个创作者每月的涨粉率及截止当月的总粉丝量隐藏条件：涨粉率保留3位小数，结果按创作者ID、总粉丝量升序排序重点问题怎么求每个创作者每月的涨粉率？首先group by author,substring(start_time,1,7)涨粉率=(加粉量 - 掉粉量) / 播放量加粉量-掉粉量：sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end)播放量：count(start_time)怎么求截止当月的总粉丝量？求累计想到聚合窗口函数，这里是先聚合到月，再开窗：sum(x...

问题切分筛选条件：有用户互动的最近一个月中要求的是：每类视频的转发量和转发率隐藏条件：转发率保留3位小数、转发率降序排序重点问题如何定义有用户互动的最近一个月？我个人的理解首先是有关注，点赞，转发，评论某一个或多个动作就是互动，其次是最近的互动视频的一个月内所以落实到代码，首先通过where筛选出if_follow=1 or if_like = 1 or if_retweet= 1 or comment_id != 'NULL'通过窗口函数对video_id进行分区，求出max(start_time)，即最近一次互动视频的日期最后通过子查询where筛选出日期在date_add(max(sta...

问题切分筛选条件：将进度大于60%的类别输出（先聚合求进度，再筛选，可以用having筛选)要求的是：各类视频的平均播放进度隐藏条件：结果保留两位小数、按播放进度倒序排序重点问题如何求各类视频的平均播放进度？播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。播放时长timestampdiff(second,start_time,end_time)视频时长duration需要注意的是如果播放时长大于视频时长时，播放进度均记为100%也就是if(timestampdiff(second,start_time,end_time)>=duration,1,...

问题切分筛选条件：2021年、有播放记录的视频要求的是：每个视频的完播率（按照视频分组，求各自完播率）隐藏条件：完播率结果保留三位小数、按完播率降序排序重点问题如何计算视频完播率？视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。完成播放次数结束观看时间与开始播放时间的差：timestampdiff(second,start_time,end_time)判断是否timestampdiff(second,start_time,end_time)>=duration如果是，记作完成播放，如果否，不记作完成播放sum(if...