안녕하세요.
검색하고 고민도 하고 쿼리르 만들어봤는데 결국 실패하여 고수님들께 요청 드립니다.
-- 고장의 원리 입니다.
TIC 이 4 일 경우 고장발생이 된 시간이며 그 외 숫자인 경우 고장발생을 수리했다는 시간입니다.
'4','4' 가 연속적으로 나올경우는 첫번째 '4' 이외의 정보는 허수로 계산을 하지 않습니다.
같은 원리로 '4' 이외의 정보 또 한 연속으로 나오면 첫번째 정보만 실수이며 나머지는 허수로 계산을 하지 않습니다.
예를 들어{ '4' '20171214101111'},{ '4', '20171214101112'}, {'4', '20171214101113'}, {'2', '20171214101114'}, {'2','201714101115'} 이렇게 있다면
고장발생 시간은 '20171214101111' 이며 고장정지 시간은 '20171214101114' 이며 수리시간 차이는 3초입니다.
--관리의 원리입니다.
관리의 원리는 고장과 같으나 관리는 TIC 정보가 '3', '1' 이 된다는 것입니다.
첫번째 데이터는 '4' 가 아닌 '2','3','1' 데이터가 올 수 있습니다.
하기는 제가 테스트를 해본 내용인데
이상하게 고장에서 예를 들었던 내용 중에 고장정지 시간이 앞이 아닌 마지막 내용이 찍혀서 원하는 결과 값이 안나오더라구요 ㅠ.ㅠ
고수님들은 혹시 가능할 거 같아서 글을 올립니다.
아쉽게도 피벗은 사용이 불가능 합니다.
감사합니다.
WITH FLAG AS
(
SELECT 1 SEQ, '4' TIC, '20171209101010' AS ST_DT FROM DUAL UNION ALL -- 첫번째는 TIC 에는 '2', '1','3' 이 올 수 있습니다.
SELECT 2 SEQ, '3' TIC, '20171209101012' AS ST_DT FROM DUAL UNION ALL
SELECT 3 SEQ, '2' TIC, '20171209101013' AS ST_DT FROM DUAL UNION ALL
SELECT 4 SEQ, '4' TIC, '20171209101014' AS ST_DT FROM DUAL UNION ALL
SELECT 5 SEQ, '4' TIC, '20171209101016' AS ST_DT FROM DUAL UNION ALL
SELECT 6 SEQ, '2' TIC, '20171209101018' AS ST_DT FROM DUAL UNION ALL
SELECT 7 SEQ, '2' TIC, '20171209101020' AS ST_DT FROM DUAL UNION ALL
SELECT 8 SEQ, '4' TIC, '20171209101021' AS ST_DT FROM DUAL UNION ALL
SELECT 9 SEQ, '2' TIC, '20171209102022' AS ST_DT FROM DUAL UNION ALL
SELECT 10 SEQ, '3' TIC, '20171209102023' AS ST_DT FROM DUAL UNION ALL
SELECT 11 SEQ, '1' TIC, '20171209102025' AS ST_DT FROM DUAL UNION ALL
SELECT 12 SEQ, '2' TIC, '20171209102027' AS ST_DT FROM DUAL UNION ALL
SELECT 13 SEQ, '3' TIC, '20171209102030' AS ST_DT FROM DUAL UNION ALL
SELECT 14 SEQ, '4' TIC, '20171209102031' AS ST_DT FROM DUAL UNION ALL
SELECT 15 SEQ, '1' TIC, '20171209103033' AS ST_DT FROM DUAL UNION ALL
SELECT 16 SEQ, '2' TIC, '20171209103034' AS ST_DT FROM DUAL UNION ALL
SELECT 17 SEQ, '3' TIC, '20171209103038' AS ST_DT FROM DUAL UNION ALL
SELECT 18 SEQ, '4' TIC, '20171209103039' AS ST_DT FROM DUAL UNION ALL
SELECT 19 SEQ, '4' TIC, '20171209103040' AS ST_DT FROM DUAL UNION ALL
SELECT 20 SEQ, '4' TIC, '20171209103041' AS ST_DT FROM DUAL
)
SELECT '고장' AS NAME
,TO_CHAR(MAX(START_DATE), 'YYYY-MM-DD HH24:MI:SS') AS START_TIME
,TO_CHAR(MAX(END_DATE), 'YYYY-MM-DD HH24:MI:SS') AS END_TIME
,ORD_SEQ
,SUBSTR(TO_CHAR(NUMTODSINTERVAL(ROUND((MAX(END_DATE)-MAX(START_DATE))*60*60*24), 'SECOND')),12,8) AS GAP_TIME
FROM
(
SELECT DECODE(A.TIC, '4', B.ST_DT) AS START_DATE
,(CASE WHEN A.TIC <> '4' THEN B.ST_DT ELSE NULL END) AS END_DATE
,ROW_NUMBER() OVER(PARTITION BY (CASE WHEN OTC = 'P' THEN PREV ELSE NEXTS END) ORDER BY A.ST_DT) AS ORD_SEQ
,PREV
,NEXTS
,A.SEQ
,A.TIC
,A.ST_DT
FROM FLAG A
,(SELECT TO_DATE(ST_DT, 'YYYYMMDDHH24MISS') AS ST_DT
,LAG(DECODE(TIC, '4', '4', '2')) OVER(ORDER BY ST_DT) AS PREV
,LEAD(DECODE(TIC, '4', '4', '2')) OVER(ORDER BY ST_DT) AS NEXTS
,SEQ
,OTC
,TIC
FROM FLAG
,(SELECT CASE WHEN TIC <> '4' THEN 'N' ELSE 'P' END AS OTC
FROM FLAG
WHERE ROWNUM = 1) B
)B
WHERE A.SEQ = B.SEQ
AND DECODE(A.TIC, '4', '4', '2') <> (CASE WHEN OTC = 'P' THEN PREV ELSE NEXTS END)
ORDER BY A.ST_DT
)
GROUP BY ORD_SEQ
UNION ALL
SELECT '관리' AS NAME
,TO_CHAR(MAX(START_DATE), 'YYYY-MM-DD HH24:MI:SS') AS START_TIME
,TO_CHAR(MAX(END_DATE), 'YYYY-MM-DD HH24:MI:SS') AS END_TIME
,ORD_SEQ
,SUBSTR(TO_CHAR(NUMTODSINTERVAL(ROUND((MAX(END_DATE)-MAX(START_DATE))*60*60*24), 'SECOND')),12,8) AS GAP_TIME
FROM
(
SELECT (CASE WHEN A.TIC IN ('3', '1') THEN TO_DATE(A.ST_DT, 'YYYYMMDDHH24MISS') ELSE NULL END) AS START_DATE
,(CASE WHEN A.TIC NOT IN ('3', '1') THEN TO_DATE(A.ST_DT, 'YYYYMMDDHH24MISS') ELSE NULL END) AS END_DATE
,ROW_NUMBER() OVER(PARTITION BY (CASE WHEN OTC = 'P' THEN PREV ELSE NEXTS END) ORDER BY A.ST_DT) AS ORD_SEQ
,PREV
,NEXTS
,A.SEQ
,A.TIC
,A.ST_DT
FROM FLAG A
,(SELECT ST_DT
,LAG((CASE WHEN TIC IN ('3', '1') THEN '3' ELSE '2' END)) OVER(ORDER BY ST_DT) AS PREV
,LEAD((CASE WHEN TIC IN ('3', '1') THEN '3' ELSE '2' END)) OVER(ORDER BY ST_DT) AS NEXTS
,SEQ
,TIC
,OTC
FROM FLAG
,(SELECT CASE WHEN TIC IN ('3', '1') THEN 'P' ELSE 'N' END AS OTC
FROM FLAG
WHERE ROWNUM = 1) B
)B
WHERE A.SEQ = B.SEQ
AND (CASE WHEN A.TIC IN ('3', '1') THEN '3' ELSE '2' END) <> (CASE WHEN OTC = 'P' THEN PREV ELSE NEXTS END)
ORDER BY A.ST_DT
)
GROUP BY ORD_SEQ
ORDER BY NAME, ORD_SEQ
|