路静

分类

/ 注册

全部文章（共49篇）

题解 | 每个月Top3的周杰伦歌曲

SELECT month, ranking, song_name, play_pv FROM ( SELECT song_name, MONTH(fdate) AS month, COUNT(song_id) AS play_pv, ROW_NUMBER() OVER...

2025-07-08

0 57

题解 | 每篇文章同一时刻最大在看人数

SELECT artical_id,max(cnt) AS max_uv FROM( SELECT artical_id,SUM(num) OVER (PARTITION BY artical_id ORDER BY dt ASC,num DESC) AS cnt FROM ( ...

2025-07-07

0 50

题解 | 2021年11月每天的人均浏览文章时长

SELECT dt,ROUND(SUM(view_len_sec)/COUNT(DISTINCT uid),1) AS avg_viiew_len_sec FROM ( SELECT uid,DATE(in_time) AS dt, TIMESTAMPDIFF(SECO...

2025-07-07

0 38

题解 | 近一个月发布的视频中热度最高的top3视频

SELECT video_id,ROUND((100*yes_watch_rate+5*like_cnt+3*com_cnt+2*ret_cnt)/(1+TIMESTAMPDIFF(DAY,recently_end_date,cur_date)),0) AS hot_index FROM (SEL...

2025-07-07

0 45

题解 | 国庆期间每类视频点赞量和转发量

SELECT tag, dt, sum_like_cnt_7d, max_retweet_cnt_7d FROM ( SELECT tag, DATE_FORMAT(start_time, '%Y-%m-%d') AS dt...

2025-07-05

0 52

题解 | 每个创作者每月的涨粉率及截止当前的总粉丝量

SELECT author,month,ROUND(fans_growth_cnt/play_cnt,3) AS fans_growth_rate,SUM(fans_growth_cnt) OVER (PARTITION BY author ORDER BY month) as total_fans...

2025-07-05

0 55

题解 | 每类视频近一个月的转发量/率

SELECT tag,SUM(if_retweet) AS retweet_cnt,ROUND(SUM(if_retweet)/COUNT(tag),3) AS retweet_rate FROM tb_user_video_log JOIN tb_video_info USING(video_id...

2025-07-04

0 46

题解 | 平均播放进度大于60%的视频类别

SELECT tag,CONCAT(ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>duration,100,TIMESTAMPDIFF(SECOND,start_time,end_time)/duration*100)),2) ,...

2025-07-04

0 46

题解 | 各个视频的平均完播率

SELECT video_id,ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0)),3) AS avg_comp_play_rate FROM tb_user_video_log JOIN tb_vide...

2025-07-04

0 46