旺w旺w旺z
旺w旺w旺z
全部文章
题解
归档
标签
去牛客网
登录
/
注册
旺w旺w旺z的博客
全部文章
/ 题解
(共6篇)
题解 | #各城市最大同时等车人数#
总代码 with t1 as ( SELECT city,type ,rank() over(partition by city order by event_time,type desc) as 'rnk' from ( SELECT city,ev...
Mysql
2022-02-22
0
191
题解 | #每天的日活数及新用户占比#
先计算各个日期的日活 with dau as ( SELECT dt,count(DISTINCT uid) as 'dau_num' from ( select uid,date(in_time) as 'dt' from tb_user_log ...
Mysql
2022-01-16
0
291
题解 | #统计活跃间隔对用户分级结果#
先计算各个用户的初次登录时间(first_day),最近登录时间(recent_day),以及总的计算下当前的日期(now) SELECT uid, (select date(max(out_time)) from tb_user_log) as 'now', ...
Mysql
2022-01-14
0
213
题解 | #国庆期间每类视频点赞量和转发量#
先存储25-3号每天的点赞转发情况(res): with res as ( select tag, DATE(start_time) as 'dt', sum(if_like) as 'likeNum', sum(if_ret...
Mysql
2022-01-12
5
475
题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
我先把各个作者各个月份的播放量(num),本月粉丝增加量(fans)存储为res: with res as ( select author, date_format(end_time,'%Y-%m') as 'month', count(1) ...
Mysql
2022-01-12
0
411
题解 | #各个视频的平均完播率#
SELECT a.video_id, round(sum((end_time-start_time-b.duration) >=0 )/count(a.video_id),3) avg_comp_play_rate FROM tb_user_video_log a left j...
Mysql
2022-01-12
0
259