小让の码场

外观

SQL167 连续签到领金币 ​

Static BadgeStatic Badge

题目描述 ​

用户行为日志表tb_user_log

iduidartical_idin_timeout_timesign_in
110102021-07-07 10:00:002021-07-07 10:00:091
210102021-07-08 10:00:002021-07-08 10:00:091
310102021-07-09 10:00:002021-07-09 10:00:421
410102021-07-10 10:00:002021-07-10 10:00:091
510102021-07-11 23:59:552021-07-11 23:59:591
610102021-07-12 10:00:282021-07-12 10:00:501
710102021-07-13 10:00:282021-07-13 10:00:501
810202021-10-01 10:00:282021-10-01 10:00:501
910202021-10-02 10:00:012021-10-02 10:01:501
1010202021-10-03 10:00:552021-10-03 11:00:591
1110202021-10-04 10:00:452021-10-04 11:00:550
1210202021-10-05 10:00:532021-10-05 11:00:591
1310202021-10-06 10:00:452021-10-06 11:00:551

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：

  • artical_id-文章ID代表用户浏览的文章的ID，特殊情况artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。注意：只有artical_id为0时sign_in值才有效。
  • 从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
  • 每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

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

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

输出示例：

示例数据的输出结果如下：

uidmonthcoin
10120210715
1022021107

解释：

101在活动期内连续签到了7天，因此获得1*7+2+6=15金币；

102在10.01~10.03连续签到3天获得5金币

10.04断签了，10.05~10.06连续签到2天获得2金币，共得到7金币。

SQL Schema ​

sql
DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-07-07 10:00:00', '2021-07-07 10:00:09', 1),
  (101, 0, '2021-07-08 10:00:00', '2021-07-08 10:00:09', 1),
  (101, 0, '2021-07-09 10:00:00', '2021-07-09 10:00:42', 1),
  (101, 0, '2021-07-10 10:00:00', '2021-07-10 10:00:09', 1),
  (101, 0, '2021-07-11 23:59:55', '2021-07-11 23:59:59', 1),
  (101, 0, '2021-07-12 10:00:28', '2021-07-12 10:00:50', 1),
  (101, 0, '2021-07-13 10:00:28', '2021-07-13 10:00:50', 1),
  (102, 0, '2021-10-01 10:00:28', '2021-10-01 10:00:50', 1),
  (102, 0, '2021-10-02 10:00:01', '2021-10-02 10:01:50', 1),
  (102, 0, '2021-10-03 11:00:55', '2021-10-03 11:00:59', 1),
  (102, 0, '2021-10-04 11:00:45', '2021-10-04 11:00:55', 0),
  (102, 0, '2021-10-05 11:00:53', '2021-10-05 11:00:59', 1),
  (102, 0, '2021-10-06 11:00:45', '2021-10-06 11:00:55', 1);

思路分析 ​

