- 已知一张用户行为日志表
tb_user_log
,字段包括uid-用户ID
、artical_id-文章ID
、in_time-进入时间
、out_time-离开时间
、sign_in-是否签到
- 注意1:只有
artical_id为0时sign_in
值才有效; - 注意2:从2021年7月7日0点开始,用户每天签到可以领1金币,并可以开始累积签到天数,连续签到的第3、7天分别可额外领2、6金币,每连续签到7天后重新累积签到天数
- 问题:计算每个用户2021年7月至10月每月获得的金币数
CREATE TABLE tb_user_log (
uid INT COMMENT '用户ID',
artical_id INT COMMENT '视频ID',
in_time varchar(20) COMMENT '进入时间',
out_time varchar(20) COMMENT '离开时间',
sign_in int COMMENT '是否签到'
) ;
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-14 10:00:28', '2021-07-13 10:00:50', 1),
(101, 0, '2021-07-15 11:00:28', '2021-07-14 11:00:50', 1),
(101, 0, '2021-07-16 11:59:28', '2021-07-16 00:01:20', 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-05 11:00:45', '2021-10-04 11:00:55', 0),
(102, 0, '2021-10-06 11:00:53', '2021-10-05 11:00:59', 1),
(102, 0, '2021-10-07 11:00:45', '2021-10-06 11:00:55', 1);
思路分析:
为了计算每日用户领取的金币数量,我们需要关注三个关键节点:
1. 连续签到3天,额外获得2个金币(共领取3个金币)。
2. 连续签到7天,额外获得6个金币(共领取7个金币)。
3. 在其余情况下,每天领取1个金币。
- 如何判断是否连续签到3天/7天?
思路:
我们需要将一段连续的签到日期分成一组,再将这段日期组别进行组内排序最终判断金币数
所以该题的难度是如何进行分组:
分组策略:
1. 排序与排名: - 使用ROW_NUMBER()
函数对用户的签到记录按日期排序,并为每条记录生成一个唯一的排名rn
。
row_number() OVER (PARTITION BY uid ORDER BY DATE(in_time)) as rn
2. 日期分组: - 利用DATE_SUB(dt, INTERVAL rn DAY)
对签到日期进行转换,其中dt
是签到日期,rn
是排序后的排名。这样处理后,连续签到的日期将转换到相同的值,从而实现分组。
然后再使用ROW_NUMBER()
函数对组内连续日期进行排序并 % 7 进行判断后计算金币数
case row_number() over(partition by uid,date_sub(dt, interval rn day) order by dt)%7
when 3 then 3
when 7 then 7
else 1
end as day_coin
date rn group
2021-07-07 1 2021-07-06
2021-07-08 2 2021-07-06
2021-07-11 3 2021-07-08
对于签到日期2021-07-07
和2021-07-08
,通过DATE_SUB()
函数处理后,它们都被转换到相同的日期2021-07-06
。这表明这两个签到记录是连续的,因此它们属于同一组。
签到日期2021-07-11
经过相同的处理后,转换到了2021-07-08
。这个结果与前两个日期的处理结果不同,,用户的签到行为中断了,因说明从2021-07-08
到2021-07-11
之间此2021-07-11
属于一个新的组。
其中还有注意的是:
原数据的日期表示为这种格式:'2021-07-06 10:00:00'
我们需要使用date()函数转为这种格式:'2021-07-06 '
全部语句如下:
WITH t1 AS (
SELECT
uid,
DATE(in_time) AS dt,
row_number() OVER (PARTITION BY uid ORDER BY DATE(in_time)) as 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,
-- 连续的日期会形成同一个组,然后再对组内进行一次排序。
case row_number() over(partition by uid, date_sub(dt, interval rn day) order by dt)%7
when 3 then 3
when 7 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 uid, DATE_FORMAT(dt, '%Y-%m');