题解 167 | #连续签到领金币#

【场景】：连续签到、连续的表示

【分类】：窗口函数、date(in_time) - row_number()

分析思路

难点：

1.如何表示连接？ 连续的表示： 日期减去排序的值相等

2.如何根据断签来分组？基于连续的表示，值相等就可以分组！

新学到：

连续的表示： 日期减去排序的值相等。

金币累计计算： 排序后取余

（1）统计用户签到的月份、日期、按用户分组对日期排序、日期减去排序的值

注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。所以只计算 in_time；

连续的表示： 日期减去排序的值相等；

• [使用]：date(in_time) - row_number()

（2）统计每一行根据用户、日期减去排序的值分组排序的值

• [使用]：row_number()

（3）根据排序结果计算签到积分

注：满足连续签到3天的条件 额外加2；满足连续签到7天条件 额外加6；

每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）：取余的手段；

• [条件]：只有artical_id为0时sign_in值才有效

• [使用]：group by 分组条件 [日期]；order by 对查询结果排序 [日期升序];

扩展

前往查看： MySQL 连续记录 场景分析

求解代码

方法一：

with
    main as(
        #统计用户签到的月份、日期、按用户分组对日期排序、日期减去排序的值
        select
            uid,
            date_format(in_time,'%Y%m') as month,
            date(in_time) as dt,
            row_number() over (partition by uid order by in_time) as drk,
            date(in_time) - row_number() over (partition by uid order by in_time) as dt_drk
        from tb_user_log
        where artical_id = 0
        and sign_in = 1
        and date(in_time) between '2021-07-07' and '2021-10-31'
    )
    ,main1 as(
        #统计每个用户每月每个连续签到的积分
        select
            uid,
            month,
            dt_drk,
            count(*)+floor(count(*)/7)*(2+6)+floor(count(*)%7/3)*2 as num_coin
        from main
        group by uid,month,dt_drk
    )
#统计每个用户每月连续签到的积分
select
    uid,
    month,
    sum(num_coin) as coin
from main1
group by uid,month
order by month,uid

main表的查询结果：

1	101|202107|2021-07-07|1|20210706	
2	101|202107|2021-07-08|2|20210706	
3	101|202107|2021-07-09|3|20210706		
4	101|202107|2021-07-10|4|20210706		
5	101|202107|2021-07-11|5|20210706		
6	101|202107|2021-07-12|6|20210706		
7	101|202107|2021-07-13|7|20210706		
8	102|202110|2021-10-01|1|20211000		
9	102|202110|2021-10-02|2|20211000		
10	102|202110|2021-10-03|3|20211000		
11	102|202110|2021-10-05|4|20211001		
12	102|202110|2021-10-06|5|20211001		

main1表的查询结果:

1	101|202107|2021-07-07|1|20210706|1
2	101|202107|2021-07-08|2|20210706|2	
3	101|202107|2021-07-09|3|20210706|3		
4	101|202107|2021-07-10|4|20210706|4		
5	101|202107|2021-07-11|5|20210706|5		
6	101|202107|2021-07-12|6|20210706|6		
7	101|202107|2021-07-13|7|20210706|7		
8	102|202110|2021-10-01|1|20211000|1		
9	102|202110|2021-10-02|2|20211000|2		
10	102|202110|2021-10-03|3|20211000|3		
11	102|202110|2021-10-05|4|20211001|1		
12	102|202110|2021-10-06|5|20211001|2		

方法二：

优化方法

with
    main as(
        #统计用户签到的月份、日期、按用户分组对日期排序、日期减去排序的值
        select
            uid,
            date_format(in_time,'%Y%m') as month,
            date(in_time) as dt,
            row_number() over (partition by uid order by in_time) as drk,
            date(in_time) - row_number() over (partition by uid order by in_time) as dt_drk
        from tb_user_log
        where artical_id = 0
        and sign_in = 1
        and date(in_time) between '2021-07-07' and '2021-10-31'
    )
    ,main1 as(
        #统计每一行根据用户、日期减去排序的值分组排序的值
        select
            *,
            row_number() over(partition by uid,dt_drk order by dt) as rk
        from main
    )
#对每一行取余来判断是否额外加2或者额外加6；取余也解决了7天之后重置的情况
select
    uid,
    month,
    sum(case 
            when rk%7=0 then 7
            when rk%7=3 then 3
            else 1
        end) as coin
from main1
group by uid,month
order by month,uid