有没有办法计算first_check_in和last_check_out之间的总休息时间?

Question

MySQL 版本是8.0.16

我想获得给定日期的总休息时间，表格如下：

id | employee_id  | check_in             | check_out           
--------------------------------------------------------------
1 | 2103         | 2019-09-15 07:30:00  | 2019-09-15 09:20:00  
2 | 2103         | 2019-09-15 10:35:00  | null
3 | 2103         | 2019-09-16 08:00:00  | 2019-09-16 10:00:00
4 | 2103         | 2019-09-16 11:00:00  | 2019-09-16 18:00:00
5 | 2095         | 2019-09-16 08:30:00  | 2019-09-16 18:30:00

我的预期结果

id | employee_id  | check_in             | check_out          |  breaks 
--------------------------------------------------------------------------
1 | 2103         | 2019-09-15 07:30:00  | null               |  75
3 | 2103         | 2019-09-16 08:00:00  | 2019-09-16 18:00:00|  60
5 | 2095         | 2019-09-16 08:30:00  | 2019-09-16 18:30:00|  0

我的查询仅返回列employee_id、日期、first_check_in last_check_out

SELECT 
a1.employee_id,
cast(a1.check_in as date) AS date,
MIN(a1.check_in) AS first_check_in,
CASE WHEN a2.employee_id IS NULL 
THEN MAX(a1.check_out)     
ELSE NULL
END AS last_check_out
FROM  attendance_employees AS a1  
LEFT JOIN attendance_employees AS a2   
ON a1.employee_id = a2.employee_id
AND  CAST(a1.check_in AS date) =  CAST(a2.check_in AS date)
AND a2.check_out IS NULL
WHERE  1 = 1
AND DATE(a1.check_in) in ('2019-09-15', '2019-09-16')
GROUP BY  a1.employee_id, 
CAST(a1.check_in AS date)
ORDER BY  a1.employee_id,    
date

有没有办法在 MySQL 中计算这个休息时间？谢谢

更新了我的完整查询，其中包含预期工作和休息时间范围的时间段

SELECT  CONCAT(IFNULL(employees.first_name,''),' ',IFNULL(employees.surname,'')) as full_name,
teams.description,
time_periods.start_time as start,
time_periods.end_time as end,
time_periods.time_period_type,
time_groups.name,
cast(a1.check_in as date) AS date,
MIN(a1.check_in) AS first_check_in,
CASE WHEN a2.employee_id IS NULL 
THEN MAX(a1.check_out)     
ELSE NULL
END AS last_check_out
FROM  attendance_employees AS a1  
LEFT JOIN attendance_employees AS a2   
ON a1.employee_id = a2.employee_id
AND  CAST(a1.check_in AS date) =  CAST(a2.check_in AS date)
AND a2.check_out IS NULL
JOIN employees
ON employees.id = a1.employee_id
JOIN teams
ON employees.team_id = teams.id
JOIN time_groups
ON teams.time_group_id = time_groups.id
LEFT JOIN day_time_group_time_period
ON time_groups.id = day_time_group_time_period.time_group_id
AND day_time_group_time_period.day_id = 3
LEFT JOIN time_periods
ON day_time_group_time_period.time_period_id = time_periods.id
WHERE  1 = 1
AND DATE(a1.check_in) in ('2019-09-15', '2019-09-16') 
GROUP BY  a1.employee_id, 
CAST(a1.check_in AS date),    
a2.employee_id,
time_periods.start_time,
time_periods.end_time,
time_periods.time_period_type
ORDER BY  a1.employee_id,    
date

给出以下结果：

full_name| start   | end     | type| name     | date      | first_check_in     | last_check_out
-----------------------------------------------------------------------------------------------------
EMP1     | 08:00:00| 16:30:00| 1   | Day Shift| 2019-09-16| 2019-09-16 08:45:00| 2019-09-16 19:30:00
EMP1     | 10:00:00| 10:30:00| 2   | Day Shift| 2019-09-16| 2019-09-16 08:45:00| 2019-09-16 19:30:00
EMP1     | 12:00:00| 12:30:00| 2   | Day Shift| 2019-09-16| 2019-09-16 08:45:00| 2019-09-16 19:30:00
EMP2     | 08:00:00| 16:30:00| 1   | Day Shift| 2019-09-15| 2019-09-15 07:30:00| NULL
EMP2     | 10:00:00| 10:30:00| 2   | Day Shift| 2019-09-15| 2019-09-15 07:30:00| NULL
EMP2     | 12:00:00| 12:30:00| 2   | Day Shift| 2019-09-15| 2019-09-15 07:30:00| NULL

哪里 类型 1 表示预期工作时间范围和 类型 2 表示预期的休息时间范围

Answer 1

在您的原始问题中，以下方法可以工作：

SELECT   dt.employee_id,
Min(dt.check_in) AS check_in,
CASE WHEN COUNT(dt.check_out) = COUNT(*) THEN MAX(dt.check_out)
ELSE NULL
END AS check_out,
Sum(dt.break_interval) AS break_interval
FROM     (
SELECT   employee_id,
check_in,
check_out,
timestampdiff(minute, Lag(check_out) over w, check_in) AS break_interval
FROM     attendance_employees
WHERE    check_in BETWEEN '2019-09-15 00:00:00' AND      '2019-09-16 23:59:59'
WINDOW w AS (partition BY employee_id, date(check_in) ORDER BY check_in ASC) ) dt
GROUP BY dt.employee_id,
date(dt.check_in);
| employee_id | check_in            | check_out           | break_interval |
| ----------- | ------------------- | ------------------- | -------------- |
| 2095        | 2019-09-16 08:30:00 | 2019-09-16 18:30:00 |                |
| 2103        | 2019-09-15 07:30:00 |                     | 75             |
| 2103        | 2019-09-16 08:00:00 | 2019-09-16 18:00:00 | 60             |

在DB Fiddle上查看

详：

• 在MySQL 8+中，我们可以使用分析/窗口函数，如LAG()，来计算同一天员工的前check_out时间。为了获得这个，我们的分区窗口将在员工和check_in日期结束。分区窗口将按check_in时间排序，以便LAG()仅返回以前的条目。
• 我们可以在几分钟内确定break_interval，使用当前行check_in时间和前一行check_out时间之间的TimeStampDiff()函数。
• 一旦我们确定了每一行的这些值;我们可以在子查询(派生表(中使用它们，并对特定日期的员工进行break_interval的总和聚合。此外，当员工在特定日期的最后一个条目中没有check_out时，获得check_out时间将需要一些特殊处理，因为您需要在那里NULL并且MAX(..)不会返回NULL。
• 您还可以通过避免DATE()check_in时间和范围条件的函数来使查询可优化。