database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41495 게시물 읽기
No. 41495
시작과 끝정보 시간계산
작성자
오라클초보(msgzzang)
작성일
2017-12-14 10:32ⓒ
2017-12-14 10:49ⓜ
조회수
5,995

안녕하세요.

검색하고 고민도 하고 쿼리르 만들어봤는데 결국 실패하여 고수님들께 요청 드립니다.

-- 고장의 원리 입니다.

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

 

이 글에 대한 댓글이 총 7건 있습니다.

WITH flag AS
(
SELECT 1 seq, '4' tic, '20171209101010' st_dt FROM dual
UNION ALL SELECT  2, '3', '20171209101012' FROM dual
UNION ALL SELECT  3, '2', '20171209101013' FROM dual
UNION ALL SELECT  4, '4', '20171209101014' FROM dual
UNION ALL SELECT  5, '4', '20171209101016' FROM dual
UNION ALL SELECT  6, '2', '20171209101018' FROM dual
UNION ALL SELECT  7, '2', '20171209101020' FROM dual
UNION ALL SELECT  8, '4', '20171209101021' FROM dual
UNION ALL SELECT  9, '2', '20171209102022' FROM dual
UNION ALL SELECT 10, '3', '20171209102023' FROM dual
UNION ALL SELECT 11, '1', '20171209102025' FROM dual
UNION ALL SELECT 12, '2', '20171209102027' FROM dual
UNION ALL SELECT 13, '3', '20171209102030' FROM dual
UNION ALL SELECT 14, '4', '20171209102031' FROM dual
UNION ALL SELECT 15, '1', '20171209103033' FROM dual
UNION ALL SELECT 16, '2', '20171209103034' FROM dual
UNION ALL SELECT 17, '3', '20171209103038' FROM dual
UNION ALL SELECT 18, '4', '20171209103039' FROM dual
UNION ALL SELECT 19, '4', '20171209103040' FROM dual
UNION ALL SELECT 20, '4', '20171209103041' FROM dual
)
SELECT DECODE(tic, '4', '고장', '3', '관리') gb
     , st_dt
     , DECODE(tic, '4', e4, '3', e3) ed_dt
     , ROW_NUMBER() OVER(PARTITION BY tic ORDER BY st_dt) rn
     , ROUND( ( TO_DATE(DECODE(tic, '4', e4, '3', e3), 'yyyymmddhh24miss')
              - TO_DATE(st_dt, 'yyyymmddhh24miss')
              ) *24*60*60 ) sec
  FROM (SELECT tic
             , st_dt
             , LAG(tic, 1, '0') OVER(ORDER BY seq) lag_tic
             , LEAD(DECODE(tic, '4', '', st_dt) IGNORE NULLS)
                                           OVER(ORDER BY seq) e4
             , LEAD(DECODE(tic, '3', '', st_dt) IGNORE NULLS)
                                           OVER(ORDER BY seq) e3
          FROM (SELECT seq
                     , DECODE(tic, '1', '3', tic) tic
                     , st_dt
                  FROM flag
                )
        )
 WHERE tic != lag_tic
   AND tic IN ('4', '3')
 ORDER BY gb, st_dt
;

마농(manon94)님이 2017-12-14 14:09에 작성한 댓글입니다.
이 댓글은 2017-12-14 15:07에 마지막으로 수정되었습니다.

 마농님 답변 감사합니다.

 그런데 IGORE NULLS 부분에서 에러가 나네요 ㅠ.ㅠ

 전 다른 방법으로 이것저것 해 봤는데 아직도 실패 중인데...

 너무 너무 감사합니다.

오라클초보(msgzzang)님이 2017-12-14 14:48에 작성한 댓글입니다.

버전이 낮은 모양이네요.
ignore nulls 구문이 lag 함수에는 나중에 적용된 듯.
last_value 에서 ignore nulls 가 먼저 적용이 되었으니.
last_value 로 한번 해보세요.

