牛客749132389号
牛客749132389号
全部文章
题解
归档
标签
去牛客网
登录
/
注册
牛客749132389号的博客
全部文章
/ 题解
(共7篇)
题解 | #2021年11月每天的人均浏览文章时长#
SELECT date(in_time) dt, ROUND( sum(TIMESTAMPDIFF(second,in_time,out_time))/count(DISTINCT uid), 1 ) avg_viiew_len_sec from tb_user_log WHERE DA...
Mysql
2022-06-29
0
142
题解 | #国庆期间每类视频点赞量和转发量#
with temp1 as (SELECT '2021-10-01' dt, DATE_FORMAT(start_time,'%Y-%m') date,tag,sum(if_like) sum_like_cnt_7d, SUM(if_retweet) as max_retweet_cnt_7d fr...
Mysql
2022-06-27
0
140
题解 | #每类视频近一个月的转发量/率#
SELECT tag,SUM(a.if_retweet) as retweet_cut, ROUND(SUM(a.if_retweet)/count(start_time),3) as retweet_rate from tb_user_video_log a JOIN tb_video_info ...
Mysql
2022-06-25
0
135
题解 | #每类视频近一个月的转发量/率#
with temp1 as ( select a.*,b.tag from tb_user_video_log a INNER JOIN tb_video_info b USING(video_id) ), temp2 as ( selec...
Mysql
2022-06-23
0
170
题解 | #平均播放进度大于60%的视频类别#
SELECT tag, CONCAT(ROUND(avg(IF(TIMESTAMPDIFF(second,start_time,end_time)>duration,1,TIMESTAMPDIFF(second,start_time,end_time)/duration))*100...
Mysql
2022-06-22
0
182
题解 | #平均播放进度大于60%的视频类别#
with temp1 as (SELECT tag,video_id,if(TIMESTAMPDIFF(second,start_time,end_time)>=duration,duration,TIMESTAMPDIFF(second,start_time,end_time)) as ga...
Mysql
2022-06-22
0
185
题解 | #各个视频的平均完播率#
with totalcount as ( SELECT video_id, count() as totalcount from tb_user_video_log WHERE EXTRACT(YEAR from start_time) = '2021' GROUP BY video_id ), ...
Mysql
2022-06-21
0
172