레코드를 단일 레코드로 선택하여 시간 차이를 확인하는 방법
직원의 움직임을 추적하기 위해 사용하는 서드파티 크로킹 어플리케이션이 있습니다.MariaDB로 동작하지만 상당히 지저분합니다(imo).프런트엔드는 윈도우 기반이며 모든 처리는 거기서 이루어집니다.문제는 실시간 보고를 위해 데이터를 추출하고 싶다는 것입니다.
클로킹 액티비티는 모두 1개의 테이블에 저장됩니다.아래 샘플을 참조하십시오.
INDX _DATE_ _TIME_ SURNAME WIEGANDID ZKINOUT
51 2018/09/03 5:52:04 Thakadu 000000000000AE 0
198 2018/09/03 14:04:29 Thakadu 000000000000AE 1
309 2018/09/03 21:54:06 Mabeo 000000000000BA 0
370 2018/09/04 5:47:20 Thakadu 000000000000AE 0
401 2018/09/04 6:00:09 Mabeo 000000000000BA 1
557 2018/09/04 14:04:57 Thakadu 000000000000AE 1
691 2018/09/04 21:53:33 Mabeo 000000000000BA 0
748 2018/09/05 5:47:20 Thakadu 000000000000AE 0
780 2018/09/05 6:00:34 Mabeo 000000000000BA 1
946 2018/09/05 14:05:32 Thakadu 000000000000AE 1
1089 2018/09/05 21:49:48 Mabeo 000000000000BA 0
1144 2018/09/06 5:50:41 Thakadu 000000000000AE 0
1174 2018/09/06 6:00:16 Mabeo 000000000000BA 1
1328 2018/09/06 14:09:28 Thakadu 000000000000AE 1
1482 2018/09/06 21:50:32 Mabeo 000000000000BA 0
1568 2018/09/07 5:58:48 Thakadu 000000000000AE 0
1555 2018/09/07 6:01:01 Mabeo 000000000000BA 1
1812 2018/09/07 14:05:47 Thakadu 000000000000AE 1
1845 2018/09/07 21:51:31 Mabeo 000000000000BA 0
문제는 Mabeo가 22:00:00부터 다음날 06:00:00까지 일하는 곳에서 발생합니다.또한 직원이 어떤 이유로 퇴실하지 않을 수도 있습니다.그러면 시스템은 시간을 기록하지 않고 자동으로 로그아웃합니다.
제가 보고 싶은 결과는 다음과 같습니다.
DATE_IN TIME_IN DATE_OUT TIME_OUT SURNAME WIEGANDID
2018/09/03 05:52:04 2018/09/03 14:04:29 Thakadu 000000000000AE
2018/09/03 21:54:06 2018/09/04 06:00:09 Mabeo 000000000000BA
2018/09/04 05:47:20 2018/09/04 14:04:57 Thakadu 000000000000AE
2018/09/04 21:53:33 2018/09/05 06:00:16 Mabeo 000000000000BA
이렇게 하면 각 이메일이 일정 기간 동안 기록된 실제 시간을 계산할 수 있습니다.
GROUP BY와 CASE를 사용하여 어느 정도 성공을 거둘 수 있었습니다만, 문제는 야간 근무에 있습니다.
어떤 도움이라도 주시면 감사하겠습니다.
---------------------------------------------------------------------
네, 기여해 주신 모든 분들께 감사드립니다.거의 다 맞혔는데 아직 100%는 못 맞췄어요.@rf1234의 제안대로 아래 코드를 사용했습니다.답변 감사합니다.
SELECT COALESCE (a.DATE_IN, b.DATE_IN) AS DATE_IN,
SUBSTR(COALESCE (a.TIME_IN, b.TIME_IN), 1, 8) AS TIME_IN,
CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN COALESCE (a.DATE_OUT,
b.DATE_OUT) ELSE '' END AS DATE_OUT,
CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN
SUBSTR(COALESCE (a.TIME_OUT, b.TIME_OUT), 1, 8) ELSE '' END
AS TIME_OUT,
COALESCE (a.SURNAME, b.SURNAME) AS SURNAME,
COALESCE (a.WIEGANDID, b.WIEGANDID) AS WIEGANDID,
CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN
TIMESTAMPDIFF(SECOND, a.DATE_TIME_IN, b.DATE_TIME_OUT) / 3600 ELSE '' END
AS HOURS_WORKED
FROM (
SELECT _DATE_ AS DATE_IN,
_TIME_ AS TIME_IN,
NULL AS DATE_OUT,
NULL AS TIME_OUT,
SURNAME,
WIEGANDID,
CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_IN
FROM _2018_09
WHERE ZKINOUT = 0 AND SURNAME = 'MABEO'
GROUP BY WIEGANDID, _DATE_ ) AS a,
(
SELECT NULL AS DATE_IN,
NULL AS TIME_IN,
_DATE_ AS DATE_OUT,
_TIME_ AS TIME_OUT,
SURNAME,
WIEGANDID,
CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_OUT
FROM _2018_09
WHERE ZKINOUT = 1 AND SURNAME = 'MABEO'
GROUP BY WIEGANDID, _DATE_) AS b
WHERE a.WIEGANDID = b.WIEGANDID
ORDER BY 1, 2, 3, 4
지금은 한 명의 직원만 뽑도록 코드를 수정했습니다.내가 얻는 결과는 거의 내가 원하는 것이다. 단, 테이블 a의 각 레코드와 테이블 b의 각 레코드가 결합되는 것 같다.다음은 결과의 샘플 이미지입니다.
마스터 ArsuKa가 말했듯이 데이터베이스를 재설계하면 물론 훨씬 더 쉽게 만들 수 있습니다.하지만 당신이 가진 것만으로 우리가 당신의 결과를 얻을 수 있을지 지켜봅시다.또, 임시 테이블내의 2개의 타임스탬프를 분산하는 것으로, 시프트가 이미 종료했을 경우에 대비해, 작업 시간(플로트 번호)을 계산합니다.임시 DATE_ 사용TIME 컬럼은 야간 근무 중인 직원의 문제를 해결합니다.근무조가 다른 날에 끝나든 간에 그 계산은 옳다.
SELECT COALESCE (a.DATE_IN, b.DATE_IN) AS DATE_IN,
SUBSTR(COALESCE (a.TIME_IN, b.TIME_IN), 1, 8) AS TIME_IN,
CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN COALESCE (a.DATE_OUT,
b.DATE_OUT) ELSE '' END AS DATE_OUT,
CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN
SUBSTR(COALESCE (a.TIME_OUT, b.TIME_OUT), 1, 8) ELSE '' END
AS TIME_OUT,
COALESCE (a.SURNAME, b.SURNAME) AS SURNAME,
COALESCE (a.WIEGANDID, b.WIEGANDID) AS WIEGANDID,
CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN
TIMESTAMPDIFF(SECOND, a.DATE_TIME_IN, b.DATE_TIME_OUT) / 3600 ELSE '' END
AS HOURS_WORKED
FROM (
SELECT _DATE_ AS DATE_IN,
_TIME_ AS TIME_IN,
NULL AS DATE_OUT,
NULL AS TIME_OUT,
SURNAME,
WIEGANDID,
CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_IN
FROM test
WHERE ZKINOUT = 0 ) AS a,
(
SELECT NULL AS DATE_IN,
NULL AS TIME_IN,
_DATE_ AS DATE_OUT,
_TIME_ AS TIME_OUT,
SURNAME,
WIEGANDID,
CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_OUT
FROM test
WHERE ZKINOUT = 1 ) AS b
WHERE a.WIEGANDID = b.WIEGANDID
HAVING HOURS_WORKED < 15 AND HOURS_WORKED <> ''
ORDER BY 1, 2, 3, 4
이것은 조금 복잡해 보이고 다음과 같은 처리를 합니다.2개의 임시 테이블 a와 b가 생성됩니다.다음은()의 SELECT 문입니다.1개의 임시 테이블에는 로그인 레코드와 로그아웃 레코드가 포함됩니다.각 "누락" 필드는 NULL로 설정됩니다.
그런 다음 WIEGANDID를 사용하여 이들 테이블을 서로 결합할 수 있습니다.COALESCE는 늘 값이 필터링되고 올바른 값을 포함하는 다른 임시 테이블의 값으로 대체되도록 합니다.DATE_ 체크TIME_OUT은 DATE_보다 커야 합니다.TIME_IN은 해당 워커가 아직 로그아웃하지 않은 경우 DATE_OUT 및 TIME_OUT을 비워 둡니다.
결과 테이블에 HOWS_WORKED라는 새로운 컬럼을 추가했습니다.변동 시간으로 작업한 시간이 포함되어 있습니다.아직 종료되지 않은 시프트는 결과 세트에서 무시됩니다(HOURS_WORKED <> ").중복을 배제하고 복잡함을 회피하기 위해 HOURS_WORKED > 15의 결과 세트를 필터링합니다.이는 실제로는 발생하지 않고 보통 2교대 사이에 2교대 시간이 있기 때문에 2교대를 짧게 잡을 수 없기 때문입니다.일반적인 솔루션이 아니라 이 특정 애플리케이션에 적합한 솔루션입니다.
위 문장은 CREATE VIEW 문에서 사용할 수 있습니다.그런 다음 원래 테이블 대신 뷰를 사용하여 작업할 수 있으므로 복잡성을 줄일 수 있습니다.
아래 그림은 귀하의 모든 샘플 기록을 바탕으로 한 위의 쿼리 결과를 보여줍니다.
나는 지난주에야 내 직원들의 펀치를 감시하는 앱을 만들었다.IN 및 PunchOUT 시간은 참석을 모니터링하는 데 소요되며, 따라서 데이터를 추가로 분석하기 위해 반나절이나 걸리는 휴가 등입니다.
제공된 데이터를 보고 분석 앱이 매우 단순해진 이유를 제안합니다.펀치에 타임스탬프 데이터 유형 사용IN과 Punch OUT을 사용하면 기존 열을 병합할 뿐만 아니라 작업 장애물을 제거할 수 있습니다.
IN 및 OUT 시간을 사용하여 "작업 시간"(단순 산술!)을 계산할 수 있습니다.플래그를 추가하여 야간 작업 여부(BETWIN 기능 사용) 등을 확인할 수 있습니다.바로 사용할 수 있는 심플한 기능으로 선도!
도움이 필요하시면 말씀해주세요...!
언급URL : https://stackoverflow.com/questions/52520904/how-to-select-records-into-a-single-record-to-determine-time-difference
'source' 카테고리의 다른 글
| 최대 절전 모드에서 결과 집합을 추출할 수 없습니다. (0) | 2022.12.18 |
|---|---|
| HashTables는 충돌에 어떻게 대처합니까? (0) | 2022.12.18 |
| 네이티브 코드를 호출하기 위해 유고 인민군 대신 JNI를 사용하시겠습니까? (0) | 2022.12.18 |
| LOCK TABLES가 테이블이 잠기지 않았다고 말한 후 UNION 쿼리에서 선택 (0) | 2022.12.18 |
| jQuery의 .bind() 대 .on() (0) | 2022.12.08 |