История перерывов сотрудников Mysql с разницей во времени

У меня есть данные биометрического журнала сотрудников с inoutmodeфлаг. Я пытаюсь получить подробный список времени перерыва и с разницей во времени.

inoutmode 4 как отрыв и 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

частично я получил записи, используя условие case, но 4 и 5 inoutmode должны быть в одной строке для вычисления разницы во времени. Есть идеи...

Любые предложения приветствуются.

Источник

2 ответа

Решение

Вот подход, использующий LEAD() а также LAG() Оконные функции доступны в MariaDB 10.2+ и MySQL 8+.

  • Когда конкретная строка InOutMode режим 4, это означает, что это break_outвремя. Теперь мы используемLAG()функция, чтобы получить непосредственно предыдущую строку для этого конкретного EmpID. Порядок определяется в зависимости от времени. Итак, если непосредственно предыдущая строкаInOutMode режим 5, что означает наличие соответствующего break_in время для этого break_out время, иначе null.
  • Аналогичный процесс выполняется для строки с InOutMode режим быть 5. Единственная разница на этот раз в том, что мы используемLEAD()функция вместо этого; потому что нам нужно сразу получить следующую строку и проверить,break_out или не.
  • Теперь нам просто нужно использовать этот набор результатов как производную таблицу иDISTINCTэто вне (потому что у нас будут повторяющиеся строки для каждого случая, когда есть break_in и break_out вместе). Кроме того, во внешнем запросе мы можем вычислить разницу во времени, используя TimeDiff() функциональность.

Следующий запрос выполняется для 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 Fiddle

Источник

Если я правильно понимаю проблему, вы хотите связать "4" записи с "5" записями. Ваш результирующий набор, похоже, не имеет ничего общего с вашими выборочными данными, поэтому за ним трудно следить.

Следующий подход связывает каждые 4/5 с ближайшим. Он делает это, назначая группировку, считая вверх для каждой "4" и вниз для каждой "5".

Однако это не совсем полное решение, потому что может быть несколько взлетов и падений, поскольку серии "4" и "5" идут подряд. Итак, он назначает вторичную группировку, чтобы разбить это.

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<>.

Источник
Другие вопросы по тегам