mysql - 有没有办法计算 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、date、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             |

View on DB Fiddle

详细信息:

• 在 MySQL 8+ 中，我们可以使用分析/窗口函数，如 LAG() , 来计算员工在同一天的just previous check_out 时间。为此，我们的分区窗口将超过员工和 check_in 日期。分区窗口将按 check_in 时间排序，以便 LAG() 仅返回之前的条目。
• 我们可以使用 TimeStampDiff() 以分钟为单位确定 break_interval在当前行的 check_in 时间和上一行的 check_out 时间之间运行。
• 一旦我们确定了每一行的这些值；我们可以在子查询 ( Derived Table ) 中使用它们，并在特定日期为员工进行 break_interval 的总和聚合。此外，当员工在特定日期的最后一个条目中没有 check_out 时，获取 check_out 时间将需要一些特殊处理，因为您需要 NULL 并且 MAX(..) 不会返回 NULL。
• 您也可以查询sargeable通过在 check_in 时间避免使用 DATE() 函数并使用范围条件。