用到的知识点：lead窗口函数，date_sub日期函数，case when end语句，取余是%，向下取整floor函数，group/having/order可以用别名step1:统计每个用户在2021年7月7日到2021年10月31日的的登录情况，并按照登录日期排序。with t1 as( select distinct uid ,date(in_time) as in_day ,date_format(in_time,"%Y%m") as in_month ,row_number() over(partition by uid order by date(in_tim...

做一次忘一次，这次终于整理好思路啦！先来看看知识点：滑动窗口函数：min(字段1) over(partition by 字段1 order by 字段2 rows between a and b） a取值：unbounded preceding（划分排序后前面所有行）/ n preceding / current row b取值：unbounded following / n following/ 0 following(=current row) 注意：1️⃣rows between n preceding and current row 可以简写为：rows n preceding 2️⃣...

select video_id, round((100*already_rate + 5*like_num + 3*comment_num + 2*retweet_num)/(1+last_no_day)) as hot_index from( select a.video_id as video_id, sum(if(watch_time>=duration,1,0))/count(*) as already_rate, sum(if_like) as like_num, sum(if_comment) as comment_num, sum(if_retweet) as retwee...

select author,imonth, round(sum( case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end )/count(*),3) as fans_growth_rate, sum(sum( case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end ) ) over(partition by author order by imonth) as total_fans from (select video_id, date_forma...

select uid,in_month, sum(case when days%7 in (1,2) then 15*floor(days/7)+days%7 when days%7 in(3,4,5,6) then 15*floor(days/7)+days%7+2 else 15*floor(days/7) end) as coin from( select uid,idate, min(date_format(in_day,'%Y%m')) as in_month, # 为了最后得到月份列 count(distinct in_day) as days # 连续登录日期 from( sel...

select act_day as dt, count(*) as dau, round(sum(if_new)/count(*),2) as uv_new_ratio from( select uid, date(in_time) as act_day, min(date(in_time)) over(partition by uid) as new_day, if(date(in_time)=min(date(in_time)) over(partition by uid) ,1,0) as if_new #当天新用户计为1，否则0 from tb_user_log union selec...

select user_grade,round(count(*)/total_num,2) as ratio from ( select uid,total_num, case when datediff(today,min(in_day))>6 and datediff(today,max(in_day))<6 then '忠实用户' when datediff(today,min(in_day))<6 then '新晋用户' when datediff(today,max(in_day))between 7 and 29 then '沉睡用户' else '流失用户' e...