我要评论一、创建每日预聚合表（每日人脸识别统计表）
create table if not exists xy_daily_face_stats (
stat_date date primary key comment '统计日期（yyyy-mm-dd）',
daily_unique_person int not null default 0 comment '当日去重人数',
updated_at timestamp default current_timestamp on update current_timestamp comment '更新时间',
index idx_daily_count (daily_unique_person) -- 用于查询排序优化
) engine=innodb default charset=utf8mb4 comment='每日刷脸统计表';
二、创建增量更新存储过程
查看所有存储过程
show procedure status where db = 'zhgddb';
方案a：更新指定日期（灵活模式）
create procedure sp_update_daily_face_stats(in p_date date)
begin
-- 声明变量
declare v_daily_unique int default 0;
-- 计算指定日期的统计数据
select
count(distinct personid)
into
v_daily_unique
from xy_importandexport_record
where eventname = 'acs.acs.eventtype.successface'
and inandouttype = 1
and date(eventtime) = p_date;
-- 插入或更新统计表（使用 on duplicate key update）
insert into xy_daily_face_stats (stat_date, daily_unique_person)
values (p_date, v_daily_unique)
on duplicate key update
daily_unique_person = values(daily_unique_person),
updated_at = current_timestamp;
-- 返回处理结果（可选）
select concat('updated: ', p_date,
', unique: ', v_daily_unique) as result;
end
-- 1. 查看存储过程
show create procedure sp_update_daily_face_stats;
-- 3. 执行
call sp_update_daily_face_stats('2026-01-01');
-- 4. 验证
select * from xy_daily_face_stats order by stat_date desc limit 10;
方案b：批量更新指定日期范围（补数据用）
create procedure sp_update_date_range(in p_start_date date, in p_end_date date)
begin
declare v_current_date date;
declare v_daily_unique int;
set v_current_date = p_start_date;
-- 循环处理每一天
while v_current_date <= p_end_date do
-- 计算当天数据
select
count(distinct personid)
into
v_daily_unique
from xy_importandexport_record
where eventname = 'acs.acs.eventtype.successface'
and inandouttype = 1
and date(eventtime) = v_current_date;
-- 插入或更新
insert into xy_daily_face_stats (stat_date, daily_unique_person)
values (v_current_date, v_daily_unique)
on duplicate key update
daily_unique_person = values(daily_unique_person),
updated_at = current_timestamp;
-- 下一天
set v_current_date = date_add(v_current_date, interval 1 day);
end while;
select concat('updated from ', p_start_date, ' to ', p_end_date) as result;
end
-- 1. 查看存储过程
show create procedure sp_update_date_range;
-- 2. 删除旧存储过程
-- drop procedure if exists sp_update_date_range;
-- 3. 执行
call sp_update_date_range('2026-08-06', '2026-08-06');
-- 4. 验证
select * from xy_daily_face_stats order by stat_date desc limit 10;
三、创建事件调度器（自动执行）
3.1 开启事件调度器
-- 检查是否开启 show variables like 'event_scheduler'; -- 开启（临时生效） set global event_scheduler = on; -- 永久开启（修改配置文件 my.cnf 或 my.ini） -- 在 [mysqld] 下添加： -- event_scheduler = on
3.2 创建每日执行事件
每小时执行 + 只更新当天，第二天凌晨固化（推荐）
-- 1. 每小时执行：更新当天数据（实时查询用） create event evt_hourly_update_today on schedule every 1 hour starts concat(curdate(), ' 01:00:00') do call sp_update_daily_face_stats(curdate()); -- 2. 每天凌晨2点：执行昨天的数据（固化，用于历史报表） create event evt_daily_finalize_yesterday on schedule every 1 day starts concat(curdate() + interval 1 day, ' 02:00:00') do call sp_update_daily_face_stats(date_sub(curdate(), interval 1 day));
执行时间线： 08-06 01:00：更新 08-06 00:00-01:00 的数据 08-06 02:00：更新 08-06 00:00-02:00 的数据（覆盖） 08-06 03:00：更新 08-06 00:00-03:00 的数据（覆盖） ... 08-06 23:00：更新 08-06 00:00-23:00 的数据（覆盖） 08-07 00:00：更新 08-06 全天数据（覆盖） 08-07 02:00：固化 08-06 的数据（最终版本）
四、 管理事件
-- 查看所有事件 show events; -- 启用事件:每小时执行：更新当天数据（实时查询用） alter event evt_hourly_update_today enable; -- 启用事件:每天凌晨2点：执行昨天的数据（固化，用于历史报表） alter event evt_daily_finalize_yesterday enable; -- 查看事件详细信息 show create event evt_hourly_update_today; show create event evt_daily_finalize_yesterday; -- 暂停事件 alter event evt_hourly_update_today disable; -- 删除事件 drop event if exists evt_hourly_update_today;
五、业务查询sql（毫秒级响应）
5.1 查询月度日均
select
date_format(stat_date, '%y-%m') as month,
round(
sum(daily_unique_person) /
count(case when daily_unique_person > 0 then 1 end),
0
) as value
from xy_daily_face_stats
where stat_date >= date_format(curdate(), '%y-01-01')
and stat_date <= curdate()
group by date_format(stat_date, '%y-%m')
order by month;
总结
到此这篇关于mysql创建每日预聚合表、增量更新存储过程、启动事件的文章就介绍到这了,更多相关mysql创建每日预聚合表内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论