SQL练习-连续类问题

连续类问题

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

备注：
• 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 金币）
• 如果签到记录的 in_time-进入时间和 out_time-离开时间跨天了，也只记作 in_time
对应的日期签到了。

数据源表

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);

数据源表为

解题思路

• 要先筛选出合法用户，包括article_id = 0且已签到
• 对第三天和第七天进行特殊处理，即要判断是不是第三天或第七天

代码逻辑

1. 先筛选合法用户并进行排序

SELECT DISTINCT
	uid
	,DATE(in_time) dt
	,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY DATE(in_time)) AS rn
FROM tb_user_log
-- 只有这样才算合法用户
WHERE article_id = 0 AND sign_in = 1 AND DATE(in_time) BETWEEN '2021-07-01' AND '2021-10-31'

2. 通过dt - rn得到dt_tmp来判断连续性，即dt_tmp相同的则是连续，再对连续的登录进行分组排序，同时判断是否是第三天或第七天

SELECT
	*
	,DATE_SUB(dt,INTERVAL rn day) AS dt_tmp
	-- 对7取模，如果为3则是代表第三天，如果是0代表是第七天
	,CASE ROW_NUMBER() OVER(PARTITION BY DATE_SUB(dt,INTERVAL rn day),uid ORDER BY dt)%7
		WHEN 3 THEN 3
		WHEN 0 THEN 7
		ELSE 1
	END AS day_coin
FROM(
-- 第一步内容
	SELECT DISTINCT
		uid
		,DATE(in_time) dt
		,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY DATE(in_time)) AS rn
	FROM tb_user_log
	-- 只有这样才算合法用户
	WHERE article_id = 0 AND sign_in = 1 AND DATE(in_time) BETWEEN '2021-07-01' AND '2021-10-31'
)

3. 进行汇总计算 ,最终完整代码为

WITH distinct_uid_sort AS(
	SELECT DISTINCT
		uid
        ,DATE(in_time) AS dt
        ,ROW_NUMBER() 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) BETWEEN '2021-07-01' AND '2021-10-31'
),
compute AS(
	SELECT
		*
        ,date_sub(dt, INTERVAL rn day) AS dt_tmp
        ,case row_number() over(partition by date_sub(dt, INTERVAL rn day),uid ORDER BY dt)%7
			WHEN 3 THEN 3
            WHEN 0 THEN 7
            ELSE 1
		END as day_coin
	FROM distinct_uid_sort
)
SELECT
	uid,DATE_FORMAT(dt,'%Y%m') month,sum(day_coin) AS total
FROM compute
GROUP BY uid,DATE_FORMAT(dt,'%Y%m')

最终的统计结果为