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

# 1. 筛选出各用户符合条件的签到日期，得到<签到表>
SELECT * 
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1;

# 2. 对签到表进行排序。使用窗口函数row_number()
# 以uid分组
# 按照date(in_time)排序
SELECT 
    uid,
    date(in_time) dt,
    row_number() over (partition by uid order by date(in_time)) ranking 
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1;

# 3. 求出各用户连续登录的天数
#（1）用(dt-ranking)得到日期dt2，如果是连续签到，则dt2的日期相同
SELECT 
    uid,
    date(in_time) dt,
    row_number() over (partition by uid order by date(in_time)) ranking,
    date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2 
FROM tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1;

#（2）再来计算日期对应的连续签到天数
select 
    *,
    row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2  # 窗口的partition分组根据uid和差值日期dt2
FROM
    (
        SELECT 
            uid,
            date(in_time) dt,
            row_number() over (partition by uid order by date(in_time)) ranking,
            date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2
        FROM tb_user_log
        where date(in_time) between '2021-07-07' and '2021-10-31'
        and artical_id=0
        and sign_in=1
    )t1
;

#（3）有了连续签到天数ranking2，就可以攻克每日领取的金币数量了，取7的余数，用case when来判断范围。
select 
    *,
    row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2,
    case 
        when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=3 then 3
        when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=0 then 7
    else 1 end coin 
FROM
    (
        SELECT 
            uid,
            date(in_time) dt,
            row_number() over (partition by uid order by date(in_time)) ranking,
            date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2
        FROM tb_user_log
        where date(in_time) between '2021-07-07' and '2021-10-31'
        and artical_id=0
        and sign_in=1
    ) t1
;

# 4. 整理格式，按照uid，月份分组，求出对应的coin和。并且按照月份、ID升序排序
select 
    t2.uid,
    DATE_FORMAT(t2.dt,'%Y%m') month,
    sum(t2.coin) coin 
FROM
    (
    select 
        *,
        row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2,
        case 
            when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=3 then 3
            when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=0 then 7
        else 1 end coin 
    FROM
        (
            SELECT 
                uid,
                date(in_time) dt,
                row_number() over (partition by uid order by date(in_time)) ranking,
                date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2
            FROM tb_user_log
            where date(in_time) between '2021-07-07' and '2021-10-31'
            and artical_id=0
            and sign_in=1
        )t1
    )t2
group by t2.uid, DATE_FORMAT(t2.dt,'%Y%m')
order by t2.uid, DATE_FORMAT(t2.dt,'%Y%m')
;