连续签到领金币

明确题意：

计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币），结果按月份、ID升序排序。

问题分解：

  • 计算每个人连续签到的天次（生成子表t_sign_idx）：
    • 计算每次签到的序号和连续签到起始日期（生成子表t_sign_base）：
      • 计算每次签到的序号（生成子表t_sign_date_rn）：
        • 过滤出活动期内的有效签到记录：
          • 有效签到：artical_id = 0 and sign_in = 1
          • 活动期：DATE(in_time) >= "2021-07-07" and DATE(in_time) <= "2021-10-31"
        • 截取签到日期：DATE(in_time) as sign_dt
        • 每次签到序号（按天，相同日期的序号一样）：DENSE_RANK() over(partition by uid order by DATE(in_time)) as rn
        • 去重，每天的多次签到只保留一条：SELECT DISTINCT uid, sign_dt, rn
      • 计算当前签到往前连续的起始日期：DATE_SUB(sign_dt, INTERVAL rn DAY) as base_dt
    • 定义窗口，按每个用户每次起始日期分区，按签到日期排序：window wd_uid_dt as (partition by uid, base_dt order by sign_dt)
    • 计算当前签到是一个签到周期内（7天一个周期）的连续签到的天序：(ROW_NUMBER() over(wd_uid_dt) - 1) % 7 as sign_idx
  • 按用户和月份分组：group by uid, DATE_FORMAT(sign_dt, "%Y%m")
  • 计算每个用户每月获取的金币：
    • 第3天和第7天单独奖励，其他为1：case when sign_idx=6 then 7 when sign_idx=2 then 3 else 1 end
    • 计算总金币，求和：sum()

细节问题：

  • 表头重命名：as
  • 按月份、ID升序排序：order by month, uid;

完整代码：

select uid, DATE_FORMAT(sign_dt, "%Y%m") as `month`,
    sum(case when sign_idx=6 then 7 when sign_idx=2 then 3 else 1 end) as coin
from (
    select uid, sign_dt,
        (ROW_NUMBER() over(wd_uid_dt) - 1) % 7 as sign_idx
    from (
        select uid, sign_dt, rn, DATE_SUB(sign_dt, INTERVAL rn DAY) as base_dt
        from (
            select DISTINCT uid, DATE(in_time) as sign_dt,
                DENSE_RANK() over(partition by uid order by DATE(in_time)) as rn
            from tb_user_log
            where artical_id = 0 and sign_in = 1 
                and DATE(in_time) >= "2021-07-07" and DATE(in_time) <= "2021-10-31"
        ) as t_sign_date_rn
    ) as t_sign_base
    window wd_uid_dt as (partition by uid, base_dt order by sign_dt)
) as t_sign_idx
group by uid, `month`
order by `month`, uid;