手写sql果然还是很难，果然还是岁数大了，或者说今天心不静吧，尝试3次才能理解写出来。
但值得说的是，我真的理解了，也掌握了sql中的一些函数，真是感受到了，在变强呀！哈哈
对应SQL
代码语言：javascript复制insert into ebook_snapshot (ebook_id, date, view_count, vote_count, view_increase, vote_increase)
SELECT t1.id, curdate(), 0, 0, 0, 0
from ebook t1
where not exists(select 1 from ebook_snapshot t2 where t1.id = t2.ebook_id and t2.date = curdate());
update ebook_snapshot t1, ebook t2
set t1.vote_count=t2.vote_count,
t1.view_count=t2.view_count
where t1.date = curdate()
and t1.ebook_id = t2.id;
select t1.ebook_id, t1.vote_count, t1.view_count
from ebook_snapshot t1
where date = date_sub(curdate(), interval 1 day);
update ebook_snapshot t1 left join (select ebook_id, vote_count, view_count
from ebook_snapshot
where date = date_sub(curdate(), interval 1 day)) t2
on t1.ebook_id = t2.ebook_id
set t1.view_increase=t1.view_count - ifnull(t2.view_count, 0),
t1.vote_increase=t1.vote_count - ifnull(t2.view_count, 0)
where t1.date = curdate()
电子书快照功能
完全是后端，自定义sql的使用，这是核心，示例如下：
代码语言：javascript复制<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.jiawa.wiki.mapper.EbookSnapshotMapperCust" >
<!--
# 方案一（ID不连续）：
# 删除今天的数据
# 为所有的电子书生成一条今天的记录
# 更新总阅读数、总点赞数
# 更新今日阅读数、今日点赞数
# 方案二（ID连续）：
# 为所有的电子书生成一条今天的记录，如果还没有
# 更新总阅读数、总点赞数
# 更新今日阅读数、今日点赞数
-->
<update id="genSnapshot">
insert into ebook_snapshot(ebook_id, date, view_count, vote_count, view_increase, vote_increase)
select t1.id, curdate(), 0, 0, 0, 0
from ebook t1
where not exists(select 1
from ebook_snapshot t2
where t1.id = t2.ebook_id
and t2.date = curdate());
update ebook_snapshot t1, ebook t2
set t1.view_count = t2.view_count,
t1.vote_count = t2.vote_count
where t1.date = curdate()
and t1.ebook_id = t2.id;
update ebook_snapshot t1 left join (select ebook_id, view_count, vote_count
from ebook_snapshot
where date = date_sub(curdate(), interval 1 day)) t2
on t1.ebook_id = t2.ebook_id
set t1.view_increase = (t1.view_count - ifnull(t2.view_count, 0)),
t1.vote_increase = (t1.vote_count - ifnull(t2.vote_count, 0))
where t1.date = curdate();
</update>
</mapper>
定时任务，负责收集数据
代码语言：javascript复制import com.jiawa.wiki.service.EbookSnapshotService;
import com.jiawa.wiki.util.SnowFlake;
import jakarta.annotation.Resource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.slf4j.MDC;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
@Component
public class EbookSnapshotJob {
private static final Logger LOG = LoggerFactory.getLogger(EbookSnapshotJob.class);
@Resource
private EbookSnapshotService ebookSnapshotService;
@Resource
private SnowFlake snowFlake;
/**
* 自定义cron表达式跑批
* 只有等上一次执行完成，下一次才会在下一个时间点执行，错过就错过
*/
@Scheduled(cron = "0/5 * * * * ?")
public void doSnapshot() {
// 增加日志流水号
MDC.put("LOG_ID", String.valueOf(snowFlake.nextId()));
LOG.info("生成今日电子书快照开始");
Long start = System.currentTimeMillis();
ebookSnapshotService.genSnapshot();
LOG.info("生成今日电子书快照结束，耗时：{}毫秒", System.currentTimeMillis() - start);
}
}
public interface EbookSnapshotMapperCust {
public void genSnapshot();
}
import com.jiawa.wiki.mapper.EbookSnapshotMapperCust;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;
@Service
public class EbookSnapshotService {
@Resource
private EbookSnapshotMapperCust ebookSnapshotMapperCust;
public void genSnapshot() {
ebookSnapshotMapperCust.genSnapshot();
}
}
配置文件添加如下内容：
代码语言：javascript复制rocketmq:
name-server: 127.0.0.1:9876 # mq地址
producer:
group: default # 必须指定group
send-message-timeout: 3000 # 消息发送超时时长，默认3s
retry-times-when-send-failed: 3 # 同步发送消息失败重试次数，默认2
retry-times-when-send-async-failed: 3 # 异步发送消息失败重试次数，默认2
customized-trace-topic: TEST__TOPIC
知识点：
- not exists：这个条件用于检查子查询是否返回任何记录。如果没有返回记录，
not exists条件为真 - 使用 select 1 是一种常用的技巧，它实际上并不关心查询返回的具体数据，只关心是否有记录返回。
- &allowMultiQueries=true可让mybatis一次知心多个sql
- left join配合on使用，一定要加 俩表id=id
- date_sub(curdate(), interval 1 day)) 获取前一天
- ifnull(t2.view_count, 0)，如果view_count没有值直接给默认值0
写在最后
真的不得不说，这世上存在能量交换这事，多与高能量的人接触，这样你才会变的更好，加油呀，朋友！
