累計連續(xù)簽到 設(shè)計和實現(xiàn)
最近公司業(yè)務(wù)上需要實現(xiàn)一個累計連續(xù)打卡的功能，現(xiàn)在把打卡設(shè)計問題和思路整理一下發(fā)給大家
-
目前搜集到一些基于 Redis 位圖 / 關(guān)系型數(shù)據(jù)庫的一些方案，可以參考一下，做出最優(yōu)方案的選擇
由于需求的復(fù)雜，本文還是選擇使用關(guān)系型數(shù)據(jù)庫實現(xiàn)和存儲，因為關(guān)系型數(shù)據(jù)庫查詢無所不能，哈哈哈哈
功能要求
- 簽到
- 補(bǔ)簽
- 統(tǒng)計某用戶截至今天連續(xù)打卡天數(shù)
- 統(tǒng)計某用戶在某一天打卡排名
- 統(tǒng)計某用戶截至到某天連續(xù)打卡天數(shù)
- 最高連續(xù)簽到記錄
下面直接上一個需求圖
問題難點
- 怎么用比較好方式去統(tǒng)計連續(xù)打卡天數(shù)
- 怎么實現(xiàn)補(bǔ)卡功能以達(dá)到連續(xù)簽到的效果
- 怎么實現(xiàn)補(bǔ)簽后連續(xù)天數(shù)的統(tǒng)計功能
數(shù)據(jù)庫設(shè)計
以下是打卡記錄表的設(shè)計和實現(xiàn)，我已經(jīng)去掉了一些業(yè)務(wù)字段，剩下都是表結(jié)構(gòu)的核心字段
CREATE TABLE mark_record (
id BIGINT NOT NULL COMMENT 'ID'
PRIMARY KEY,
create_time DATETIME NOT NULL COMMENT '創(chuàng)建時間',
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 '距離上次打卡相差天數(shù)',
mark_type TINYINT DEFAULT 0 NOT NULL COMMENT '補(bǔ)簽 0否 1是',
CONSTRAINT uidx_user_id_mark_day_time
UNIQUE (user_id, mark_day_time)
)
COMMENT '打卡簽到表';
id/create_time/update_time 表結(jié)構(gòu)的常規(guī)字段，簡單提醒一下，業(yè)務(wù)上這些字段也比較重要
id表的唯一主鍵create_time/update_time比較重要數(shù)據(jù)信息字段一般都保留
列舉一個比較實用業(yè)界數(shù)據(jù)分頁案例：
數(shù)據(jù)分頁翻頁時候，防止新增數(shù)據(jù)導(dǎo)致分頁加載出現(xiàn)重復(fù)數(shù)據(jù)，一般做法是當(dāng)客戶端打卡當(dāng)前頁面那瞬間時間戳傳過來，上下翻頁都是用同一個時間戳，后端查詢數(shù)據(jù)時候只查詢小于這個時間戳的數(shù)據(jù)，大于這個時間戳的數(shù)據(jù)就不會加載出來了
其他用途就不一一列舉了
-
user_id&mark_day_time組成一個唯一索引
一個用戶一天只允許打卡一次，加唯一索引保證數(shù)據(jù)唯一防止臟數(shù)據(jù)
-
mark_type記錄打卡類型
區(qū)分正常打卡和補(bǔ)卡
-
day_continue冗余字段 距離上次打卡記錄相差天數(shù)
以方便統(tǒng)計相關(guān)打卡記錄數(shù)據(jù)
代碼實現(xiàn)
打卡功能實現(xiàn)
markDayTime 當(dāng)前打卡簽到日期，userId 當(dāng)前打卡用戶 ID
簽到功能 SQL 實現(xiàn)
使用 INSERT INTO SELECT 查詢小于當(dāng)前簽到日期(markDayTime)最近一條簽到記錄數(shù)據(jù)，如果不存在，day_continue 字段為 -1，如果存在打卡記錄，則day_continue 字段為 markDayTime 與查詢簽到記錄結(jié)果 mark_day_time 相差天數(shù)
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
補(bǔ)簽功能實現(xiàn)
補(bǔ)簽功能 SQL
其實和簽到功能的sql一致，傳入?yún)?shù)不一樣：簽到日期
markDayTime為補(bǔ)簽日期，markType類型為補(bǔ)簽類型
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
補(bǔ)簽和普通打卡在代碼上有不一致，因為需要更新大于補(bǔ)簽日期最舊一條數(shù)據(jù)的
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, "補(bǔ)簽日期異常");
}
// 構(gòu)造打卡記錄
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)) {
// 更新補(bǔ)簽日期前一條數(shù)據(jù)間隔天數(shù)
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}
統(tǒng)計連續(xù)簽到功能實現(xiàn)
計算今天是否打卡/連續(xù)打卡天數(shù)/總打卡數(shù)
今天是否打卡:查詢今天是否存在打卡記錄
連續(xù)打卡天數(shù)：當(dāng)天沒打卡，前一天打卡，也算連續(xù)打卡；如果前一天沒有打卡，那就斷簽了，
總打卡數(shù)：統(tǒng)計用戶所有打卡記錄數(shù)量
SQL 參數(shù)說明：
#{yesterdayTime}為昨天的日期，#{markDayTime}為今天的日期
SQL 連續(xù)簽到統(tǒng)計邏輯：
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
查詢所在某天的連續(xù)簽到天數(shù)
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
實現(xiàn)最高連續(xù)天數(shù)
用戶數(shù)據(jù)表加一個最高連續(xù)簽到記錄或者 redis 記錄用戶ID關(guān)聯(lián)的最高記錄，每次簽到后查詢連簽記錄，大于替換掉該值。
本文就不提供相關(guān)的代碼實現(xiàn)
總結(jié)
- 目前這個方案我總感覺還是不夠完美，希望大家看了可以提供一下相關(guān)的想法
- 我覺得比較好的方案是上面文章鏈接提到的 Redis 位圖實現(xiàn)方式與 目前方案 混合搭配使用，記錄時候分別記錄兩份數(shù)據(jù)
優(yōu)點
- 使用關(guān)系型數(shù)據(jù)庫做了簽到記錄，關(guān)系型數(shù)據(jù)庫的強(qiáng)大易于統(tǒng)計相關(guān)的簽到數(shù)據(jù)
缺點
- 統(tǒng)計 SQL 復(fù)雜
- 當(dāng)記錄數(shù)據(jù)量大，性能可能存在問題
