陈皮和白糖

分类

/ 注册

全部文章（共10篇）

题解 | #计算商城中2021年每月的GMV#

select date_format(event_time, '%Y-%m') as month, sum(total_amount) as GMV from tb_order_overall where status != 2 group by 1 having substr(month,1,4)...

2023-09-06

0 161

题解 | #每天的日活数及新用户占比#

with t1 as ( select uid , date_format(in_time,'%Y-%m-%d') as time from tb_user_log union -- 去重 select uid , date_format(out_time,'%Y-%m-%...

2023-09-05

0 217

题解 | #统计活跃间隔对用户分级结果#

with t1 as ( select uid, date_format(in_time, '%Y-%m-%d') as in_time, date_format(max(in_time) over (), '%Y-%m-%d') a...

2023-09-05

0 177

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

with t1 as ( select uid,substr(in_time,1,10) as dt, (unix_timestamp(out_time) - unix_timestamp(in_time)) as toltime from tb_user_l...

2023-08-17

0 142

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

with tt1 as( select tag,concat_ws('',substr(round(sum(guankanshichang)/sum(duration),4)*100,1,5),'%') as avg_play_progress from (select ...

2023-08-16

0 154

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

with tt1 as ( select t1.video_id, t1.start_time, t1.end_time, t2.duration, t2....

2023-08-16

0 208

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

with t1 as ( select substr(t1.start_time,1,10) as dt, t2.tag, sum(if_like) as like_cnt, ...

2023-08-16

0 178

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

with t1 as (select author, substr(end_time, 1, 7) as mon, count(if(if_follow = 1, 1, null)) as addfen...

2023-08-16

0 135

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

select t2.tag, sum(if_retweet) as retweet_cut, round(sum(if_retweet) / count(1), 3) as retweet_rate from tb_user_video_log t1 join...

2023-08-16

0 157

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

with tt1 as ( select t1.video_id, t1.start_time, t1.end_time, t1.if_like, t1.if_retweet, t1...

2023-08-16

3 253