比较好理解的思考方式就是根据需要的结果，一步一步反推自己需要什么样格式的数据。

  1. 计算活动期间每个用户每月所获得的金币数。那么我反推一步最希望的数据是用户某一天签到时所获得的金币数，然后就可以按照用户(uid)和月份(month)进行分组，聚合(sum)一下金币数即可得到活动期间每个用户每月所获得的金币数。

    用户某一天签到时所获得的金币数的数据如下表所示：

    uiddtday_coin
    1012021-07-071
    1012021-07-081
    1012021-07-093
    1012021-07-101
    1012021-07-111
    1012021-07-121
    1012021-07-137
    1022021-10-011
    1022021-10-021
    1022021-10-033
    1022021-10-051
    1022021-10-061

  2. 再反推，想要获得用户某一天签到时所获得的金币数，那么我必须知道用户签到当天是连续签到的第几天。知道是连续签到的第几天之后，那么签到当天所获得的金币数 = 连续签到的第几天对 7 取余，如果余数为 0 则获得 7 枚金币，如果为3则获得3枚，其他情况为 1 枚金币。

    用户签到当天是连续签到的第几天的数据如下表所示，从表中可以看到，因为用户102在 2021-10-04 那一天断签了，所以他在 2021-10-05 那一天会被重新算作连续签到的第一天。

    uiddt连续签到的第几天
    1012021-07-071
    1012021-07-082
    1012021-07-093
    1012021-07-104
    1012021-07-115
    1012021-07-126
    1012021-07-137
    1022021-10-011
    1022021-10-022
    1022021-10-033
    1022021-10-051
    1022021-10-062

  3. 其实反推到这里思路就已经非常清晰了，求用户签到当天是连续签到的第几天，也就是所谓的连续问题。

    1. 连续问题的核心就是利用签到日期与排序编号的差值相等。因为如果是连续的话，编号自增 1，日期同样自增 1 天。

    2. 如下表所示，dt 是签到日期，dt_tmp 是签到日期与排序编号的差值，可以发现用户102在 2021-10-04 那一天断签了，因此他在 2021-10-05 那天的 dt_tmp 与前面的不相同。

      uiddtrndt_tmp
      1012021-07-0712021-07-06
      1012021-07-0822021-07-06
      1012021-07-0932021-07-06
      1012021-07-1042021-07-06
      1012021-07-1152021-07-06
      1012021-07-1262021-07-06
      1012021-07-1372021-07-06
      1022021-10-0112021-09-30
      1022021-10-0222021-09-30
      1022021-10-0332021-09-30
      1022021-10-0542021-10-01
      1022021-10-0652021-10-01

    3. 求用户签到当天是连续签到的第几天。只需按照用户和签到日期与排序编号的差值进行分组，日期升序进行编号（ROW_NUMBER() OVER (PARTITION BY uid, DATE_SUB(dt, INTERVAL rn DAY) ORDER BY dt)）即可得到用户签到当天是连续签到的第几天。

      uiddtrndt_tmp连续签到的第几天
      1012021-07-0712021-07-061
      1012021-07-0822021-07-062
      1012021-07-0932021-07-063
      1012021-07-1042021-07-064
      1012021-07-1152021-07-065
      1012021-07-1262021-07-066
      1012021-07-1372021-07-067
      1022021-10-0112021-09-301
      1022021-10-0222021-09-302
      1022021-10-0332021-09-303
      1022021-10-0542021-10-011
      1022021-10-0652021-10-012

答案 ​

sql
WITH t1 AS (SELECT DISTINCT uid,                                                             -- 为了防止一天有多次签到活动，使用 DISTINCT 去重
                            DATE(in_time) AS                                            `dt`,
                            DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE(in_time)) `rn` -- 按照用户分组，日期升序进行编号
            FROM tb_user_log
            WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
              AND artical_id = 0
              AND sign_in = 1),
     t2 AS (SELECT uid,
                   dt,
                   rn,
                   -- 如果用户是连续签到的话，则每天日期-编号所得到的日期（差值）应该是相等的，如果不是连续（即中间有断签的情况）的话，则差值不相等
                   DATE_SUB(dt, INTERVAL rn DAY)                                                   AS `dt_tmp`,
                   -- 按照用户和相减所得到的日期进行分组，日期升序进行编号，如果用户中间有断签，就不会分到同一组，也就会重新编号
                   ROW_NUMBER() OVER (PARTITION BY uid, DATE_SUB(dt, INTERVAL rn DAY) ORDER BY dt) AS `连续签到的第几天`,
                   -- 计算用户当天签到时应该获得的金币数
                   CASE ROW_NUMBER() OVER (PARTITION BY uid, DATE_SUB(dt, INTERVAL rn DAY) ORDER BY dt) % 7
                       WHEN 3 THEN 3
                       WHEN 0 THEN 7
                       ELSE 1 END                                                                  AS `day_coin` -- 用户当天签到时应该获得的金币数
            FROM t1)
SELECT uid, DATE_FORMAT(dt, '%Y%m') AS `month`, SUM(day_coin) AS `coin`
FROM t2
GROUP BY 1, 2
ORDER BY 2, 1;