WITH flag AS
(
SELECT 1 seq, '4' tic, '20171209101010' st_dt FROM dual
UNION ALL SELECT  2, '3', '20171209101012' FROM dual
UNION ALL SELECT  3, '2', '20171209101013' FROM dual
UNION ALL SELECT  4, '4', '20171209101014' FROM dual
UNION ALL SELECT  5, '4', '20171209101016' FROM dual
UNION ALL SELECT  6, '2', '20171209101018' FROM dual
UNION ALL SELECT  7, '2', '20171209101020' FROM dual
UNION ALL SELECT  8, '4', '20171209101021' FROM dual
UNION ALL SELECT  9, '2', '20171209102022' FROM dual
UNION ALL SELECT 10, '3', '20171209102023' FROM dual
UNION ALL SELECT 11, '1', '20171209102025' FROM dual
UNION ALL SELECT 12, '2', '20171209102027' FROM dual
UNION ALL SELECT 13, '3', '20171209102030' FROM dual
UNION ALL SELECT 14, '4', '20171209102031' FROM dual
UNION ALL SELECT 15, '1', '20171209103033' FROM dual
UNION ALL SELECT 16, '2', '20171209103034' FROM dual
UNION ALL SELECT 17, '3', '20171209103038' FROM dual
UNION ALL SELECT 18, '4', '20171209103039' FROM dual
UNION ALL SELECT 19, '4', '20171209103040' FROM dual
UNION ALL SELECT 20, '4', '20171209103041' FROM dual
)
SELECT DECODE(tic, '4', '고장', '3', '관리') gb
     , st_dt
     , DECODE(tic, '4', e4, '3', e3) ed_dt
     , ROW_NUMBER() OVER(PARTITION BY tic ORDER BY st_dt) rn
     , ROUND( ( TO_DATE(DECODE(tic, '4', e4, '3', e3), 'yyyymmddhh24miss')
              - TO_DATE(st_dt, 'yyyymmddhh24miss')
              ) *24*60*60 ) sec
  FROM (SELECT tic
             , st_dt
             , LAG(tic, 1, '0') OVER(ORDER BY seq) lag_tic
             , LAST_VALUE(DECODE(tic, '4', '', st_dt) IGNORE NULLS)
                                            OVER(ORDER BY seq DESC) e4
             , LAST_VALUE(DECODE(tic, '4', '', st_dt) IGNORE NULLS)
                                            OVER(ORDER BY seq DESC) e3
--             , LEAD(DECODE(tic, '4', '', st_dt) IGNORE NULLS)
--                                           OVER(ORDER BY seq) e4
--             , LEAD(DECODE(tic, '3', '', st_dt) IGNORE NULLS)
--                                           OVER(ORDER BY seq) e3
          FROM (SELECT seq
                     , DECODE(tic, '1', '3', tic) tic
                     , st_dt
                  FROM flag
                )
        )
 WHERE tic != lag_tic
   AND tic IN ('4', '3')
 ORDER BY gb, st_dt
;

 

마농(manon94)님이 2017-12-14 15:08에 작성한 댓글입니다.

마농님 감사합니다.

그런데 케이스가 다양한가 봅니다.

하기 처럼 데이터를 입력했더니 고장은 정확하게 올라오는데 관리 시간차는 0 으로 나오네요.

계속 귀찮게 해서 죄송합니다.

 

WITH flag AS

