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

题目：

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

难点：

• 连续签到问题，已经有很多题目考过了，不多赘述
• 每7天重新计算累计签到天数 —— 这里有两种计算方式来解决：1）根据每一段连续签到，得出总累积天数，再一次性计算总金币数（我用的是后者）；2）识别第3天、第7天，并赋予当天对应的金币数

方法 1：找出每个用户在每个月的每一段连续签到，再计算每一段对应的总金币数，最后再根据uid、月份来聚合

步骤 1：先找出每个用户在2021年7月7日 - 10月31日间，每一天的登录情况

SELECT DISTINCT uid, DATE(in_time) AS date,
                /* DENSE_RANK()用于后续匹配是否连续登录用 */
                DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE(in_time) ASC) AS rk
FROM tb_user_log
WHERE sign_in = 1 AND artical_id = 0 AND (DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31')

步骤 2：找出每个用户的每一段连续登录

SELECT uid, 
       /* 之所以是rk-1，是为了以连续登录的第一天为始点 */
       DATE_SUB(date, INTERVAL rk-1 DAY) AS start_date,
       COUNT(uid) AS days
FROM
    (SELECT DISTINCT uid, DATE(in_time) AS date,
                     DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE(in_time) ASC) AS rk
     FROM tb_user_log
     WHERE sign_in = 1 AND artical_id = 0 AND (DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31')) AS t1
GROUP BY uid, DATE_SUB(date, INTERVAL rk-1 DAY)

步骤 3：先根据每一段连续登录，计算其总金币数，再根据uid和月份聚合，得出每个用户在每个月的总金币数

• 当连续天数 <= 2天，总金币 = 天数
• 当连续天数 >= 3天，且 (连续天数 / 7) 的余数 <= 2，则总金币 = 天数 + 周数 X 2 + 周数 X 6
• 当连续天数 >= 3天，且 (连续天数 / 7) 的余数 >= 3，则总金币 = 天数 + (周数+1) X 2 + 周数 X 6
• 其中，周数 = 连续天数 / 7 所得的商的向下取整，即，FLOOR(连续天数 / 7)

  SELECT uid, DATE_FORMAT(start_date, '%Y%m') AS month,
         /* CASE WHEN用于计算每一段连续登录，应得的总金币数，最后结合SUM函数来聚合，
            得出每个用户在每个月的总金币数 */
         SUM(CASE WHEN days <= 2 THEN days
              WHEN days >= 3 AND MOD(days, 7) <= 2 THEN days + FLOOR(days / 7) * 2 + FLOOR(days / 7) * 6
              WHEN days >=3 AND MOD(days, 7) >= 3 THEN days + (FLOOR(days / 7) + 1) * 2 + FLOOR(days / 7) * 6
              ELSE NULL END) AS coin
  FROM
      (SELECT uid, DATE_SUB(date, INTERVAL rk-1 DAY) AS start_date, COUNT(uid) AS days
       FROM
           (SELECT DISTINCT uid, DATE(in_time) AS date,
                            DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE(in_time) ASC) AS rk
            FROM tb_user_log
            WHERE sign_in = 1 AND artical_id = 0 AND (DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31')) AS t1
       GROUP BY uid, DATE_SUB(date, INTERVAL rk-1 DAY)) AS t2
  GROUP BY uid, DATE_FORMAT(start_date, '%Y%m')
  ORDER BY month ASC, uid ASC;

方法 2：换个角度思考，当连续登录是第3天、第7天时，该两天可额外获得2金币、6金币，即，第3天和第7天，可以获得总共3金币和7金币，其余的天数皆是1金币

步骤 1：同样的，先找出每个用户在2021年7月7日 - 10月31日间，每一天的登录情况

SELECT DISTINCT uid, DATE(in_time) AS date,
                /* DENSE_RANK()用于后续匹配是否连续登录用 */
                DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE(in_time) ASC) AS rk
FROM tb_user_log
WHERE sign_in = 1 AND artical_id = 0 AND (DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31')

步骤 2：在找出每个用户的每一段连续登录后，根据uid，和连续登录的第一天来分组，对每一天登录再进行排序

• 连续登录的第3天、第7天，可得到总共3和7金币
• 对于其他连续登录的NO天数，即得1金币

  SELECT uid, DATE_SUB(date, INTERVAL rk-1 DAY) AS start_date, date,
         (CASE WHEN DENSE_RANK() OVER (PARTITION BY uid, DATE_SUB(date, INTERVAL rk-1 DAY) ORDER BY date ASC) % 7 = 3 THEN 3
               WHEN DENSE_RANK() OVER (PARTITION BY uid, DATE_SUB(date, INTERVAL rk-1 DAY) ORDER BY date ASC) % 7 = 0 THEN 7
               ELSE 1 END) AS coin
  FROM
      (SELECT DISTINCT uid, DATE(in_time) AS date,
                       DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE(in_time) ASC) AS rk
       FROM tb_user_log
       WHERE sign_in = 1 AND artical_id = 0 AND (DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31')) AS t1

步骤 3：根据uid和月份，使用SUM函数对所得金币进行聚合加总即可

SELECT uid, DATE_FORMAT(start_date, "%Y%m") AS month, SUM(coin) AS coins
FROM
    (SELECT uid, DATE_SUB(date, INTERVAL rk-1 DAY) AS start_date, date,
            (CASE WHEN DENSE_RANK() OVER (PARTITION BY uid, DATE_SUB(date, INTERVAL rk-1 DAY) ORDER BY date ASC) % 7 = 3 THEN 3
                WHEN DENSE_RANK() OVER (PARTITION BY uid, DATE_SUB(date, INTERVAL rk-1 DAY) ORDER BY date ASC) % 7 = 0 THEN 7
                ELSE 1 END) AS coin
     FROM
         (SELECT DISTINCT uid, DATE(in_time) AS date,
                          DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE(in_time) ASC) AS rk
          FROM tb_user_log
          WHERE sign_in = 1 AND artical_id = 0 AND (DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31')) AS t1) AS t2
GROUP BY uid, DATE_FORMAT(start_date, "%Y%m")
ORDER BY month ASC, uid ASC;