累計(jì)連續(xù)簽到設(shè)計(jì)和實(shí)現(xiàn)
點(diǎn)擊關(guān)注，與你共同成長！
作者：hdfg159 鏈接：https://www.jianshu.com/p/bacd924df502
累計(jì)連續(xù)簽到設(shè)計(jì)和實(shí)現(xiàn)
最近公司業(yè)務(wù)上需要實(shí)現(xiàn)一個(gè)累計(jì)連續(xù)打卡的功能，現(xiàn)在把打卡設(shè)計(jì)問題和思路整理一下發(fā)給大家
目前搜集到一些基于 Redis 位圖 / 關(guān)系型數(shù)據(jù)庫的一些方案，可以參考一下，做出最優(yōu)方案的選擇
玩轉(zhuǎn)Redis-京東簽到領(lǐng)京豆如何實(shí)現(xiàn) 基于Redis位圖實(shí)現(xiàn)用戶簽到功能 如何利用 Redis 快速實(shí)現(xiàn)簽到統(tǒng)計(jì)功能
由于需求的復(fù)雜，本文還是選擇使用關(guān)系型數(shù)據(jù)庫實(shí)現(xiàn)和存儲(chǔ)，因?yàn)殛P(guān)系型數(shù)據(jù)庫查詢無所不能，哈哈哈哈
功能要求
簽到
補(bǔ)簽
統(tǒng)計(jì)某用戶截至今天連續(xù)打卡天數(shù)
統(tǒng)計(jì)某用戶在某一天打卡排名
統(tǒng)計(jì)某用戶截至到某天連續(xù)打卡天數(shù)
最高連續(xù)簽到記錄
下面直接上一個(gè)需求圖
問題難點(diǎn)
怎么用比較好方式去統(tǒng)計(jì)連續(xù)打卡天數(shù)
怎么實(shí)現(xiàn)補(bǔ)卡功能以達(dá)到連續(xù)簽到的效果
怎么實(shí)現(xiàn)補(bǔ)簽后連續(xù)天數(shù)的統(tǒng)計(jì)功能
數(shù)據(jù)庫設(shè)計(jì)
以下是打卡記錄表的設(shè)計(jì)和實(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)建時(shí)間',
update_time DATETIME NOT NULL COMMENT '更新時(shí)間',
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ù)信息字段一般都保留
列舉一個(gè)比較實(shí)用業(yè)界數(shù)據(jù)分頁案例：數(shù)據(jù)分頁翻頁時(shí)候，防止新增數(shù)據(jù)導(dǎo)致分頁加載出現(xiàn)重復(fù)數(shù)據(jù)，一般做法是當(dāng)客戶端打卡當(dāng)前頁面那瞬間時(shí)間戳傳過來，上下翻頁都是用同一個(gè)時(shí)間戳，后端查詢數(shù)據(jù)時(shí)候只查詢小于這個(gè)時(shí)間戳的數(shù)據(jù)，大于這個(gè)時(shí)間戳的數(shù)據(jù)就不會(huì)加載出來了 其他用途就不一一列舉了
user_id & mark_day_time 組成一個(gè)唯一索引
一個(gè)用戶一天只允許打卡一次，加唯一索引保證數(shù)據(jù)唯一防止臟數(shù)據(jù)
mark_type 記錄打卡類型
區(qū)分正常打卡和補(bǔ)卡
day_continue 冗余字段 距離上次打卡記錄相差天數(shù)
以方便統(tǒng)計(jì)相關(guān)打卡記錄數(shù)據(jù)
代碼實(shí)現(xiàn)
打卡功能實(shí)現(xiàn)
markDayTime 當(dāng)前打卡簽到日期，userId 當(dāng)前打卡用戶 ID
簽到功能 SQL 實(shí)現(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ǔ)簽功能實(shí)現(xiàn)
補(bǔ)簽功能 SQL
其實(shí)和簽到功能的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ǔ)簽和普通打卡在代碼上有不一致，因?yàn)樾枰麓笥谘a(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;
}
findNearestBeforeRecord SQL:
SELECT *
FROM mark_record
WHERE user_id = #{userId}
AND mark_day_time > #{markDayTime}
ORDER BY mark_day_time
LIMIT 1
updateDayContinueById SQL:
UPDATE mark_record
SET day_continue=#{updatedDayContinue}
WHERE id = #{id}
統(tǒng)計(jì)連續(xù)簽到功能實(shí)現(xiàn)
計(jì)算今天是否打卡/連續(xù)打卡天數(shù)/總打卡數(shù)
今天是否打卡:查詢今天是否存在打卡記錄 連續(xù)打卡天數(shù)：當(dāng)天沒打卡，前一天打卡，也算連續(xù)打卡；如果前一天沒有打卡，那就斷簽了， 總打卡數(shù)：統(tǒng)計(jì)用戶所有打卡記錄數(shù)量
SQL 參數(shù)說明：#{yesterdayTime}為昨天的日期，#{markDayTime}為今天的日期
SQL 連續(xù)簽到統(tǒng)計(jì)邏輯：
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
實(shí)現(xiàn)最高連續(xù)天數(shù)
用戶數(shù)據(jù)表加一個(gè)最高連續(xù)簽到記錄或者 redis 記錄用戶ID關(guān)聯(lián)的最高記錄，每次簽到后查詢連簽記錄，大于替換掉該值。本文就不提供相關(guān)的代碼實(shí)現(xiàn)
總結(jié)
目前這個(gè)方案我總感覺還是不夠完美，希望大家看了可以提供一下相關(guān)的想法
我覺得比較好的方案是上面文章鏈接提到的 Redis 位圖實(shí)現(xiàn)方式與 目前方案 混合搭配使用，記錄時(shí)候分別記錄兩份數(shù)據(jù)
優(yōu)點(diǎn)
使用關(guān)系型數(shù)據(jù)庫做了簽到記錄，關(guān)系型數(shù)據(jù)庫的強(qiáng)大易于統(tǒng)計(jì)相關(guān)的簽到數(shù)據(jù)
缺點(diǎn)
統(tǒng)計(jì) SQL 復(fù)雜 當(dāng)記錄數(shù)據(jù)量大，性能可能存在問題
張一鳴的自我修養(yǎng)，每個(gè)職場人都該看一看！
以上，便是今天的分享，希望大家喜歡，覺得內(nèi)容不錯(cuò)的，歡迎「分享」「贊」或者點(diǎn)擊「在看」支持，謝謝各位。