如何通过SQL定时事件将maintenance_tasks表按员工按日汇总至新表

前置步骤：创建日统计汇总表 #

首先需要新建存放汇总结果的统计表，示例建表语句如下（可根据实际业务调整字段类型）：

CREATE TABLE IF NOT EXISTS employee_daily_maintenance_stats (
    emp_id INT NOT NULL COMMENT '员工ID',
    stat_date DATE NOT NULL COMMENT '统计日期',
    total_duration DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '当日总任务耗时(单位可与原表保持一致，比如小时/分钟)',
    PRIMARY KEY (emp_id, stat_date) COMMENT '联合主键避免重复统计同一员工同一日期的数据'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '员工运维任务日统计汇总表';

补全定时事件逻辑 #

在你提供的事件框架基础上，补充INSERT SELECT聚合逻辑即可，默认每日6点统计前一日的所有任务数据（如果需要统计当日到6点的数据，可自行调整WHERE条件中的日期范围）：

DELIMITER //
CREATE EVENT IF NOT EXISTS fetch_24hr_data
    ON SCHEDULE
        EVERY 1 DAY
        STARTS( TIMESTAMP(CURRENT DATE) + INTERVAL 1 DAY + INTERVAL 6 HOUR)
    DO
    BEGIN
        -- 聚合前一日的员工任务耗时，插入汇总表，重复主键则更新总耗时避免重复插入
        INSERT INTO employee_daily_maintenance_stats (emp_id, stat_date, total_duration)
        SELECT 
            emp_id,
            DATE(submit_time) AS stat_date,
            SUM(task_duration) AS total_duration
        FROM maintenance_tasks
        -- 统计前一日的所有任务，可根据实际业务调整时间范围
        WHERE submit_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
          AND submit_time < CURDATE()
        GROUP BY emp_id, DATE(submit_time)
        ON DUPLICATE KEY UPDATE total_duration = VALUES(total_duration);
    END //
DELIMITER ;

注意事项 #

• 执行事件前需要确认MySQL事件调度器已开启，可执行SET GLOBAL event_scheduler = ON;临时开启，如需永久生效需要修改my.cnf配置文件添加event_scheduler=ON
• 以上SQL中的字段名（如emp_id员工ID字段、submit_time任务提交时间字段、task_duration任务耗时字段）需要和你实际maintenance_tasks表的字段保持一致，可按需调整
• 如果需要统计当日0点到6点的任务，将WHERE条件修改为submit_time >= CURDATE() AND submit_time < DATE_ADD(CURDATE(), INTERVAL 6 HOUR)即可

内容的提问来源于stack exchange，提问作者Pablo