例题1:连续7日签到领金币
思路:首先,筛选出各用户符合条件的签到日期;其次,对签到表进行排序。使用窗口函数row_number(),以uid分组;按照date(in_time)排序;然后,求出各用户连续登录的天数。用dt-ranking,得到日期相同的为一组。此组数量则为连续签到天数,然后,再计算日期对应的连续登陆天数;就可以攻克每日领取的金币数量了,取7的余数
with t1 as (select uid,date(in_time) as dt from tb_user_log
where artical_id=0 and sign_in=1 and date(in_time)between '2021-07-07' and '2021-10-31')
,t2 as(select distinct(uid),dt,row_number()over(partition by uid order by dt) as ranking
from t1)
,t3 as(select uid,dt,ranking,date(dt-ranking) as dt2
from t2)
,t4 as (select *,row_number()over(partition by uid,dt2 order by dt) as ranking2
from t3)
#select * ,(case when ranking2%7=3 then 3 when ranking2%7=0 then 7 else 1 end)from t4
select uid,month,sum(coin) as coin from(
select uid,date_format(dt,'%Y%m') as month,(case when ranking2%7=3 then 3 when ranking2%7=0 then 7 else 1 end) as
coin
from t4
) a
group by uid,month
例题2:某乎问答最大连续回答问题的天数大于等于3天的用户及其等级
思路:建立临时表t1选出每个用户的ID以及回答问题的日期;建立临时表t2,使用row_number()函数对每个用户回答问题的日期进行排序;建立临时表t3,通过看回答问题的日期-排名是否想等判断是否连续
with t1 as(select distinct author_id,answer_date from answer_tb)
,t2 as(select *,row_number()over(partition by author_id order by answer_date) as rk
from t1)
,t3 as(select *,date_format((answer_date-rk),'%Y-%m-%d') as dt2 from t2)
select a.author_id,b.author_level,a.days_cnt from (
select author_id,count(dt2) as days_cnt from t3
group by author_id,dt2
having count(dt2)>=3) a left join author_tb b
on a.author_id=b.author_id
例题3:请你统计连续2天及以上在该店铺购物的用户及其对应的次数
思路同上
with t1 as(select distinct user_id,sales_date from sales_tb)
,t2 as(select *,row_number()over(partition by user_id order by sales_date) as rk from t1)
,t3 as(select *,(sales_date-rk) as dt2 from t2)
select user_id,count(dt2) as days_count
from t3
group by user_id,dt2
having count(dt2)>=2