(
    SELECT 1  SEQ, '4' TIC, '20171213085822' AS ST_DT FROM DUAL UNION ALL
    SELECT 2  SEQ, '2' TIC, '20171213085850' AS ST_DT FROM DUAL UNION ALL
    SELECT 3  SEQ, '4' TIC, '20171213092150' AS ST_DT FROM DUAL UNION ALL
    SELECT 4  SEQ, '2' TIC, '20171213092208' AS ST_DT FROM DUAL UNION ALL
    SELECT 5  SEQ, '4' TIC, '20171213093109' AS ST_DT FROM DUAL UNION ALL
    SELECT 6  SEQ, '2' TIC, '20171213093202' AS ST_DT FROM DUAL UNION ALL
    SELECT 7  SEQ, '4' TIC, '20171213093251' AS ST_DT FROM DUAL UNION ALL
    SELECT 8  SEQ, '3' TIC, '20171213093323' AS ST_DT FROM DUAL UNION ALL
    SELECT 9  SEQ, '4' TIC, '20171213093324' AS ST_DT FROM DUAL UNION ALL
    SELECT 10 SEQ, '2' TIC, '20171213093330' AS ST_DT FROM DUAL UNION ALL
    SELECT 11 SEQ, '4' TIC, '20171213093416' AS ST_DT FROM DUAL UNION ALL
    SELECT 12 SEQ, '2' TIC, '20171213093449' AS ST_DT FROM DUAL UNION ALL
    SELECT 13 SEQ, '4' TIC, '20171213094756' AS ST_DT FROM DUAL UNION ALL
    SELECT 14 SEQ, '2' TIC, '20171213094759' AS ST_DT FROM DUAL UNION ALL
    SELECT 15 SEQ, '4' TIC, '20171213094902' AS ST_DT FROM DUAL UNION ALL
    SELECT 16 SEQ, '2' TIC, '20171213094904' AS ST_DT FROM DUAL UNION ALL
    SELECT 17 SEQ, '4' TIC, '20171213094915' AS ST_DT FROM DUAL UNION ALL
    SELECT 18 SEQ, '2' TIC, '20171213094922' AS ST_DT FROM DUAL UNION ALL
    SELECT 19 SEQ, '4' TIC, '20171213094928' AS ST_DT FROM DUAL UNION ALL
    SELECT 20 SEQ, '2' TIC, '20171213094944' AS ST_DT FROM DUAL UNION ALL
    SELECT 21 SEQ, '4' TIC, '20171213095455' AS ST_DT FROM DUAL UNION ALL
    SELECT 22 SEQ, '2' TIC, '20171213095525' AS ST_DT FROM DUAL UNION ALL
    SELECT 23 SEQ, '4' TIC, '20171213101247' AS ST_DT FROM DUAL UNION ALL
    SELECT 24 SEQ, '3' TIC, '20171213101259' AS ST_DT FROM DUAL UNION ALL
    SELECT 25 SEQ, '2' TIC, '20171213101300' AS ST_DT FROM DUAL UNION ALL
    SELECT 26 SEQ, '4' TIC, '20171213102155' AS ST_DT FROM DUAL UNION ALL
    SELECT 27 SEQ, '2' TIC, '20171213102212' AS ST_DT FROM DUAL UNION ALL
    SELECT 28 SEQ, '4' TIC, '20171213103439' AS ST_DT FROM DUAL UNION ALL
    SELECT 29 SEQ, '3' TIC, '20171213103454' AS ST_DT FROM DUAL UNION ALL
    SELECT 30 SEQ, '2' TIC, '20171213103456' AS ST_DT FROM DUAL UNION ALL
    SELECT 31 SEQ, '3' TIC, '20171213103501' AS ST_DT FROM DUAL UNION ALL
    SELECT 32 SEQ, '2' TIC, '20171213103545' AS ST_DT FROM DUAL UNION ALL
    SELECT 33 SEQ, '3' TIC, '20171213103551' AS ST_DT FROM DUAL UNION ALL
    SELECT 34 SEQ, '2' TIC, '20171213103557' AS ST_DT FROM DUAL UNION ALL
    SELECT 35 SEQ, '3' TIC, '20171213103601' AS ST_DT FROM DUAL UNION ALL
    SELECT 36 SEQ, '2' TIC, '20171213103612' AS ST_DT FROM DUAL UNION ALL
    SELECT 37 SEQ, '4' TIC, '20171213103801' AS ST_DT FROM DUAL UNION ALL
    SELECT 38 SEQ, '2' TIC, '20171213103818' AS ST_DT FROM DUAL UNION ALL
    SELECT 39 SEQ, '4' TIC, '20171213103820' AS ST_DT FROM DUAL UNION ALL
    SELECT 40 SEQ, '2' TIC, '20171213103823' AS ST_DT FROM DUAL UNION ALL
    SELECT 41 SEQ, '3' TIC, '20171213104240' AS ST_DT FROM DUAL UNION ALL
    SELECT 42 SEQ, '2' TIC, '20171213105909' AS ST_DT FROM DUAL UNION ALL
    SELECT 43 SEQ, '3' TIC, '20171213111559' AS ST_DT FROM DUAL
)
SELECT DECODE(tic, '4', '고장', '3', '관리') gb
     , st_dt
     , DECODE(tic, '4', e4, '3', e3) ed_dt
     , ROW_NUMBER() OVER(PARTITION BY tic ORDER BY st_dt) rn
     , ROUND( ( TO_DATE(DECODE(tic, '4', e4, '3', e3), 'yyyymmddhh24miss')
              - TO_DATE(st_dt, 'yyyymmddhh24miss')
              ) *24*60*60 ) sec
  FROM (SELECT tic
             , st_dt
             , LAG(tic, 1, '0') OVER(ORDER BY seq) lag_tic
             , LAST_VALUE(DECODE(tic, '4', '', st_dt) IGNORE NULLS)
                                            OVER(ORDER BY seq DESC) e4
             , LAST_VALUE(DECODE(tic, '4', '', st_dt) IGNORE NULLS)
                                            OVER(ORDER BY seq DESC) e3
 
          FROM (SELECT seq
                     , DECODE(tic, '1', '3', tic) tic
                     , st_dt
                  FROM flag
                )
        )
 WHERE tic != lag_tic
   AND tic IN ('4', '3')
 ORDER BY gb, st_dt
