2021年11月每天的人均浏览文章时长
with t1 as (select uid,date(in_time) as dt,timestampdiff(second,in_time,out_time) as time
from tb_user_log
where in_time like'2021-11%' and artical_id!=0)
select dt,round(sum(time)/(count(distinct(uid))),1) as avg_viiew_len_sec
from t1
group by dt
order by avg_viiew_len_sec
每篇文章同一时刻最大在看人数
思路:
①将用户的进入时间单独拎出来,同时记为1;离开时间单独拎出来,同时记为-1,这样就聚合这两个表,按照时间排序,意思就是:进去一个加1,离开一个减1。
②然后利用窗口函数对计数(1或者-1)求累计和,因为题目规定:同一时间有就有出的话先算进来的后算出去的,所以排序的时候就要看好了先按时间排序,再按计数排序!
③然后再在每个分组里面去求最大的累积和就是最多同时在线的人数了!
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 (
select artical_id,in_time dt,1 num from tb_user_log
where artical_id != 0
union all
(select artical_id,out_time dt,-1 num from tb_user_log
where artical_id !=0)) a
)b
group by artical_id
order by max_uv desc
2021年11月每天新用户的次日留存率
思路:
- 建立临时表t1,按照用户分组找出每个用户的最早登入时间;
- 建立临时表t2,按照用户分组找出每个用户的活跃期间,对登入和登出时间进行union all
- 建立临时表t3,将t1和t2进行左连接,条件是用户ID相等并且t1的时间=t2的时间-1
- 按照时间进行分组,计算留存率
with t1 as(select uid,min(date(in_time)) as dt from tb_user_log group by uid ), t2 as (select uid,date(in_time) as dt from tb_user_log union (select uid,date(out_time) as dt from tb_user_log order by uid)) ,t3 as(select t1.uid as uid1,t1.dt as dt1, t2.uid as uid2,t2.dt as dt2 from t1 left join t2 on t1.uid=t2.uid and t1.dt=date_sub(t2.dt,interval 1 day)) select * from t3 select dt1,round(count(uid2)/count(uid1),2) as uv_left_rate from t3 where dt1 like'2021-11%' group by dt1
统计活跃间隔对用户等级的结果
思路:
- 建立临时表t1找出每个用户的最早登入时间和最晚登出时间
- 建立临时表t2计算每个用户最早登入时间距今天的天数val1,最晚登出时间距今天的天数val2
- 建立临时表t3对用户进行评级
- 计算比率
with t1 as (select uid,date(min(in_time))as first_time ,date(max(out_time))as dt from tb_user_log group by uid) ,t2 as (select uid, datediff((select date(max(out_time)) from tb_user_log),first_time) as val1,datediff((select date(max(out_time)) from tb_user_log),dt) as val2 from t1) ,t3 as (select uid,(case when val1=val2 and val1<7 then '新晋用户' when val1>=7 and val2<7 then '忠实用户' when val2>=30 then '流失用户' else '沉睡用户' end) as user_grade from t2) select user_grade,round(count(*)/(select count(*)from t3),2) as ratio from t3 group by user_grade order by ratio desc
每天的日活数及新用户占比
思路:
- 建立临时表t1,找出每个用户的最早登陆时间
- 建立临时表t2,找出每个用户的活跃时间
- 建立临时表t3,按照日期进行分组找到每天的日活数
- 建立临时表t4,按照首次登陆时间进行分组找到每天的新用户数据
- 计算每天的新用户占比
with t1 as(select uid,min(date(in_time)) as first_time from tb_user_log group by uid) ,t2 as(select distinct uid,dt from( select uid,date(in_time) as dt from tb_user_log union all (select uid,date(out_time)as dt from tb_user_log)) a) ,t3 as(select dt,count(uid) as dau from t2 group by dt) ,t4 as(select first_time,count(uid) as new_count from t1 group by 1) select t3.dt,t3.dau,ifnull((round((t4.new_count/t3.dau),2)),0.00) as uv_new_ratio from t3 left join t4 on t3.dt=t4.first_time order by dt
连续签到领金币
思路:见连续问题解题思路-CSDN博客