wiki快照

初始化今日快照数据 若今天已有数据 则不进行插入

insert into ebook_snapshot(ebook_id, date, view_count, vote_count, view_increase, vote_increase)
select 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.ebook_id = t2.id
and t1.date = curdate();

查询昨日点赞数和浏览数

select ebook_id, vote_count, view_count
from ebook_snapshot t1
where t1.date = date_sub(curdate(), interval 1 day);

统计增量 增量 = 今天数 - 昨天 这里会遇到一个问题 若不进行佐连接查询的话, 如果昨天无数据 则不会进行更新操作 ifnull()若值不存在 初始化为

update ebook_snapshot t1 left join (select ebook_id, vote_count, view_count
from ebook_snapshot t1
where t1.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 date = curdate()