;
오라클초보님이 2017-12-14 15:41에 작성한 댓글입니다. Edit

 아 감사합니다. 

해결 되었습니다. 

 

, LAST_VALUE(DECODE(tic, '4', '', st_dt) IGNORE NULLS)
                                            OVER(ORDER BY seq DESC) e3 --> 이부분이 "3" 이네요..
 
 
너무너무 감사합니다.
 
항상 행복하세요..^^
오라클초보님이 2017-12-14 15:51에 작성한 댓글입니다. Edit

오타가 있었네요. '4' -> '3'
그리고 다시 생각해 보니
중복 제거 후에 lead 를 사용한다면 ignore nulls 가 필요 없겟네요.


SELECT DECODE(tic, '4', '고장', '3', '관리') gb
     , st_dt
     , ed_dt
     , ROW_NUMBER() OVER(PARTITION BY tic ORDER BY seq) rn
     , ROUND ( ( TO_DATE(ed_dt, 'yyyymmddhh24miss')
               - TO_DATE(st_dt, 'yyyymmddhh24miss')
               ) * 24*60*60 ) sec
  FROM (SELECT seq, tic, st_dt
             , LEAD(st_dt) OVER(ORDER BY seq) ed_dt
          FROM (SELECT seq, tic, st_dt
                     , LAG(tic, 1, '0') OVER(ORDER BY seq) lag_tic
                  FROM (SELECT seq
                             , DECODE(tic, '1', '3', tic) tic
                             , st_dt
                          FROM flag
                        )
                ) a
         WHERE tic != lag_tic
        )
 WHERE tic IN ('4','3')
;

마농(manon94)님이 2017-12-14 15:57에 작성한 댓글입니다.

 너무 너무 감사합니다.

 어쩜 이리 간결하고 쉽게 해결하시는지 항상

 존경하고 감탄하고 있습니다.

 전 것으로 했는데 마지막 것으로 다시 변경해야 겠네요.^^

오라클초보님이 2017-12-14 16:20에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41499DBF 파일 확장관련입니다. [1]
전성용
2017-12-18
5323
41497solaris 11 proc 사용해보신분?
신우철
2017-12-16
5640
41496아;; 단순쿼리인데 도저히 원인을 모르겠네요;; [2]
정군
2017-12-14
6300
41495시작과 끝정보 시간계산 [7]
오라클초보
2017-12-14
5995
414949억건 업테이트와 인서트
엑소
2017-12-13
5698
41493오라클 복구 여부 확인좀 부탁드립니다. [1]
복구확인
2017-12-12
5425
41492rownum 구간 null row까지 전부 표현 가능한가요? [2]
김기훈
2017-12-11
5644
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다