Mysql 직원 휴식 시간 기록(시간 차이 있음

Mysql 직원 휴식 시간 기록(시간 차이 있음

종업원의 바이오메트릭 로그 데이터를 가지고 있다.inoutmode플래그. 자세한 휴식 시간 목록과 시차를 알아내려고 합니다.

inoutmode4개는 탈옥으로, 5개는 침입으로.

INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (105, '2019-09-19', '14:00:13', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (105, '2019-09-19', '16:07:08', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (105, '2019-09-19', '16:07:18', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '15:44:26', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '16:37:58', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '20:01:11', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '20:01:25', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (235, '2019-09-19', '20:30:29', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (326, '2019-09-19', '15:58:30', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (326, '2019-09-19', '19:34:09', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (327, '2019-09-19', '15:44:19', 5);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (327, '2019-09-19', '15:55:37', 4);
INSERT INTO `tbl_downloadentry` (`EmpMachineID`, `shift_date`, `AttenTime`, `InOutMode`) VALUES (327, '2019-09-19', '19:59:38', 4);

원하는 출력은 다음과 같습니다.

| EmpMachineID | attendance_date | break_out  | break_in  | Diff      |
|--------------|-----------------|------------|-----------|-----------|
| 235          | 2019-09-19      | 15:44:26   |           |           |
|              | 2019-09-19      | 16:37:58   |           |           |
|              | 2019-09-19      |            | 20:01:11  |           |
|              | 2019-09-19      | 20:30:29   | 20:01:25  |    29:04  |
| 326          | 2019-09-19      | 19:34:09   | 15:58:30  | 03:35:39  |

나는 성과를 올리기 위해 최선을 다했다.다음은 시도한 쿼리입니다.

SELECT l2.empmachineid,
       l2.shift_date,
       l2.attentime,
       l2.inoutmode
FROM   tbl_downloadentry AS l2
WHERE  l2.inoutmode IN ( 5, 4 )
       AND l2.shift_date = "2019-09-19"
ORDER  BY l2.empmachineid,
          l2.shift_date,
          l2.attentime ASC  

MySQL 버전 = 10.3.17-MariaDB-1-log

SELECT l2.EmpMachineID, l2.shift_date, l2.InOutMode, 
       case when l2.InOutMode=5 then l2.AttenTime END AS BreakOut, 
       case when l2.InOutMode=4 then l2.AttenTime END AS BreakIn
FROM tbl_downloadentry AS l2
WHERE l2.InOutMode IN (5, 4) AND l2.shift_date="2019-09-19"
ORDER BY l2.EmpMachineID, l2.shift_date, l2.AttenTime ASC

부분적으로 i는 케이스 조건을 사용하여 레코드를 도착시켰지만, 4와 5의 inout 모드는 시간 차이를 계산하기 위해 한 줄에 있어야 합니다.생각나는 거 없어?

제안해주시면 감사하겠습니다.

다음은 MariaDB 10.2+와 MySQL 8+에서 사용할 수 있는 Window 기능을 활용하는 방법입니다.

• 특정 행이InOutMode모드는4즉, 이것은break_out시간. 이제 우리는LAG()특정 EMP에 대한 바로 이전 행을 가져오는 함수ID. 주문은 시간에 따라 정의됩니다.바로 앞 행이InOutMode모드는5즉, 우리가 대응책을 가지고 있다는 것을 의미합니다.break_in이럴 때break_out시간, 그 외null.
• 이 행에 대해서도 같은 프로세스가 실행됩니다.InOutMode모드 존재5이번과 다른 점이 있다면,LEAD()바로 다음 행을 가져와 다음 행이 다음 행인지 확인해야 합니다.break_out그렇지 않으면.
• 이제 이 결과 세트를 파생 테이블로 사용하면 됩니다.DISTINCT(break_in과 break_out이 함께 있는 모든 케이스에 대해 중복된 행이 있기 때문입니다).또한 외부 쿼리에서는 기능을 사용하여 시차를 계산할 수 있습니다.

다음 쿼리는 다음에 대해 수행됩니다.EmpID = 235데모용:

SELECT 
  DISTINCT 
    dt.*, 
    TIMEDIFF(dt.break_out, dt.break_in) AS diff 
FROM 
(
SELECT  
  EmpMachineID, 
  shift_date, 
  CASE InOutMode 
    WHEN 4 THEN AttenTime -- this is break_out row
    WHEN 5 THEN -- this is break_in row, find the break_out if exists
      CASE 
        WHEN LEAD(InOutMode) OVER w = 4 
        THEN LEAD(AttenTime) OVER w 
      END       
  END AS break_out, 
  CASE InOutMode 
    WHEN 5 THEN AttenTime -- this is break_in row
    WHEN 4 THEN   -- this is break_out row, find the break_in if exists
      CASE 
        WHEN LAG(InOutMode) OVER w = 5 
        THEN LAG(AttenTime) OVER w 
      END  
  END AS break_in
FROM tbl_downloadentry 
WHERE EmpMachineID = 235 
AND InOutMode IN (4,5) 
AND shift_date = '2019-09-19' 
WINDOW w AS (PARTITION BY EmpMachineID
             ORDER BY AttenTime ASC)
) AS dt;

결과

| EmpMachineID | shift_date | break_out | break_in | diff     |
| ------------ | ---------- | --------- | -------- | -------- |
| 235          | 2019-09-19 | 15:44:26  |          |          |
| 235          | 2019-09-19 | 16:37:58  |          |          |
| 235          | 2019-09-19 |           | 20:01:11 |          |
| 235          | 2019-09-19 | 20:30:29  | 20:01:25 | 00:29:04 |

---

DB Fielen 보기

내가 문제를 제대로 이해한다면, 당신은 "4"개의 레코드를 "5"개의 레코드에 연관짓고 싶어합니다.결과 집합은 표본 데이터와 거의 관련이 없는 것 같기 때문에 따라가기가 어렵습니다.

다음의 어프로치는, 각 4/5 를 가장 가까운 어프로치에 관련짓습니다.이것은 그룹화를 할당하고, 각 "4"에 대해 카운트업하고, 각 "5"에 대해 카운트다운함으로써 이루어집니다.

그러나 "4"와 "5"가 연속적으로 나열되기 때문에 여러 가지 기복이 있을 수 있기 때문에 이것이 전체 해결책은 아닙니다.이 문제를 해결하기 위해 2차 그룹을 할당합니다.

SELECT EmpMachineID, shift_date,
       MAX(CASE WHEN InOutMode = 4 THEN AttenTime END),
       MAX(CASE WHEN InOutMode = 5 THEN AttenTime END)
FROM (SELECT dl.*,
             SUM(InOutMode = group_first_InOutMode) OVER (PARTITION BY dl.EmpMachineID, dl.shift_date, dl.grouping ORDER BY dl.AttenTime) as secondary_grouping
      FROM (SELECT dl.*,
                   FIRST_VALUE(InOutMode) OVER (PARTITION BY dl.EmpMachineID, dl.shift_date, dl.grouping ORDER BY dl.AttenTime) as group_first_InOutMode
            FROM (SELECT dl.*,
                         SUM( CASE WHEN InOutMode = 4 THEN 1 WHEN InOutMode = 5 THEN -1 END) OVER
                              (PARTITION BY dl.EmpMachineID, dl.shift_date
                               ORDER BY dl.AttenTime
                              ) -
                              (CASE WHEN InOutMode = 5 THEN -1 ELSE 0 END) as grouping  -- subtract out "5"s on current row
                 FROM tbl_downloadentry dl
                 WHERE dl.InOutMode IN (5, 4) AND dl.shift_date = '2019-09-19'
                ) dl
           ) dl
     ) dl
GROUP BY EmpMachineID, shift_date, grouping, secondary_grouping;

여기 db<>fiddle이 있습니다.

언급URL : https://stackoverflow.com/questions/58037389/mysql-employee-break-time-history-with-time-difference