19.00为加班，查询每人的加班平均时长（每次加班的平均数）

Question

CREATE TABLE IF NOT EXISTS attendance (
id int(11) NOT NULL AUTO_INCREMENT,
time datetime NOT NULL COMMENT '打卡时间',
employee_id varchar(30) NOT NULL COMMENT '员工id',
employee_name varchar(50) DEFAULT NULL COMMENT '员工姓名',
workcode varchar(30) DEFAULT NULL,
status varchar(30) DEFAULT NULL,
authority varchar(30) DEFAULT NULL,
card_src varchar(30) DEFAULT NULL,
type varchar(30) DEFAULT NULL,
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
UNIQUE KEY employee_time_UNIQUE (time,employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

首先，需要查询出所有的加班记录，可以通过以下SQL语句实现：

SELECT employee_id, employee_name, AVG(TIMESTAMPDIFF(MINUTE, time, ADDTIME(DATE(time), '19:00:00'))) AS average_overtime_duration
FROM attendance
WHERE time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND type = 'overtime'
GROUP BY employee_id, employee_name;

其中，TIMESTAMPDIFF(MINUTE, time, ADDTIME(DATE(time), '19:00:00'))计算的是每次加班的时长（单位为分钟），再通过AVG函数计算每个员工加班的平均时长。

另外，WHERE条件中设置时间范围，只统计最近一个月内的加班记录，并且限定加班类型为'overtime'。