累计连续签到 设计和实现
最近公司业务上需要实现一个累计连续打卡的功能，现在把打卡设计问题和思路整理一下发给大家
-
目前搜集到一些基于 Redis 位图 / 关系型数据库的一些方案，可以参考一下，做出最优方案的选择
由于需求的复杂，本文还是选择使用关系型数据库实现和存储，因为关系型数据库查询无所不能，哈哈哈哈
功能要求
- 签到
- 补签
- 统计某用户截至今天连续打卡天数
- 统计某用户在某一天打卡排名
- 统计某用户截至到某天连续打卡天数
- 最高连续签到记录
下面直接上一个需求图
问题难点
- 怎么用比较好方式去统计连续打卡天数
- 怎么实现补卡功能以达到连续签到的效果
- 怎么实现补签后连续天数的统计功能
数据库设计
以下是打卡记录表的设计和实现，我已经去掉了一些业务字段，剩下都是表结构的核心字段
CREATE TABLE mark_record (
id BIGINT NOT NULL COMMENT 'ID'
PRIMARY KEY,
create_time DATETIME NOT NULL COMMENT '创建时间',
update_time DATETIME NOT NULL COMMENT '更新时间',
user_id BIGINT NOT NULL COMMENT '用户ID',
mark_day_time INT NOT NULL COMMENT '打卡日期 yyyyMMdd',
day_continue BIGINT DEFAULT 0 NOT NULL COMMENT '距离上次打卡相差天数',
mark_type TINYINT DEFAULT 0 NOT NULL COMMENT '补签 0否 1是',
CONSTRAINT uidx_user_id_mark_day_time
UNIQUE (user_id, mark_day_time)
)
COMMENT '打卡签到表';
id/create_time/update_time 表结构的常规字段，简单提醒一下，业务上这些字段也比较重要
id表的唯一主键create_time/update_time比较重要数据信息字段一般都保留
列举一个比较实用业界数据分页案例：
数据分页翻页时候，防止新增数据导致分页加载出现重复数据，一般做法是当客户端打卡当前页面那瞬间时间戳传过来，上下翻页都是用同一个时间戳，后端查询数据时候只查询小于这个时间戳的数据，大于这个时间戳的数据就不会加载出来了
其他用途就不一一列举了
-
user_id&mark_day_time组成一个唯一索引
一个用户一天只允许打卡一次，加唯一索引保证数据唯一防止脏数据
-
mark_type记录打卡类型
区分正常打卡和补卡
-
day_continue冗余字段 距离上次打卡记录相差天数
以方便统计相关打卡记录数据
代码实现
打卡功能实现
markDayTime 当前打卡签到日期，userId 当前打卡用户 ID
签到功能 SQL 实现
使用 INSERT INTO SELECT 查询小于当前签到日期(markDayTime)最近一条签到记录数据，如果不存在，day_continue 字段为 -1，如果存在打卡记录，则day_continue 字段为 markDayTime 与查询签到记录结果 mark_day_time 相差天数
INSERT INTO mark_record (id, create_time, update_time, user_id, mark_day_time, day_continue, mark_type)
SELECT #{id},
#{createTime},
#{updateTime},
#{userId},
#{markDayTime},
IF(COUNT(t.id) = 0, -1, to_days(#{markDayTime}) - to_days(mark_day_time)),
#{markType}
FROM (SELECT id, mark_day_time
FROM mark_record
WHERE user_id = #{userId}
AND mark_day_time < #{markDayTime}
ORDER BY mark_day_time DESC
LIMIT 1) t
补签功能实现
补签功能 SQL
其实和签到功能的sql一致，传入参数不一样：签到日期
markDayTime为补签日期，markType类型为补签类型
INSERT INTO mark_record (id, create_time, update_time, user_id, mark_day_time, day_continue, mark_type)
SELECT #{id},
#{createTime},
#{updateTime},
#{userId},
#{markDayTime},
IF(COUNT(t.id) = 0, -1, to_days(#{markDayTime}) - to_days(mark_day_time)),
#{markType}
FROM (SELECT id, mark_day_time
FROM mark_record
WHERE user_id = #{userId}
AND mark_day_time < #{markDayTime}
ORDER BY mark_day_time DESC
LIMIT 1) t
补签和普通打卡在代码上有不一致，因为需要更新大于补签日期最旧一条数据的
day_continue字段
public MarkRecord completeMark(MarkRecord record) {
DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
Long userId = record.getUserId();
Integer markDayTime = record.getMarkDayTime();
int nowDayTime = Integer.parseInt(LocalDateTime.now().format(DATE_TIME_FORMATTER));
if (nowDayTime <= markDayTime) {
throw new ServiceFailException(FailCode.ERROR_PARAM, "补签日期异常");
}
// 构造打卡记录
MarkRecord mark = fillMarkRecord(record, markDayTime, 1);
int completeMarkResult = markRecordMapper.completeMark(mark);
if (completeMarkResult != 1) {
return null;
}
// 更新大于markDayTime的第一条记录dayContinue字段值
MarkRecord nearestBeforeRecord = markRecordMapper.findNearestBeforeRecord(userId, markDayTime, clubId, markId);
if (Objects.nonNull(nearestBeforeRecord)) {
// 更新补签日期前一条数据间隔天数
Integer time = nearestBeforeRecord.getMarkDayTime();
long betweenDays = LocalDate.parse(String.valueOf(markDayTime), DATE_TIME_FORMATTER)
.until(LocalDate.parse(String.valueOf(time), DATE_TIME_FORMATTER), ChronoUnit.DAYS);
markRecordMapper.updateDayContinueById(betweenDays, nearestBeforeRecord.getId());
}
return mark;
}
findNearestBeforeRecordSQL:
SELECT *
FROM mark_record
WHERE user_id = #{userId}
AND mark_day_time > #{markDayTime}
ORDER BY mark_day_time
LIMIT 1
updateDayContinueByIdSQL:
UPDATE mark_record
SET day_continue=#{updatedDayContinue}
WHERE id = #{id}
统计连续签到功能实现
计算今天是否打卡/连续打卡天数/总打卡数
今天是否打卡:查询今天是否存在打卡记录
连续打卡天数：当天没打卡，前一天打卡，也算连续打卡；如果前一天没有打卡，那就断签了，
总打卡数：统计用户所有打卡记录数量
SQL 参数说明：
#{yesterdayTime}为昨天的日期，#{markDayTime}为今天的日期
SQL 连续签到统计逻辑：
SELECT im.mark AS marked,
IF(yim.mark = 0,
(IF(im.mark = 0, 0, 1)),
(CASE yim.day_continue
WHEN 0
THEN 1 + if(im.mark = 0, 0, 1)
WHEN 1
THEN to_days(#{yesterdayTime}) - to_days((SELECT mark_day_time
FROM mark_record
WHERE user_id = #{userId}
AND mark_day_time < #{yesterdayTime}
AND day_continue != 1
ORDER BY mark_day_time DESC
LIMIT 1)) + if(im.mark = 0, 0, 1) + 1
ELSE
1 + if(im.mark = 0, 0, 1)
END)) AS continueMarkedDays,
amc.markCount AS totalMarkedDays
FROM (SELECT if(count(*) > 0, 1, 0) AS mark
FROM mark_record
WHERE user_id = #{userId}
AND mark_day_time = #{markDayTime}) im,
(SELECT if(count(*) > 0, 1, 0) AS mark, day_continue
FROM mark_record
WHERE user_id = #{userId}
AND mark_day_time = #{yesterdayTime}) yim,
(SELECT count(*) AS markCount
FROM mark_record
WHERE user_id = #{userId}) amc
查询所在某天的连续签到天数
SELECT if(tmrmdt.day_continue != 1,
to_days(ta.mark_day_time) - to_days(#{day}) + 1,
to_days(ta.mark_day_time) - to_days(tb.mark_day_time) + 1)
FROM (SELECT tmr.day_continue
FROM mark_record tmr
WHERE tmr.mark_day_time = #{day}
AND tmr.user_id = #{userId})
AS tmrmdt,
((SELECT bmr.mark_day_time
FROM mark_record bmr
WHERE bmr.mark_day_time < #{day}
AND bmr.day_continue != 1
AND bmr.user_id = #{userId}
ORDER BY bmr.mark_day_time DESC
LIMIT 1)
UNION ALL
(SELECT #{day})
LIMIT 1) tb,
((SELECT amrt.mark_day_time
FROM mark_record amrt,
((SELECT amr.mark_day_time
FROM mark_record amr
WHERE amr.mark_day_time > #{day}
AND amr.day_continue != 1
AND amr.user_id = #{userId}
ORDER BY amr.mark_day_time
LIMIT 1)
UNION ALL
(SELECT NULL)
LIMIT 1) amrtt
WHERE if(amrtt.mark_day_time IS NOT NULL,
amrt.mark_day_time < amrtt.mark_day_time,
amrt.mark_day_time > #{day})
AND amrt.day_continue = 1
AND amrt.user_id = #{userId}
ORDER BY amrt.mark_day_time DESC
LIMIT 1)
UNION ALL
(SELECT #{day})
LIMIT 1) ta
实现最高连续天数
用户数据表加一个最高连续签到记录或者 redis 记录用户ID关联的最高记录，每次签到后查询连签记录，大于替换掉该值。
本文就不提供相关的代码实现
总结
- 目前这个方案我总感觉还是不够完美，希望大家看了可以提供一下相关的想法
- 我觉得比较好的方案是上面文章链接提到的 Redis 位图实现方式与 目前方案 混合搭配使用，记录时候分别记录两份数据
优点
- 使用关系型数据库做了签到记录，关系型数据库的强大易于统计相关的签到数据
缺点
- 统计 SQL 复杂
- 当记录数据量大，性能可能存在问题
