공통쿼리

[하태욱]

########################
#                      #
#  OOZIE 작업 시간 측정     #
#                      #
########################
/* PK : MST_ID + MST_START_TIME + LV01_ID + LV02_ID */
SELECT MST.MST_ID                 /* PK */
 , MST.MST_APP_NAME           /* WF명 */
 , MST.MST_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , MST.MST_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , MST.MST_START_TIME         /* 최종 시작 일시 */ 
 , MST.MST_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , MST.MST_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , TIMESTAMPDIFF(SECOND, MST.MST_START_TIME, MST.MST_END_TIME ) AS MST_WF_TIME_SEC
 , TIMESTAMPDIFF(MINUTE, MST.MST_START_TIME, MST.MST_END_TIME ) AS MST_WF_TIME_MIN  
 , ROUND(TIMESTAMPDIFF(MINUTE, MST.MST_START_TIME, MST.MST_END_TIME )/60,1) AS MST_WF_TIME_HOUR    
 , B.ID                 AS LV01_ID                 /* PK */
 , B.APP_NAME           AS LV01_APP_NAME           /* WF명 */
 , B.CREATED_TIME       AS LV01_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , B.LAST_MODIFIED_TIME AS LV01_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , B.PARENT_ID          AS LV01_PARENT_ID          /* 상위 WF ID */  
 , B.START_TIME         AS LV01_START_TIME         /* 최종 시작 일시 */ 
 , B.END_TIME           AS LV01_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , B.STATUS             AS LV01_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , TIMESTAMPDIFF(SECOND, B.START_TIME, B.END_TIME ) AS LV01_WF_TIME_SEC
 , TIMESTAMPDIFF(MINUTE, B.START_TIME, B.END_TIME ) AS LV01_WF_TIME_MIN

 , C.ID                 AS LV02_ID                 /* PK */
 , C.APP_NAME           AS LV02_APP_NAME           /* WF명 */
 , C.CREATED_TIME       AS LV02_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , C.LAST_MODIFIED_TIME AS LV02_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , C.PARENT_ID          AS LV02_PARENT_ID          /* 상위 WF ID */  
 , C.START_TIME         AS LV02_START_TIME         /* 최종 시작 일시 */ 
 , C.END_TIME           AS LV02_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , C.STATUS             AS LV02_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , TIMESTAMPDIFF(SECOND, C.START_TIME, C.END_TIME ) AS LV02_WF_TIME_SEC
 , TIMESTAMPDIFF(MINUTE, C.START_TIME, C.END_TIME ) AS LV02_WF_TIME_MIN

 , D.ID                 AS LV03_ID                 /* PK */
 , D.APP_NAME           AS LV03_APP_NAME           /* WF명 */
 , D.CREATED_TIME       AS LV03_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , D.LAST_MODIFIED_TIME AS LV03_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , D.PARENT_ID          AS LV03_PARENT_ID          /* 상위 WF ID */  
 , D.START_TIME         AS LV03_START_TIME         /* 최종 시작 일시 */ 
 , D.END_TIME           AS LV03_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , D.STATUS             AS LV03_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , TIMESTAMPDIFF(SECOND, D.START_TIME, D.END_TIME ) AS LV03_WF_TIME_SEC
 , TIMESTAMPDIFF(MINUTE, D.START_TIME, D.END_TIME ) AS LV03_WF_TIME_MIN  
  FROM (
/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                 AS MST_ID                 /* PK */
 , A.APP_NAME           AS MST_APP_NAME           /* WF명 */
 , A.APP_PATH           AS MST_APP_PATH           /* 경로 */
 , A.CREATED_TIME       AS MST_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , A.LAST_MODIFIED_TIME AS MST_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , A.PARENT_ID          AS MST_PARENT_ID          /* 상위 WF ID */  
 , A.START_TIME         AS MST_START_TIME         /* 최종 시작 일시 */ 
 , A.END_TIME           AS MST_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */   
 , A.STATUS             AS MST_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
  FROM oz.WF_JOBS A
 WHERE 1=1
   AND A.PARENT_ID LIKE '%oozie-oozi-C@%'  /* 최상위 JOB  */
       ) MST
 LEFT OUTER JOIN oz.WF_JOBS B
         ON MST.MST_ID = B.PARENT_ID
 LEFT OUTER JOIN oz.WF_JOBS C
         ON B.ID = C.PARENT_ID
 LEFT OUTER JOIN oz.WF_JOBS D
         ON C.ID = D.PARENT_ID  
 WHERE 1=1
   AND MST.MST_APP_NAME = 'wf_dd_job_all'   /* 조회 WF 명 */
   AND C.ID = 'wf_dd_mart_ord_dtl'
 ORDER BY MST.MST_CREATED_TIME DESC 
        , B.CREATED_TIME
        , C.CREATED_TIME
        , MST.MST_APP_NAME
;


/* PK : MST_ID + MST_START_TIME + LV01_ID */
SELECT MST.MST_ID                 /* PK */
 , MST.MST_APP_NAME           /* WF명 */
 , MST.MST_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , MST.MST_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , MST.MST_START_TIME         /* 최종 시작 일시 */ 
 , MST.MST_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , MST.MST_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , TIMESTAMPDIFF(SECOND, MST.MST_START_TIME, MST.MST_END_TIME ) AS MST_WF_TIME_SEC
 , TIMESTAMPDIFF(MINUTE, MST.MST_START_TIME, MST.MST_END_TIME ) AS MST_WF_TIME_MIN  
 , ROUND(TIMESTAMPDIFF(MINUTE, MST.MST_START_TIME, MST.MST_END_TIME )/60,1) AS MST_WF_TIME_HOUR    
 , B.ID                 AS LV01_ID                 /* PK */
 , B.APP_NAME           AS LV01_APP_NAME           /* WF명 */
 , B.CREATED_TIME       AS LV01_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , B.LAST_MODIFIED_TIME AS LV01_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , B.PARENT_ID          AS LV01_PARENT_ID          /* 상위 WF ID */  
 , B.START_TIME         AS LV01_START_TIME         /* 최종 시작 일시 */ 
 , B.END_TIME           AS LV01_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , B.STATUS             AS LV01_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , TIMESTAMPDIFF(SECOND, B.START_TIME, B.END_TIME ) AS LV01_WF_TIME_SEC
 , TIMESTAMPDIFF(MINUTE, B.START_TIME, B.END_TIME ) AS LV01_WF_TIME_MIN  
  FROM (
/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                 AS MST_ID                 /* PK */
 , A.APP_NAME           AS MST_APP_NAME           /* WF명 */
 , A.APP_PATH           AS MST_APP_PATH           /* 경로 */
 , A.CREATED_TIME       AS MST_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , A.LAST_MODIFIED_TIME AS MST_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , A.PARENT_ID          AS MST_PARENT_ID          /* 상위 WF ID */  
 , A.START_TIME         AS MST_START_TIME         /* 최종 시작 일시 */ 
 , A.END_TIME           AS MST_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */   
 , A.STATUS             AS MST_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
  FROM oz.WF_JOBS A
 WHERE 1=1
   AND A.PARENT_ID LIKE '%oozie-oozi-C@%'  /* 최상위 JOB  */
       ) MST
 LEFT OUTER JOIN oz.WF_JOBS B
         ON MST.MST_ID = B.PARENT_ID
 WHERE 1=1
   AND MST.MST_APP_NAME = 'wf_dd_job_all'   /* 조회 WF 명 */
 ORDER BY MST.MST_CREATED_TIME DESC 
        , MST.MST_APP_NAME
;

/* 메인 WF별 구동시간 조회 */
SELECT A.MST_APP_NAME 
 , AVG(A.MST_WF_TIME_MIN)
 , MAX(A.MST_WF_TIME_MIN)
 , MIN(A.MST_WF_TIME_MIN)  
  FROM (
/* PK : MST_ID + MST_START_TIME  */
SELECT MST.MST_ID                 /* PK */
 , MST.MST_APP_NAME           /* WF명 */
 , MST.MST_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , MST.MST_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , MST.MST_START_TIME         /* 최종 시작 일시 */ 
 , MST.MST_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , MST.MST_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , TIMESTAMPDIFF(SECOND, MST.MST_START_TIME, MST.MST_END_TIME ) AS MST_WF_TIME_SEC
 , TIMESTAMPDIFF(MINUTE, MST.MST_START_TIME, MST.MST_END_TIME ) AS MST_WF_TIME_MIN  
 , ROUND(TIMESTAMPDIFF(MINUTE, MST.MST_START_TIME, MST.MST_END_TIME )/60,1) AS MST_WF_TIME_HOUR    
  FROM (
/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                 AS MST_ID                 /* PK */
 , A.APP_NAME           AS MST_APP_NAME           /* WF명 */
 , A.APP_PATH           AS MST_APP_PATH           /* 경로 */
 , A.CREATED_TIME       AS MST_CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , A.LAST_MODIFIED_TIME AS MST_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , A.PARENT_ID          AS MST_PARENT_ID          /* 상위 WF ID */  
 , A.START_TIME         AS MST_START_TIME         /* 최종 시작 일시 */ 
 , A.END_TIME           AS MST_END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */   
 , A.STATUS             AS MST_STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
  FROM oz.WF_JOBS A
 WHERE 1=1
   AND A.PARENT_ID LIKE '%oozie-oozi-C@%'  /* 최상위 JOB  */
   ) MST
 WHERE 1=1
   AND MST.MST_APP_NAME = 'wf_dd_job_all'   /* 조회 WF 명 */  
   AND MST.MST_STATUS = 'SUCCEEDED' /* 성공건만 - 정확한것은 아님. 하위작업 에러가능존재 */ 
   ) A
 GROUP BY A.MST_APP_NAME 
 ORDER BY A.MST_APP_NAME
;


/* NODE 단위 시간의 총합이므로, 병렬 처리 시간이 반영되지 않는다. */
/* NODE 단위 조회  */
SELECT H.*
  FROM (
/* 가장 오랜 시간이 걸린 node 추출 */
SELECT MST.BOTTOM_ID
 , MST.BOTTOM_APP_NAME
 , MST.BOTTOM_CREATED_TIME
 , MST.BOTTOM_START_TIME
 , MST.BOTTOM_END_TIME
 , TIMESTAMPDIFF(SECOND,MST.BOTTOM_START_TIME, MST.BOTTOM_END_TIME ) AS BOTTOM_WF_TIME_SEC
 , TIMESTAMPDIFF(MINUTE,MST.BOTTOM_START_TIME, MST.BOTTOM_END_TIME ) AS BOTTOM_WF_TIME_MIN    
 , MST.BOTTOM_STATUS
 , MST.BOTTOM_PARENT_ID  
 , MST.NODE_ID
 , MST.NODE_CREATED_TIME
 , MST.NODE_START_TIME
 , MST.NODE_END_TIME
 , TIMESTAMPDIFF(SECOND, MST.NODE_START_TIME, MST.NODE_END_TIME ) AS BOTTOM_NODE_TIME_SEC  
 , TIMESTAMPDIFF(MINUTE, MST.NODE_START_TIME, MST.NODE_END_TIME ) AS BOTTOM_NODE_TIME_MIN    
 , MST.NODE_ERROR_CODE
 , MST.NODE_ERROR_MESSAGE
 , MST.NODE_TYPE
 , MST.NODE_TRANSITION
 , D.ID                 AS LV01_ID
 , D.APP_NAME           AS LV01_APP_NAME     
 , D.CREATED_TIME       AS LV01_CREATED_TIME
 , D.START_TIME         AS LV01_START_TIME  
 , D.END_TIME           AS LV01_END_TIME    
 , D.STATUS             AS LV01_STATUS      
 , D.PARENT_ID         AS LV01_PARENT_ID
 , E.ID                 AS LV02_ID
 , E.APP_NAME           AS LV02_APP_NAME     
 , E.CREATED_TIME       AS LV02_CREATED_TIME
 , E.START_TIME         AS LV02_START_TIME  
 , E.END_TIME           AS LV02_END_TIME    
 , E.STATUS             AS LV02_STATUS      
 , E.PARENT_ID         AS LV02_PARENT_ID
 , F.ID                 AS LV03_ID
 , F.APP_NAME           AS LV03_APP_NAME     
 , F.CREATED_TIME       AS LV03_CREATED_TIME
 , F.START_TIME         AS LV03_START_TIME  
 , F.END_TIME           AS LV03_END_TIME    
 , F.STATUS             AS LV03_STATUS      
 , F.PARENT_ID         AS LV03_PARENT_ID  
 , G.ID                 AS LV04_ID
 , G.APP_NAME           AS LV04_APP_NAME     
 , G.CREATED_TIME       AS LV04_CREATED_TIME
 , G.START_TIME         AS LV04_START_TIME  
 , G.END_TIME           AS LV04_END_TIME    
 , G.STATUS             AS LV04_STATUS      
 , G.PARENT_ID         AS LV04_PARENT_ID
 , CASE WHEN MST.BOTTOM_PARENT_ID IS NULL OR MST.BOTTOM_PARENT_ID LIKE '%oozie-oozi-C@%' THEN MST.BOTTOM_ID
WHEN D.PARENT_ID IS NULL OR D.PARENT_ID LIKE '%oozie-oozi-C@%' THEN D.ID
WHEN E.PARENT_ID IS NULL OR E.PARENT_ID LIKE '%oozie-oozi-C@%' THEN E.ID   
WHEN F.PARENT_ID IS NULL OR F.PARENT_ID LIKE '%oozie-oozi-C@%' THEN F.ID   
WHEN G.PARENT_ID IS NULL OR G.PARENT_ID LIKE '%oozie-oozi-C@%' THEN G.ID   
ELSE MST.BOTTOM_ID
END AS TOP_LVL_ID
 , CASE WHEN MST.BOTTOM_PARENT_ID IS NULL OR MST.BOTTOM_PARENT_ID LIKE '%oozie-oozi-C@%' THEN MST.BOTTOM_APP_NAME
WHEN D.PARENT_ID IS NULL OR D.PARENT_ID LIKE '%oozie-oozi-C@%' THEN D.APP_NAME
WHEN E.PARENT_ID IS NULL OR E.PARENT_ID LIKE '%oozie-oozi-C@%' THEN E.APP_NAME   
WHEN F.PARENT_ID IS NULL OR F.PARENT_ID LIKE '%oozie-oozi-C@%' THEN F.APP_NAME   
WHEN G.PARENT_ID IS NULL OR G.PARENT_ID LIKE '%oozie-oozi-C@%' THEN G.APP_NAME   
ELSE MST.BOTTOM_APP_NAME
END AS TOP_LVL_APP_NAME
 , CASE WHEN MST.BOTTOM_PARENT_ID IS NULL OR MST.BOTTOM_PARENT_ID LIKE '%oozie-oozi-C@%' THEN MST.BOTTOM_START_TIME
WHEN D.PARENT_ID IS NULL OR D.PARENT_ID LIKE '%oozie-oozi-C@%' THEN D.START_TIME
WHEN E.PARENT_ID IS NULL OR E.PARENT_ID LIKE '%oozie-oozi-C@%' THEN E.START_TIME   
WHEN F.PARENT_ID IS NULL OR F.PARENT_ID LIKE '%oozie-oozi-C@%' THEN F.START_TIME   
WHEN G.PARENT_ID IS NULL OR G.PARENT_ID LIKE '%oozie-oozi-C@%' THEN G.START_TIME   
ELSE MST.BOTTOM_START_TIME
END AS TOP_LVL_START_TIME
 , CASE WHEN MST.BOTTOM_PARENT_ID IS NULL OR MST.BOTTOM_PARENT_ID LIKE '%oozie-oozi-C@%' THEN MST.BOTTOM_END_TIME
WHEN D.PARENT_ID IS NULL OR D.PARENT_ID LIKE '%oozie-oozi-C@%' THEN D.END_TIME
WHEN E.PARENT_ID IS NULL OR E.PARENT_ID LIKE '%oozie-oozi-C@%' THEN E.END_TIME   
WHEN F.PARENT_ID IS NULL OR F.PARENT_ID LIKE '%oozie-oozi-C@%' THEN F.END_TIME   
WHEN G.PARENT_ID IS NULL OR G.PARENT_ID LIKE '%oozie-oozi-C@%' THEN G.END_TIME   
ELSE MST.BOTTOM_END_TIME
END AS TOP_LVL_END_TIME
 , CASE WHEN  ( MST.BOTTOM_PARENT_ID LIKE '%oozie-oozi-C@%' 
 OR D.PARENT_ID LIKE '%oozie-oozi-C@%'
 OR E.PARENT_ID LIKE '%oozie-oozi-C@%'
 OR F.PARENT_ID LIKE '%oozie-oozi-C@%'
 OR G.PARENT_ID LIKE '%oozie-oozi-C@%'
   )
THEN 'Y'
ELSE 'N'
END AS CDNT_CONN_YN /* 코디네이터연결여부 */
  FROM (
/* 가장 하위 WF 출력 - 하위 WF 가 없는 경우 추출 */
/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                  AS BOTTOM_ID /* PK */
 , A.APP_NAME            AS BOTTOM_APP_NAME /* WF명 */
 , A.APP_PATH            AS BOTTOM_APP_PATH /* 경로 */
 , A.CREATED_TIME        AS BOTTOM_CREATED_TIME /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , A.END_TIME            AS BOTTOM_END_TIME /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , A.LAST_MODIFIED_TIME  AS BOTTOM_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , A.PARENT_ID           AS BOTTOM_PARENT_ID /* 상위 WF ID */  
 , A.START_TIME          AS BOTTOM_START_TIME /* 최종 시작 일시 */ 
 , A.STATUS              AS BOTTOM_STATUS /* WF 상태 - 비정확. 사용하지 말것 */ 
 , B.ID                  AS NODE_ID      /* PK */
 , B.CONSOLE_URL         AS NODE_CONSOLE_URL /* Sqoop 일경우 url정보 */
 , B.CREATED_TIME        AS NODE_CREATED_TIME /* 시작일시 */
 , B.END_TIME            AS NODE_END_TIME /* 종료일시 */
 , B.ERROR_CODE          AS NODE_ERROR_CODE /* NULL 이 아닌경우 에러. shell script 일 경우 쉘 exit code */
 , B.ERROR_MESSAGE       AS NODE_ERROR_MESSAGE /* 에러메세지. */
 , B.EXECUTION_PATH      AS NODE_EXECUTION_PATH /* 실행경로 */
 , B.EXTERNAL_ID         AS NODE_EXTERNAL_ID /* WF_ID */
 , B.EXTERNAL_STATUS     AS NODE_EXTERNAL_STATUS /* 출력 상태 코드 */
 , B.LAST_CHECK_TIME     AS NODE_LAST_CHECK_TIME /* END_TIME과 동일하다 보면됨 */
 , B.NAME                AS NODE_NAME /* subworkflow명 예) subworkflow-db96  */
 , B.SIGNAL_VALUE        AS NODE_SIGNAL_VALUE /* 상태 */
 , B.SLA_XML             AS NODE_SLA_XML /* SLA 정보 */
 , B.START_TIME          AS NODE_START_TIME /* 실제 실행 시각 */
 , B.STATUS              AS NODE_STATUS /* 상태 */
 , B.TRANSITION          AS NODE_TRANSITION /* NODE ID */
 , B.TYPE                AS NODE_TYPE /* NODE TYPE */
 , B.USER_RETRY_COUNT    AS NODE_USER_RETRY_COUNT /* 사용자지정 재시작 횟수 */
 , B.USER_RETRY_INTERVAL AS NODE_USER_RETRY_INTERVAL /* 사용자지정 재시작 텀 */
 , B.USER_RETRY_MAX      AS NODE_USER_RETRY_MAX /* 사용자지정 재시작 최대 횟수 */
 , B.WF_ID               AS NODE_WF_ID /* WF ID */  
  FROM oz.WF_JOBS A
 INNER JOIN oz.WF_ACTIONS B
 ON A.ID = B.WF_ID     
  LEFT OUTER JOIN oz.WF_JOBS C
   ON A.ID = C.PARENT_ID
 WHERE C.ID IS NULL  /* 하위 WF 미존재하는 WF 추출 조건 */
   AND A.CREATED_TIME >= ADDDATE( NOW(), INTERVAL -110 DAY) 
   ) MST
 LEFT OUTER JOIN oz.WF_JOBS D
 ON MST.BOTTOM_PARENT_ID = D.ID
 LEFT OUTER JOIN oz.WF_JOBS E
 ON D.PARENT_ID = E.ID   
 LEFT OUTER JOIN oz.WF_JOBS F -- 여기까진 존재
 ON E.PARENT_ID = F.ID
 LEFT OUTER JOIN oz.WF_JOBS G
 ON F.PARENT_ID = G.ID    
) H
 WHERE H.CDNT_CONN_YN = 'Y'  /* 코디네이터 연결건만 출력조건. 단독 구동건은 부모 ID 가 붙지 않기 때문에 */
   AND H.BOTTOM_APP_NAME = 'wf_dd_mart_ord_dtl'
 ORDER BY H.TOP_LVL_START_TIME DESC
        , H.BOTTOM_NODE_TIME_MIN DESC 
, H.TOP_LVL_APP_NAME
; 




########################
#                      #
#  OOZIE 작업 에러로그 조회  #
#                      #
########################
/* BOTTOM TO TOP 구조 */
/* PK: WF_ACTIONS.ID */
/* 최종 - 최하위 WF 를 모집단으로 하여 작성. PK: NODE ID단위(= 최하위 WF의 ID단위) - 집계정보가 필요하면 GROUP BY 로 사용할것 */
/* 데이터 레벨이 NODE 단위 이기 때문에, WF 기준으로 보려면 BOTTOM_ID 로 GROUP BY 하여 조회할것. */
/* 110일 이전 일자 로그는 자동 삭제됨. */
SELECT MST.BOTTOM_ID
 , MST.BOTTOM_APP_NAME
 , MST.BOTTOM_CREATED_TIME
 , MST.BOTTOM_START_TIME
 , MST.BOTTOM_END_TIME
 , TIMESTAMPDIFF(SECOND,MST.BOTTOM_START_TIME, MST.BOTTOM_END_TIME ) AS BOTTOM_WF_TIME  
 , MST.BOTTOM_STATUS
 , MST.BOTTOM_PARENT_ID  
 , MST.NODE_ID
 , MST.NODE_CREATED_TIME
 , MST.NODE_START_TIME
 , MST.NODE_END_TIME
 , TIMESTAMPDIFF(SECOND, MST.NODE_START_TIME, MST.NODE_END_TIME ) AS BOTTOM_NODE_TIME  
 , MST.NODE_ERROR_CODE
 , MST.NODE_ERROR_MESSAGE
 , MST.NODE_TYPE
 , MST.NODE_TRANSITION
 , D.ID                 AS LV01_ID
 , D.APP_NAME           AS LV01_APP_NAME     
 , D.CREATED_TIME       AS LV01_CREATED_TIME
 , D.START_TIME         AS LV01_START_TIME  
 , D.END_TIME           AS LV01_END_TIME    
 , D.STATUS             AS LV01_STATUS      
 , D.PARENT_ID         AS LV01_PARENT_ID
 , E.ID                 AS LV02_ID
 , E.APP_NAME           AS LV02_APP_NAME     
 , E.CREATED_TIME       AS LV02_CREATED_TIME
 , E.START_TIME         AS LV02_START_TIME  
 , E.END_TIME           AS LV02_END_TIME    
 , E.STATUS             AS LV02_STATUS      
 , E.PARENT_ID         AS LV02_PARENT_ID
 , F.ID                 AS LV03_ID
 , F.APP_NAME           AS LV03_APP_NAME     
 , F.CREATED_TIME       AS LV03_CREATED_TIME
 , F.START_TIME         AS LV03_START_TIME  
 , F.END_TIME           AS LV03_END_TIME    
 , F.STATUS             AS LV03_STATUS      
 , F.PARENT_ID         AS LV03_PARENT_ID  
 , G.ID                 AS LV04_ID
 , G.APP_NAME           AS LV04_APP_NAME     
 , G.CREATED_TIME       AS LV04_CREATED_TIME
 , G.START_TIME         AS LV04_START_TIME  
 , G.END_TIME           AS LV04_END_TIME    
 , G.STATUS             AS LV04_STATUS      
 , G.PARENT_ID         AS LV04_PARENT_ID
 , CASE WHEN MST.BOTTOM_PARENT_ID IS NULL OR MST.BOTTOM_PARENT_ID LIKE '%oozie-oozi-C@%' THEN MST.BOTTOM_ID
        WHEN D.PARENT_ID IS NULL OR D.PARENT_ID LIKE '%oozie-oozi-C@%' THEN D.ID
            WHEN E.PARENT_ID IS NULL OR E.PARENT_ID LIKE '%oozie-oozi-C@%' THEN E.ID   
            WHEN F.PARENT_ID IS NULL OR F.PARENT_ID LIKE '%oozie-oozi-C@%' THEN F.ID   
            WHEN G.PARENT_ID IS NULL OR G.PARENT_ID LIKE '%oozie-oozi-C@%' THEN G.ID   
ELSE MST.BOTTOM_ID
END AS TOP_LVL_ID
 , CASE WHEN MST.BOTTOM_PARENT_ID IS NULL OR MST.BOTTOM_PARENT_ID LIKE '%oozie-oozi-C@%' THEN MST.BOTTOM_APP_NAME
        WHEN D.PARENT_ID IS NULL OR D.PARENT_ID LIKE '%oozie-oozi-C@%' THEN D.APP_NAME
            WHEN E.PARENT_ID IS NULL OR E.PARENT_ID LIKE '%oozie-oozi-C@%' THEN E.APP_NAME   
            WHEN F.PARENT_ID IS NULL OR F.PARENT_ID LIKE '%oozie-oozi-C@%' THEN F.APP_NAME   
            WHEN G.PARENT_ID IS NULL OR G.PARENT_ID LIKE '%oozie-oozi-C@%' THEN G.APP_NAME   
ELSE MST.BOTTOM_APP_NAME
END AS TOP_LVL_APP_NAME
 , CASE WHEN  ( MST.BOTTOM_PARENT_ID LIKE '%oozie-oozi-C@%' 
             OR D.PARENT_ID LIKE '%oozie-oozi-C@%'
 OR E.PARENT_ID LIKE '%oozie-oozi-C@%'
 OR F.PARENT_ID LIKE '%oozie-oozi-C@%'
 OR G.PARENT_ID LIKE '%oozie-oozi-C@%'
   )
    THEN 'Y'
            ELSE 'N'
END AS CDNT_CONN_YN /* 코디네이터연결여부 */
  FROM (
/* 가장 하위 WF 출력 - 하위 WF 가 없는 경우 추출 */
/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                  AS BOTTOM_ID /* PK */
 , A.APP_NAME            AS BOTTOM_APP_NAME /* WF명 */
 , A.APP_PATH            AS BOTTOM_APP_PATH /* 경로 */
 , A.CREATED_TIME        AS BOTTOM_CREATED_TIME /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , A.END_TIME            AS BOTTOM_END_TIME /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , A.LAST_MODIFIED_TIME  AS BOTTOM_LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , A.PARENT_ID           AS BOTTOM_PARENT_ID /* 상위 WF ID */  
 , A.START_TIME          AS BOTTOM_START_TIME /* 최종 시작 일시 */ 
 , A.STATUS              AS BOTTOM_STATUS /* WF 상태 - 비정확. 사용하지 말것 */ 
 , B.ID                  AS NODE_ID      /* PK */
 , B.CONSOLE_URL         AS NODE_CONSOLE_URL /* Sqoop 일경우 url정보 */
 , B.CREATED_TIME        AS NODE_CREATED_TIME /* 시작일시 */
 , B.END_TIME            AS NODE_END_TIME /* 종료일시 */
 , B.ERROR_CODE          AS NODE_ERROR_CODE /* NULL 이 아닌경우 에러. shell script 일 경우 쉘 exit code */
 , B.ERROR_MESSAGE       AS NODE_ERROR_MESSAGE /* 에러메세지. */
 , B.EXECUTION_PATH      AS NODE_EXECUTION_PATH /* 실행경로 */
 , B.EXTERNAL_ID         AS NODE_EXTERNAL_ID /* WF_ID */
 , B.EXTERNAL_STATUS     AS NODE_EXTERNAL_STATUS /* 출력 상태 코드 */
 , B.LAST_CHECK_TIME     AS NODE_LAST_CHECK_TIME /* END_TIME과 동일하다 보면됨 */
 , B.NAME                AS NODE_NAME /* subworkflow명 예) subworkflow-db96  */
 , B.SIGNAL_VALUE        AS NODE_SIGNAL_VALUE /* 상태 */
 , B.SLA_XML             AS NODE_SLA_XML /* SLA 정보 */
 , B.START_TIME          AS NODE_START_TIME /* 실제 실행 시각 */
 , B.STATUS              AS NODE_STATUS /* 상태 */
 , B.TRANSITION          AS NODE_TRANSITION /* NODE ID */
 , B.TYPE                AS NODE_TYPE /* NODE TYPE */
 , B.USER_RETRY_COUNT    AS NODE_USER_RETRY_COUNT /* 사용자지정 재시작 횟수 */
 , B.USER_RETRY_INTERVAL AS NODE_USER_RETRY_INTERVAL /* 사용자지정 재시작 텀 */
 , B.USER_RETRY_MAX      AS NODE_USER_RETRY_MAX /* 사용자지정 재시작 최대 횟수 */
 , B.WF_ID               AS NODE_WF_ID /* WF ID */  
  FROM oz.WF_JOBS A
 INNER JOIN oz.WF_ACTIONS B
 ON A.ID = B.WF_ID     
  LEFT OUTER JOIN oz.WF_JOBS C
   ON A.ID = C.PARENT_ID
 WHERE C.ID IS NULL  /* 하위 WF 미존재하는 WF 추출 조건 */
   AND A.CREATED_TIME  >= ADDDATE( NOW(), INTERVAL -110 DAY)   
   ) MST
 LEFT OUTER JOIN oz.WF_JOBS D
         ON MST.BOTTOM_PARENT_ID = D.ID
 LEFT OUTER JOIN oz.WF_JOBS E
         ON D.PARENT_ID = E.ID   
 LEFT OUTER JOIN oz.WF_JOBS F -- 여기까진 존재
         ON E.PARENT_ID = F.ID
 LEFT OUTER JOIN oz.WF_JOBS G
         ON F.PARENT_ID = G.ID    
 WHERE MST.NODE_ERROR_CODE IS NOT NULL /* ERROR 발생 WF 조회조건 */  
 ORDER BY MST.BOTTOM_CREATED_TIME DESC 
        , MST.BOTTOM_APP_NAME
; 




/* SAP 에러는 조회되지 않는다. 하위단에서 발생한 에러의 경우 조회가 불가능 하기 때문 */
SELECT MST.ID
     , MST.APP_NAME
 , MST.CREATED_TIME AS CDNT_CREATED_TIME
 , MST.START_TIME
 , MST.END_TIME
 , MST.STATUS
 , MST.LV01_ID
     , C.APP_NAME
 , MST.LV01_CREATED_TIME
 , MST.LV01_START_TIME
 , MST.LV01_END_TIME
 , MST.LV01_ERROR_CODE
 , MST.LV01_ERROR_MESSAGE
 , MST.LV01_TYPE
 , MST.LV01_TRANSITION
  FROM (
/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                 /* PK */
 , A.APP_NAME           /* WF명 */
 , A.APP_PATH           /* 경로 */
 , A.CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , A.END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , A.LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , A.PARENT_ID          /* 상위 WF ID */  
 , A.START_TIME         /* 최종 시작 일시 */ 
 , A.STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , B.ID                  AS LV01_ID      /* PK */
 , B.CONSOLE_URL         AS LV01_CONSOLE_URL /* Sqoop 일경우 url정보 */
 , B.CREATED_TIME        AS LV01_CREATED_TIME /* 시작일시 */
 , B.END_TIME            AS LV01_END_TIME /* 종료일시 */
 , B.ERROR_CODE          AS LV01_ERROR_CODE /* NULL 이 아닌경우 에러. shell script 일 경우 쉘 exit code */
 , B.ERROR_MESSAGE       AS LV01_ERROR_MESSAGE /* 에러메세지. */
 , B.EXECUTION_PATH      AS LV01_EXECUTION_PATH /* 실행경로 */
 , B.EXTERNAL_ID         AS LV01_EXTERNAL_ID /* WF_ID */
 , B.EXTERNAL_STATUS     AS LV01_EXTERNAL_STATUS /* 출력 상태 코드 */
 , B.LAST_CHECK_TIME     AS LV01_LAST_CHECK_TIME /* END_TIME과 동일하다 보면됨 */
 , B.NAME                AS LV01_NAME /* subworkflow명 예) subworkflow-db96  */
 , B.SIGNAL_VALUE        AS LV01_SIGNAL_VALUE /* 상태 */
 , B.SLA_XML             AS LV01_SLA_XML /* SLA 정보 */
 , B.START_TIME          AS LV01_START_TIME /* 실제 실행 시각 */
 , B.STATUS              AS LV01_STATUS /* 상태 */
 , B.TRANSITION          AS LV01_TRANSITION /* NODE ID */
 , B.TYPE                AS LV01_TYPE /* NODE TYPE */
 , B.USER_RETRY_COUNT    AS LV01_USER_RETRY_COUNT /* 사용자지정 재시작 횟수 */
 , B.USER_RETRY_INTERVAL AS LV01_USER_RETRY_INTERVAL /* 사용자지정 재시작 텀 */
 , B.USER_RETRY_MAX      AS LV01_USER_RETRY_MAX /* 사용자지정 재시작 최대 횟수 */
 , B.WF_ID               AS LV01_WF_ID /* WF ID */  
  FROM oz.WF_JOBS A
 INNER JOIN oz.WF_ACTIONS B
 ON A.ID = B.WF_ID  
 WHERE 1=1
   AND A.PARENT_ID LIKE '%oozie-oozi-C@%'  /* 최상위 JOB  */
       ) MST
 LEFT OUTER JOIN oz.WF_JOBS C
         ON MST.ID = C.PARENT_ID
 WHERE MST.LV01_ERROR_CODE IS NOT NULL /* ERROR 발생 WF 조회조건 */  
   AND MST.APP_NAME = 'wf_dd_job_all'   /* 조회 WF 명 */
 ORDER BY MST.CREATED_TIME DESC 
        , MST.APP_NAME  
;


;

/* SAP 에러는 조회되지 않는다. 하위단에서 발생한 에러의 경우 조회가 불가능 하기 때문 */
SELECT MST.ID
     , MST.APP_NAME
 , MST.CREATED_TIME AS CDNT_CREATED_TIME
 , MST.START_TIME
 , MST.END_TIME
 , MST.STATUS
 , MST.LV01_ID
     , C.APP_NAME
 , MST.LV01_CREATED_TIME
 , MST.LV01_START_TIME
 , MST.LV01_END_TIME
 , MST.LV01_ERROR_CODE
 , MST.LV01_ERROR_MESSAGE
 , MST.LV01_TYPE
 , MST.LV01_TRANSITION
  FROM (
/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                 /* PK */
 , A.APP_NAME           /* WF명 */
 , A.APP_PATH           /* 경로 */
 , A.CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , A.END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , A.LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , A.PARENT_ID          /* 상위 WF ID */  
 , A.START_TIME         /* 최종 시작 일시 */ 
 , A.STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , B.ID                  AS LV01_ID      /* PK */
 , B.CONSOLE_URL         AS LV01_CONSOLE_URL /* Sqoop 일경우 url정보 */
 , B.CREATED_TIME        AS LV01_CREATED_TIME /* 시작일시 */
 , B.END_TIME            AS LV01_END_TIME /* 종료일시 */
 , B.ERROR_CODE          AS LV01_ERROR_CODE /* NULL 이 아닌경우 에러. shell script 일 경우 쉘 exit code */
 , B.ERROR_MESSAGE       AS LV01_ERROR_MESSAGE /* 에러메세지. */
 , B.EXECUTION_PATH      AS LV01_EXECUTION_PATH /* 실행경로 */
 , B.EXTERNAL_ID         AS LV01_EXTERNAL_ID /* WF_ID */
 , B.EXTERNAL_STATUS     AS LV01_EXTERNAL_STATUS /* 출력 상태 코드 */
 , B.LAST_CHECK_TIME     AS LV01_LAST_CHECK_TIME /* END_TIME과 동일하다 보면됨 */
 , B.NAME                AS LV01_NAME /* subworkflow명 예) subworkflow-db96  */
 , B.SIGNAL_VALUE        AS LV01_SIGNAL_VALUE /* 상태 */
 , B.SLA_XML             AS LV01_SLA_XML /* SLA 정보 */
 , B.START_TIME          AS LV01_START_TIME /* 실제 실행 시각 */
 , B.STATUS              AS LV01_STATUS /* 상태 */
 , B.TRANSITION          AS LV01_TRANSITION /* NODE ID */
 , B.TYPE                AS LV01_TYPE /* NODE TYPE */
 , B.USER_RETRY_COUNT    AS LV01_USER_RETRY_COUNT /* 사용자지정 재시작 횟수 */
 , B.USER_RETRY_INTERVAL AS LV01_USER_RETRY_INTERVAL /* 사용자지정 재시작 텀 */
 , B.USER_RETRY_MAX      AS LV01_USER_RETRY_MAX /* 사용자지정 재시작 최대 횟수 */
 , B.WF_ID               AS LV01_WF_ID /* WF ID */  
  FROM oz.WF_JOBS A
 INNER JOIN oz.WF_ACTIONS B
 ON A.ID = B.WF_ID  
 WHERE 1=1
   AND A.PARENT_ID LIKE '%oozie-oozi-C@%'  /* 최상위 JOB  */
       ) MST
 INNER JOIN oz.WF_JOBS C
         ON MST.ID = C.PARENT_ID
 WHERE MST.LV01_ERROR_CODE IS NOT NULL /* ERROR 발생 WF 조회조건 */  
   AND MST.APP_NAME = 'wf_dd_job_all'   /* 조회 WF 명 */
 ORDER BY MST.CREATED_TIME DESC 
        , MST.APP_NAME  
;


;

SELECT MST.*
     , C.APP_NAME
  FROM (
/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                 /* PK */
 , A.APP_NAME           /* WF명 */
 , A.APP_PATH           /* 경로 */
 , A.CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
 , A.END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
 , A.LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
 , A.PARENT_ID          /* 상위 WF ID */  
 , A.START_TIME         /* 최종 시작 일시 */ 
 , A.STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
 , B.ID                  AS LV01_ID      /* PK */
 , B.CONSOLE_URL         AS LV01_CONSOLE_URL /* Sqoop 일경우 url정보 */
 , B.CREATED_TIME        AS LV01_CREATED_TIME /* 시작일시 */
 , B.END_TIME            AS LV01_END_TIME /* 종료일시 */
 , B.ERROR_CODE          AS LV01_ERROR_CODE /* NULL 이 아닌경우 에러. shell script 일 경우 쉘 exit code */
 , B.ERROR_MESSAGE       AS LV01_ERROR_MESSAGE /* 에러메세지. */
 , B.EXECUTION_PATH      AS LV01_EXECUTION_PATH /* 실행경로 */
 , B.EXTERNAL_ID         AS LV01_EXTERNAL_ID /* WF_ID */
 , B.EXTERNAL_STATUS     AS LV01_EXTERNAL_STATUS /* 출력 상태 코드 */
 , B.LAST_CHECK_TIME     AS LV01_LAST_CHECK_TIME /* END_TIME과 동일하다 보면됨 */
 , B.NAME                AS LV01_NAME /* subworkflow명 예) subworkflow-db96  */
 , B.SIGNAL_VALUE        AS LV01_SIGNAL_VALUE /* 상태 */
 , B.SLA_XML             AS LV01_SLA_XML /* SLA 정보 */
 , B.START_TIME          AS LV01_START_TIME /* 실제 실행 시각 */
 , B.STATUS              AS LV01_STATUS /* 상태 */
 , B.TRANSITION          AS LV01_TRANSITION /* NODE ID */
 , B.TYPE                AS LV01_TYPE /* NODE TYPE */
 , B.USER_RETRY_COUNT    AS LV01_USER_RETRY_COUNT /* 사용자지정 재시작 횟수 */
 , B.USER_RETRY_INTERVAL AS LV01_USER_RETRY_INTERVAL /* 사용자지정 재시작 텀 */
 , B.USER_RETRY_MAX      AS LV01_USER_RETRY_MAX /* 사용자지정 재시작 최대 횟수 */
 , B.WF_ID               AS LV01_WF_ID /* WF ID */  
  FROM oz.WF_JOBS A
 INNER JOIN oz.WF_ACTIONS B
 ON A.ID = B.WF_ID  
 WHERE 1=1
   AND A.PARENT_ID LIKE '%oozie-oozi-C@%'  /* 최상위 JOB  */
   AND A.APP_NAME = 'wf_dd_job_all'        /* 조회 WF 명 */
       ) MST
 INNER JOIN oz.WF_JOBS C
         ON MST.ID = C.PARENT_ID 
;


/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID                 /* PK */
     , A.APP_NAME           /* WF명 */
     , A.APP_PATH           /* 경로 */
     , A.CREATED_TIME       /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
     , A.END_TIME           /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
     , A.LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
     , A.PARENT_ID          /* 상위 WF ID */  
     , A.START_TIME         /* 최종 시작 일시 */ 
     , A.STATUS             /* WF 상태 - 비정확. 사용하지 말것 */ 
     
 , B.ID            AS LV_01_WF_ID      /* PK */
     , B.CONSOLE_URL         /* Sqoop 일경우 url정보 */
     , B.CREATED_TIME        /* 시작일시 */
     , B.END_TIME            /* 종료일시 */
     , B.ERROR_CODE          /* NULL 이 아닌경우 에러. shell script 일 경우 쉘 exit code */
     , B.ERROR_MESSAGE       /* 에러메세지. */
     , B.EXECUTION_PATH      /* 실행경로 */
     , B.EXTERNAL_ID         /* WF_ID */
     , B.EXTERNAL_STATUS     /* 출력 상태 코드 */
     , B.LAST_CHECK_TIME     /* END_TIME과 동일하다 보면됨 */
     , B.NAME                /* subworkflow명 예) subworkflow-db96  */
     , B.SIGNAL_VALUE        /* 상태 */
     , B.SLA_XML             /* SLA 정보 */
     , B.START_TIME          /* 실제 실행 시각 */
     , B.STATUS              /* 상태 */
     , B.TRANSITION          /* NODE ID */
     , B.TYPE                /* NODE TYPE */
     , B.USER_RETRY_COUNT    /* 사용자지정 재시작 횟수 */
     , B.USER_RETRY_INTERVAL /* 사용자지정 재시작 텀 */
     , B.USER_RETRY_MAX      /* 사용자지정 재시작 최대 횟수 */
     , B.WF_ID               /* WF ID */  
  FROM oz.WF_JOBS A
 INNER JOIN oz.WF_ACTIONS B
 ON A.ID = B.WF_ID  
 WHERE 1=1
   AND A.PARENT_ID LIKE '%oozie-oozi-C@%'  /* 최상위 JOB  */
   AND A.APP_NAME = 'wf_dd_job_all'        /* 조회 WF 명 */
;



SELECT B.ID                  /* PK */
     , B.CONF                /* ? BLOB */
     , B.CONSOLE_URL         /* Sqoop 일경우 url정보 */
     , B.CREATED_TIME        /* 시작일시 */
     , B.CRED                /* ? */
     , B.DATA                /* ? BLOB */
     , B.END_TIME            /* 종료일시 */
     , B.ERROR_CODE          /* NULL 이 아닌경우 에러. shell script 일 경우 쉘 exit code */
     , B.ERROR_MESSAGE       /* 에러메세지. */
     , B.EXECUTION_PATH      /* 실행경로 */
     , B.EXTERNAL_CHILD_IDS  /* ? BLOB  */
     , B.EXTERNAL_ID         /* WF_ID */
     , B.EXTERNAL_STATUS     /* 출력 상태 코드 */
     , B.LAST_CHECK_TIME     /* END_TIME과 동일하다 보면됨 */
     , B.LOG_TOKEN           /* ? */
     , B.NAME                /* subworkflow명 예) subworkflow-db96  */
     , B.PENDING             /* ? */
     , B.PENDING_AGE         /* ? */
     , B.RETRIES             /* ? */
     , B.SIGNAL_VALUE        /* 상태 */
     , B.SLA_XML             /* SLA 정보 */
     , B.START_TIME          /* 실제 실행 시각 */
     , B.STATS               /* ? */
     , B.STATUS              /* 상태 */
     , B.TRACKER_URI         /* ? */
     , B.TRANSITION          /* NODE ID */
     , B.TYPE                /* NODE TYPE */
     , B.USER_RETRY_COUNT    /* 사용자지정 재시작 횟수 */
     , B.USER_RETRY_INTERVAL /* 사용자지정 재시작 텀 */
     , B.USER_RETRY_MAX      /* 사용자지정 재시작 최대 횟수 */
     , B.WF_ID               /* WF ID */
  FROM oz.WF_ACTIONS
;


/* 대상은 코디네이터에 연결된 JOB 기준 */
SELECT A.ID            /* PK */
     , A.APP_NAME      /* WF명 */
     , A.APP_PATH      /* 경로 */
     , A.CONF          /* 사용불가 */
     , A.CREATED_TIME /* 코디네이터에 의해 시작(생성) 된 시각 ~ 배치 구동일시 */
     , A.END_TIME      /* 해당WF의 최종 종료일시 - 재구동시,재구동종료일시 */ 
     , A.EXTERNAL_ID   /* ? BLOB */
     , A.GROUP_NAME    /* ? BLOB */
     , A.LAST_MODIFIED_TIME /* 최종수정일시 END_TIME 과 동일 함 */
     , A.LOG_TOKEN          /* ? */ 
     , A.PARENT_ID          /* 상위 WF ID */  
     , A.PROTO_ACTION_CONF  /* ? BLOB */ 
     , A.RUN                /* ? BLOB */  
     , A.SLA_XML            /* ? BLOB */ 
     , A.START_TIME         /* 최종 시작 일시 */ 
     , A.STATUS             /* WF 상태 */ 
     , A.USER_NAME          /* 실행 유저 */ 
     , A.WF_INSTANCE        /* ? BLOB */ 
  FROM oz.WF_JOBS A
 WHERE 1=1
   AND A.PARENT_ID LIKE '%oozie-oozi-C@%' /* 최상위 JOB  */

;

/* 주의 ! wf_actions 에서 에러가 기록되어도, wf_jobs 에서는 에러가 기록이 안된다. */
예) 
 SELECT A.*, B.* 
   FROM oz.WF_JOBS A
  INNER JOIN oz.WF_ACTIONS B
  ON A.ID = B.WF_ID
 WHERE 1=1
   AND A.START_TIME >= '2020-01-20'
   AND ( B.ERROR_CODE IS NOT NULL OR  B.STATUS = 'KILLED' OR B.STATUS = 'FAILED'  )   /* 오류로 인한 종료건 */   
   AND A.APP_NAME = 'wf_sap_ccd_batch'
ORDER BY  A.START_TIME  DESC 
;

/* wf 노드 별 작업 시작 종료 시각   WF_ACTIONS.start_time, WF_ACTIONS.last_check_time  */
/* wf 별 작업 시작 종료 시각  created_time, end_time */



/* 하둡 wf 이력 조회 - 현재최대 4DEPTH 존재 */
-- 독립적으로 하위 WF를 실행할 경우 부모ID는 NULL 이 된다. 
-- 즉 부모ID를 기준으로 
-- 재귀함수 미지원 
SELECT A.ID
     , A.APP_NAME
 , B.ID
     , B.APP_NAME
 , C.ID
     , C.APP_NAME
 , D.ID
     , D.APP_NAME  
  FROM ( 
         SELECT ID
              , APP_NAME
              , PARENT_ID   
  FROM oz.WF_JOBS 
 WHERE PARENT_ID IS NULL 
   ) A
  LEFT OUTER JOIN oz.WF_JOBS B
               ON A.ID = B.PARENT_ID
  LEFT OUTER JOIN oz.WF_JOBS C
               ON B.ID = C.PARENT_ID
  LEFT OUTER JOIN oz.WF_JOBS D
               ON C.ID = D.PARENT_ID     
 WHERE 1=1
   AND D.ID IS NOT NULL 
 ORDER BY A.APP_NAME, A.ID
;


/* HUE 워크플로우 */
SELECT ID            AS HUE_ID
     , UUID          AS HUE_UUID
     , NAME          AS HUE_NAME
     , DESCRIPTION   AS HUE_DESCRIPTION
     , LAST_MODIFIED AS HUE_LAST_MODIFIED
     , CASE WHEN DATA LIKE '%JOIN%' THEN 'Y'
            ELSE 'N'
        END AS HUE_WF_PARALLEL_YN
     , DATA AS HUE_DATA    
  FROM hue.desktop_document2
 WHERE 1=1
   AND TYPE = 'oozie-workflow2'
   AND IS_TRASHED = 0
   AND IS_HISTORY = 0
   AND IS_MANAGED  = 0
   AND name NOT LIKE '%임시%'
   AND name NOT LIKE '%test%'
   AND name NOT LIKE '%backup%'   
   AND name NOT LIKE '%bakup%'      
   AND name NOT LIKE '%테스트%'       
 ORDER BY name 
; 
   
/* TOP DOWN 방식은 부모ID가 끊어져 있기 때문에 구현불가  */    
/* 최하위 워크플로우 기준 에러 발생 최상위 WF 조회 */
SELECT MST.START_TIME
     , MST.ID
 , MST.PARENT_ID
 , MST.APP_NAME 
     , A.CREATED_TIME
     , A.END_TIME
     , A.LAST_MODIFIED_TIME
     , A.STATUS
     , B.APP_NAME AS UPPER_1LV_APP_NAME
     , C.APP_NAME AS UPPER_2LV_APP_NAME
     , D.APP_NAME AS UPPER_3LV_APP_NAME
 , CASE WHEN D.APP_NAME IS NOT NULL THEN D.APP_NAME
            WHEN D.APP_NAME IS NULL AND C.APP_NAME IS NOT NULL THEN C.APP_NAME
            WHEN D.APP_NAME IS NULL AND C.APP_NAME IS NULL AND B.APP_NAME IS NOT NULL THEN B.APP_NAME
ELSE A.APP_NAME
END AS SUMMIT_APP_NAME
  FROM (
SELECT C.START_TIME 
     , C.ID 
     , C.PARENT_ID
     , MAX(C.APP_NAME) AS APP_NAME
  FROM (
 SELECT A.*, B.WF_ID 
     FROM oz.WF_JOBS A
  INNER JOIN oz.WF_ACTIONS B
          ON A.ID = B.WF_ID
 WHERE 1=1
   AND A.START_TIME >= '2020-01-20'
   AND ( B.ERROR_CODE IS NOT NULL OR  B.STATUS = 'KILLED' OR B.STATUS = 'FAILED'  )   /* 오류로 인한 종료건 */   
       ) C
 GROUP BY C.START_TIME 
        , C.ID 
        , C.PARENT_ID
   ) MST
  INNER JOIN oz.WF_JOBS A
  ON MST.ID = A.ID
  LEFT OUTER JOIN oz.WF_JOBS B
   ON A.PARENT_ID = B.ID   
  LEFT OUTER JOIN oz.WF_JOBS C
   ON B.PARENT_ID = C.ID
  LEFT OUTER JOIN oz.WF_JOBS D
   ON C.PARENT_ID = D.ID
 WHERE 1=1 
  /* 조회할 워크플로우 존재시 입력 */
  -- AND MST.APP_NAME = 'wf_sap_ccd_batch'       
 ORDER BY MST.START_TIME DESC
        , MST.APP_NAME 
;

/* CCIP 테이블 미적재 이슈 방안 */



;



/* 일반주문 상품ID */
SELECT PRD_ID
    , COUNT(*) 
  FROM ORD_ORD_DTL_D
 WHERE 1=1
   AND INST_DTM >= TO_DATE('20180201','YYYYMMDD')
 GROUP BY PRD_ID
;

/* 후불정기 상품ID */
SELECT PRD_ID
    , COUNT(*) 
  FROM ORD_CTR_PRD_D
 WHERE 1=1
   AND INST_DTM >= TO_DATE('20180201','YYYYMMDD')
 GROUP BY PRD_ID
;
/* 선불정기 상품ID */
SELECT PRD_ID
    , COUNT(*) 
  FROM ORD_RGLR_DLV_CTR_DTL_D
 WHERE 1=1
   AND INST_DTM >= TO_DATE('20180201','YYYYMMDD')
 GROUP BY PRD_ID
; 

공통쿼리 
/* coordinator 구동 이력 */
SELECT A.ID
     , A.APP_NAME
     , A.APP_PATH
     , A.CREATED_TIME 
     , A.END_TIME
     , A.FREQUENCY
     , A.LAST_ACTION
     , A.LAST_MODIFIED_TIME -- 배치구동일자( 종료일시 )
     , A.NEXT_MATD_TIME 
     , A.START_TIME 
     , A.STATUS 
     , A.TIME_ZONE 
     , A.USER_NAME 
     , B.ID AS HST_ID
     , B.ACTION_NUMBER 
     , B.CREATED_TIME 
     , B.EXTERNAL_ID 
     , B.LAST_MODIFIED_TIME 
     , B.NOMINAL_TIME -- 배치구동일자 ( 시작일시 )
     , B.STATUS 
  FROM oz.COORD_JOBS A
  LEFT OUTER JOIN oz.COORD_ACTIONS B
               ON A.ID = B.JOB_ID
 WHERE 1=1
--    AND A.APP_NAME LIKE '%cdnt_dd_anl_update_v2%'
   AND A.APP_NAME LIKE '%cdnt_dd_anl_update_v2%'
;

/* 현재 코디네이터에 올라가 있는지(자동 크론탭 배치구동중) 인지 확인 가능 - STATUS 컬럼  */
SELECT *
  FROM oz.COORD_JOBS A
 WHERE 1=1
--    AND A.APP_NAME LIKE '%cdnt_dd_anl_update_v2%'
   AND A.APP_NAME LIKE '%cdnt_dd_anl_update%'
;

   
   

/* 오라클 DML 이력 보기 - COMMIT여부와 상관없이 DML 발생시 이력이 남음 */
SELECT *
  FROM DBA_TAB_MODIFICATIONS
 WHERE TABLE_NAME = 'WEB_PCODE_FEE_I'
;
/* db link 조회 */
SELECT *
  FROM SALESEP.TSALES_JIGU_SUMMARY@SALESEP
;

/* 최종 수정 이력 조회 */
SELECT TABLE_OWNER 
     , TABLE_NAME
     , INSERTS
     , UPDATES
     , DELETES
     , TIMESTAMP AS LAST_CHANGE
FROM  ALL_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY') 
AND TABLE_OWNER = 'CCIP'
AND TABLE_NAME = 'ORD_MM_SL_PFMC_M'
ORDER BY LAST_CHANGE ASC;
;
/* 수정이력을 통한 미사용 테이블 조회 */
SELECT *
  FROM DBA_TAB_MODIFICATIONS
WHERE 1=1
--  AND TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY') 
AND TABLE_OWNER = 'CCIP'
AND TABLE_NAME NOT LIKE '%BIN%'
AND PARTITION_NAME IS NULL
ORDER BY TIMESTAMP
;


/* 테이블 생성 일자 조회  */
SELECT * 
  FROM USER_OBJECTS 
 WHERE OBJECT_TYPE = 'TABLE'
   AND OBJECT_NAME = 'CMM_JIGU_INFO_H'
;
/* 쿼리 실행 이력 - 최근 3일 */
SELECT LAST_ACTIVE_TIME
     , LAST_LOAD_TIME       /* 쿼리 등록 시간 */
 , FIRST_LOAD_TIME
     , PARSING_SCHEMA_NAME  /* 스키마 이름 */
     , SQL_TEXT             
 , MODULE                /*쿼리 실행한 프로그램 이름 */                           
  FROM V$SQLAREA
 WHERE 1=1
   AND PARSING_SCHEMA_NAME <> 'SYS'
   AND PARSING_SCHEMA_NAME <> 'SYSMAN'
   AND PARSING_SCHEMA_NAME <> 'DBSNMP'
   AND PARSING_SCHEMA_NAME <> 'MDSYS'
   AND PARSING_SCHEMA_NAME <> 'EXFSYS'
   AND SQL_TEXT LIKE '%CMM_JIGU_INFO_H%'
 ORDER BY LAST_ACTIVE_TIME DESC
;  



SELECT *
  FROM V$SESSION 
;

/* 랜덤 1~10 사이값 추출  */  
SELECT LEVEL AS NO 
     , DBMS_RANDOM.VALUE(1,10)
  FROM DUAL
 CONNECT BY LEVEL <= 10
;   
  
/* 컬럼값 분포 조회  */
SELECT PRD_DCLS_ID
     , COUNT(*) AS CNT 
     , ROUND(COUNT(*) / SUM( COUNT(*) ) OVER(),4)*100  AS PER
  FROM PRD_PRD_M
GROUP BY PRD_DCLS_ID
ORDER BY PRD_DCLS_ID
;

/* 트리거 조회 */
SELECT *
  FROM ALL_TRIGGERS
 WHERE TRIGGER_NAME = 'TR_ORD_CTR_PRD_PFMC_D_AIU'
;
 
/* 프로시저, 패키지 조회  */
SELECT * 
  FROM ALL_SOURCE
 WHERE 1=1
   AND OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' )  
   AND TEXT LIKE '%TSALES_JIGU_SUMMARY%'
;
/* 프로시저, 패키지내 사용빈도 조회  */
SELECT NAME
     , COUNT(*) AS CNT 
  FROM ALL_SOURCE
 WHERE 1=1
   AND OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' )  
   AND TEXT LIKE '%ORD_MM_SL_PFMC_M%'
 GROUP BY NAME
 ORDER BY CNT DESC
;

/* 프로시저 전체 내용 조회 */
SELECT *
  FROM ALL_SOURCE
 WHERE 1=1
   AND OWNER IN ('CCIP', '00CRM', 'GIS', 'OEM_DBSV1', 'HUMUSON', 'ORDDATA', 'AMAIL', 'ANL', 'HYFACTORY' ) 
   AND NAME = 'SP_ANL_DRK_PFMC_DD_PRA_SIT'
  
;
/* 공통코드그룹 조회 */
SELECT *
  FROM CMM_CODE_M
 WHERE 1=1
   AND CODE_GRP_ID = ''
   AND CODE_GRP_NM LIKE '%상품대%'
 ORDER BY CODE_GRP_ID
        , CODE_GRP_ID
 
;
/* 공통코드 조회 */
SELECT *
  FROM CMM_CODE_D
 WHERE 1=1
   AND CODE_GRP_ID = ''
   AND CODE_NM LIKE '%%'
;

/* 공통코드 한줄로 조회 */
SELECT CODE
     , LISTAGG(CODE||':'||CODE_NM, ',' ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY CODE) AS ONE_ROW_CODELIST
  FROM CMM_CODE_D
 WHERE 1=1
--   AND CODE_GRP_ID LIKE 'ORD%'
--   AND CODE_NM NOT LIKE '%_OLD'
 GROUP BY CODE
;   

/* 공통코드 코드명 조회 */   
SELECT ROW_NUMBER() OVER(ORDER BY A.CODE) AS RN
     , A.CODE
     , B.CODE_NM
     , A.CNT
     , A.PER
  FROM (
        SELECT /*+PARALLEL(4)*/ RTN_EXN_RSN_CD AS CODE
             , COUNT(*) AS CNT 
             , ROUND(COUNT(*) / SUM( COUNT(*) ) OVER(),4)*100  AS PER
          FROM ORD_ORD_DTL_D 
         GROUP BY RTN_EXN_RSN_CD
      ) A
  LEFT OUTER JOIN (
                    SELECT CODE_GRP_ID
                         , CODE
                         , CODE_NM
                         , CODE_DESC
                      FROM CMM_CODE_D
                     WHERE CODE_GRP_ID = 'ORD042'  
                   ) B
              ON A.CODE = B.CODE 
 ORDER BY RN         
;
 
/* 공통코드 입력 시점 및 코드명 조회 */   
SELECT ROW_NUMBER() OVER(ORDER BY A.CODE) AS RN
     , A.CODE
     , B.CODE_NM
     , A.CNT
     , A.PER
 , A.FIRST_INST_DTM
 , A.LAST_INST_DTM
 , A.FIRST_MDF_DTM
 , A.LAST_MDF_DTM               
  FROM (
        SELECT /*+PARALLEL(4)*/ ORD_PTR_CD AS CODE
             , COUNT(*) AS CNT 
             , ROUND(COUNT(*) / SUM( COUNT(*) ) OVER(),4)*100  AS PER
             , MIN(INST_DTM) AS FIRST_INST_DTM
             , MAX(INST_DTM) AS LAST_INST_DTM
             , MIN(MDF_DTM) AS FIRST_MDF_DTM
             , MAX(MDF_DTM) AS LAST_MDF_DTM             
             
          FROM ORD_ORD_DTL_D 
         GROUP BY ORD_PTR_CD
      ) A
  LEFT OUTER JOIN (
                    SELECT CODE_GRP_ID
                         , CODE
                         , CODE_NM
                         , CODE_DESC
                      FROM CMM_CODE_D
                     WHERE CODE_GRP_ID = 'ORD082'  
                   ) B
              ON A.CODE = B.CODE 
 ORDER BY RN         
;
/* 공통코드 입력 시점 및 코드명 조회 - 자동 생성용 */   
SELECT '' AS TB, '' AS COL_ID, ROW_NUMBER() OVER(ORDER BY A.CODE) AS RN, A.CODE, B.CODE_NM, A.CNT, A.PER, A.FIRST_INST_DTM, A.LAST_INST_DTM, A.FIRST_MDF_DTM, A.LAST_MDF_DTM  FROM ( SELECT /*+PARALLEL(4)*/ CST_RLTN_CD AS CODE, COUNT(*) AS CNT , ROUND(COUNT(*) / SUM( COUNT(*) ) OVER(),4)*100  AS PER, MIN(INST_DTM) AS FIRST_INST_DTM, MAX(INST_DTM) AS LAST_INST_DTM, MIN(MDF_DTM) AS FIRST_MDF_DTM, MAX(MDF_DTM) AS LAST_MDF_DTM FROM ORD_ORD_DTL_D GROUP BY CST_RLTN_CD) A LEFT OUTER JOIN CMM_CODE_D B ON A.CODE = B.CODE  WHERE B.CODE_GRP_ID = 'ORD095' ORDER BY RN
 
 

 
 /* 패키지, 펑션, 프로시저 내용 검색 */
 SELECT *
   FROM USER_SOURCE
  WHERE TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
    AND TEXT LIKE '%WEB_PCODE_SL_PFMC_I%'
;

/* 영업점ID 기준 조직정보 조회 */
SELECT *
  FROM VI_CMM_ORGN_INFO_SORT
 WHERE 1=1
   AND ORGN_ID5 = '2831'
;   
/* 조회시점 재직중인 FM 정보 조회 */
SELECT *
  FROM CMM_PCODE_M#
 WHERE BIZTM_ID = '2831'
   AND JIGU_ID = '02'
   AND USE_YN = 'Y'
;

/* IMPALA MINUS 구현 */
WITH TMP AS (
SELECT 'A' AS ID, '101' AS GUBUN  
UNION ALL
SELECT 'B' AS ID, '101' AS GUBUN  
UNION ALL
SELECT 'C' AS ID, '301' AS GUBUN  
), TMP2 AS (
SELECT 'A' AS ID, '101' AS GUBUN  
UNION ALL
SELECT 'B' AS ID, '301' AS GUBUN  
)
SELECT A.ID
  FROM TMP A
  LEFT OUTER JOIN  TMP2 B
  ON A.ID=B.ID
WHERE B.ID IS NULL  
;  
  

/* 상품대분류 */
SELECT A.PRD_LCLS_ID
     , B.PRD_CLS_NM
     , A.CNT
     , A.PER
  FROM (
          SELECT PRD_LCLS_ID
               , COUNT(*) AS CNT 
               , ROUND(COUNT(*) / SUM( COUNT(*) ) OVER(),4)*100  AS PER
            FROM PRD_PRD_M
           WHERE USE_YN = 'Y'  
          GROUP BY PRD_LCLS_ID
        ) A
  LEFT OUTER JOIN PRD_PRD_CLS_C B
               ON A.PRD_LCLS_ID = B.PRD_LCLS_ID
              AND ( B.PRD_MCLS_ID = ' ' OR B.PRD_MCLS_ID = '0000' ) 
              AND B.USE_YN = 'Y' 
 ORDER BY A.PRD_LCLS_ID         
;

/* 상품중분류 */
SELECT A.PRD_LCLS_ID
     , A.PRD_MCLS_ID
     , B.PRD_CLS_NM
     , A.CNT
     , A.PER
  FROM (
          SELECT PRD_LCLS_ID
               , PRD_MCLS_ID
               , COUNT(*) AS CNT 
               , ROUND(COUNT(*) / SUM( COUNT(*) ) OVER(),4)*100  AS PER
            FROM PRD_PRD_M
           WHERE USE_YN = 'Y'  
             AND PRD_MCLS_ID != ' '
          GROUP BY PRD_LCLS_ID
                 , PRD_MCLS_ID
        ) A
  LEFT OUTER JOIN (
                    SELECT PRD_LCLS_ID
                         , PRD_MCLS_ID
                         , MAX(PRD_CLS_NM) AS PRD_CLS_NM
                      FROM PRD_PRD_CLS_C
                     WHERE ( PRD_SCLS_ID = ' ' OR PRD_SCLS_ID = '000000' )
                       AND USE_YN = 'Y' 
                     GROUP BY PRD_LCLS_ID
                            , PRD_MCLS_ID
                   ) B
               ON  A.PRD_LCLS_ID = B.PRD_LCLS_ID
              AND A.PRD_MCLS_ID = B.PRD_MCLS_ID
 ORDER BY A.PRD_LCLS_ID
        , A.PRD_MCLS_ID      
;

/* 채널 마스터 */
SELECT *
  FROM PRD_CHNL_M
 WHERE 1=1
   AND USE_YN = 'Y'
--   AND CHNL_ID = '10241'
 ORDER BY CHNL_ID   
;

/* 지구정보 */
SELECT *
  FROM CMM_JIGU_INFO_C
 WHERE 1=1
   AND USE_YN = 'Y'
 ORDER BY MDF_DTM DESC
 ;
 
/* 지구정보이력 */ 
SELECT *
  FROM CMM_JIGU_INFO_H  
 WHERE 1=1
   AND USE_YN = 'Y'
 ORDER BY MDF_DTM DESC   
;

/* 매니저 */
SELECT *
  FROM CMM_PCODE_M#  /* PK:PCODE_ID(판매점)*/
 WHERE 1=1
; 
/* 매니저이력 */
SELECT *
  FROM CMM_PCODE_H#  /* PK:PCODE_ID(판매점), PCODE_SEQ(판매점순번) */
 WHERE 1=1
;

/* 지구정보 -건수 */
 -- 17154
 SELECT COUNT(*)
  FROM CMM_JIGU_INFO_C
 WHERE 1=1
   AND USE_YN = 'Y' /* Y 일 경우 정상지구, 결배지구만 존재함.*/
;   

/* 지구정보 - 지역별정보 */
-- 연령정보 부정확: 음수값존재
-- SALESEP.TSALES_JIGU_SUMMARY@SALESEP 테이블이 주 테이블 
-- 지구 가구 정보 등 존재.
SELECT *
  FROM TSALES_JIGU_SUMMARY
;  
  


/* 매니저 */
-- 10419
SELECT BIZTM_ID
        , JIGU_ID
        , COUNT(*) OVER()
        , COUNT(*) 
  FROM CMM_PCODE_M#  /* PK:PCODE_ID(판매점)*/
 WHERE 1=1
   AND USE_YN = 'Y' 
 GROUP BY BIZTM_ID
        , JIGU_ID
;
/* 조직코드 */
SELECT *
  FROM CMM_ORGN_M
 WHERE 1=1
   AND ORGN_LVL = 5
   AND ORGN_ID = '2006'
;

/* 입사일자별 분포 NTILE */
SELECT PCODE_ID
     , STRIN_DATE
     , NTILE(20) OVER(ORDER BY STRIN_DATE DESC ) AS NT
  FROM CCIP.CMM_PCODE_M B1 
;

/* IMPALA NTILE 예 */
SELECT A.NT
     , MAX(A.OUT_DATE)
     , MIN(A.OUT_DATE)
  FROM (
SELECT OUT_DATE, NTILE(10) OVER(ORDER BY OUT_DATE) AS NT
  FROM CCIP.T_MCRM_OUT 
 WHERE OUT_DATE >= NOW()
 ) A
 GROUP BY A.NT
 ORDER BY A.NT



/* 조직정보 테이블 이력 미존재 */

/* 정기주문계약을 체결한 영업점+지구 정보 */
SELECT /*+PARALLEL(4) */ BIZTM_ID
      , JIGU_ID
  FROM ORD_RGLR_ORD_CTR_M
 WHERE 1=1
   AND DEL_YN = 'N'
 GROUP BY BIZTM_ID
      , JIGU_ID
MINUS

SELECT BIZTM_ID
      , JIGU_ID
  FROM CMM_PCODE_M#
 WHERE 1=1
--   AND USE_YN = 'Y'  
;

/* 후불주문계약 */ 
SELECT /*+PARALLEL(4) */
       BIZTM_ID
     , JIGU_ID
  FROM ORD_RGLR_ORD_CTR_M /* 정기주문계약 */
 WHERE DEL_YN = 'N'
  AND BIZTM_ID = '2084'
  AND JIGU_ID  = '9'
;
/* 일반주문상세 */
SELECT /*+PARALLEL(4) */
       BIZTM_ID
     , JIGU_ID
  FROM ORD_ORD_DTL_D /* 일반주문상세 */
 WHERE 1=1  
  AND BIZTM_ID = '2084'
  AND JIGU_ID  = '9'
;

/* 선불주문계약 */ 
SELECT /*+PARALLEL(4) */
       BIZTM_ID
     , JIGU_ID
  FROM ORD_RGLR_DLV_CTR_BSC_M /* 주문_정기_배송_계약_마스터 */
 WHERE 1=1  
  AND BIZTM_ID = '2084'
  AND JIGU_ID  = '9'
;

/* 휴일달력을 사용한 년월정보추출 - 주의 현재시점 년월정보까지만 존재함.*/
SELECT TO_CHAR(HDY_DATE, 'YYYYMM') AS STD_YYMM
    , COUNT(*) AS CNT 
  FROM CMM_HDY  /* 휴일정보 */
 WHERE USE_YN = 'Y'
   AND HDY_TP_CD = '20' /* 20:은행휴일 */
 GROUP BY TO_CHAR(HDY_DATE, 'YYYYMM')
 ORDER BY TO_CHAR(HDY_DATE, 'YYYYMM')
; 

/* 임직원 직급코드별 분포 */
SELECT A.PST_CD
     , MAX(B.CODE_NM) AS CODE_NM
     , COUNT(*) AS CNT 
     , ROUND( COUNT(*) / SUM(COUNT(*)) OVER(),4) * 100 AS PER
  FROM CMM_USR A
  LEFT OUTER JOIN (
SELECT *
  FROM CMM_CODE_D
 WHERE CODE_GRP_ID = 'CMM047'   
                       AND USE_YN = 'Y'  
  ) B
               ON A.PST_CD = B.CODE
 WHERE 1=1
   AND A.USE_YN = 'Y'
 GROUP BY A.PST_CD /* 직급코드_CMM047 */
 ORDER BY A.PST_CD
;
/* 사용자 테이블의 매니저 목록에서 PCODE 직원목록 제외시 */
SELECT EMP_NO
  FROM CMM_USR
 WHERE 1=1
   AND PST_CD = 'YJ6' /* YJ6:매니저 */
MINUS 
SELECT PCODE_ID
 FROM CMM_PCODE_M#
; 

/* 하둡영역 -  */
SELECT STD_DATE
      , COUNT(*) 
  FROM dm.anl_dd_org_h /* 20170101~ 현재일자기준까지 적재되어있음. */
 GROUP BY STD_DATE
 ORDER BY STD_DATE
; 
 
/* 정기주문계약상품실적을 사용해 계약 유지 기간 추출 - PK:정기주문계약ID */
SELECT B.RGLR_ORD_CTR_ID
     , MAX(B.BIZTM_ID) AS BIZTM_ID
     , MAX(B.JIGU_ID)  AS JIGU_ID
     , MIN(B.PFMC_DATE) AS BIZ_BGN_DATE
     , MAX(B.PFMC_DATE) AS BIZ_END_DATE
  FROM (
        SELECT A.RGLR_ORD_CTR_ID
               , A.BIZTM_ID
               , A.JIGU_ID
               , A.PFMC_DATE
               , SUM(A.CHK) OVER(ORDER BY A.PFMC_DATE) AS CUM_SUM
          FROM (
 SELECT /*+PARALLEL(4) */ 
    PFMC_DATE                                      /* 영업일자 */
  , BIZTM_ID                                      /* 영업점코드 */
  , JIGU_ID                                       /* 지구코드 */
  , RGLR_ORD_CTR_ID                                      /* 정기주문계약ID */
  , LAG(RGLR_ORD_CTR_ID,1,'NULL') OVER(ORDER BY PFMC_DATE) AS  PFMC_DATE_PREV
  , LEAD(RGLR_ORD_CTR_ID,1,'NULL') OVER(ORDER BY PFMC_DATE) AS  PFMC_DATE_NEXT
  , CASE WHEN RGLR_ORD_CTR_ID <> LAG(RGLR_ORD_CTR_ID,1,'NULL') OVER(ORDER BY PFMC_DATE) THEN 1
      ELSE 0
      END AS CHK
   FROM ORD_CTR_PRD_PFMC_D /* 정기주문계약상품실적 */
  WHERE 1=1
    AND RGLR_ORD_CTR_ID = '20180516968653'
--AND BIZTM_ID = '2793'
--AND JIGU_ID = '13'
    AND PFMC_DATE >= TO_DATE('20170101','YYYYMMDD') /* 작업일 */
                ) A
) B
 GROUP BY B.RGLR_ORD_CTR_ID 
        , B.CUM_SUM
 ORDER BY MIN(B.PFMC_DATE)     
; 
 
 


/* 일별매출집계를 매출기간 선분이력으로 변경 - PK:PCODE_ID, BIZ_BGN_DATE */
SELECT B.PCODE_ID
     , MAX(B.BIZTM_ID) AS BIZTM_ID
     , MAX(B.JIGU_ID)  AS JIGU_ID
     , MIN(B.BIZ_DATE) AS BIZ_BGN_DATE
     , MAX(B.BIZ_DATE) AS BIZ_END_DATE
  FROM (
        SELECT A.PCODE_ID
               , A.BIZTM_ID
               , A.JIGU_ID
               , A.BIZ_DATE
               , SUM(A.CHK) OVER(ORDER BY A.BIZ_DATE) AS CUM_SUM
          FROM (
 SELECT /*+PARALLEL(4) */ 
    BIZ_DATE                                      /* 영업일자 */
  , BIZTM_ID                                      /* 영업점코드 */
  , JIGU_ID                                       /* 지구코드 */
  , PCODE_ID                                      /* 판매점ID */
  , CASE WHEN PCODE_ID <> LAG(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) THEN 1
      ELSE 0
      END AS CHK
   FROM WEB_PCODE_SL_PFMC_I
  WHERE 1=1
AND BIZTM_ID = '2793'
AND JIGU_ID = '13'
AND PCODE_ID IS NOT NULL /* 해당지구에 매니저 정보가 없다면 매출로 산입하지 않음 */
    AND BIZ_DATE >= TO_DATE('20170101','YYYYMMDD')
                ) A
) B
 GROUP BY B.PCODE_ID 
        , B.CUM_SUM
 ORDER BY MIN(B.BIZ_DATE)      
;

/* 실적없는 매니저 정보 존재가능. 따라서 CMM_PCODE_M 결과 합쳐준다. */
/* 월별 FM - PK : STD_YYMM,PCODE_ID */
SELECT D.STD_YYMM
     , C.PCODE_ID
     , MAX(C.BIZTM_ID) AS BIZTM_ID
     , MAX(C.JIGU_ID)  AS JIGU_ID
 
  FROM (
        /* 일별매출집계를 매출기간 선분이력으로 변경 */
        SELECT B.PCODE_ID
             , MAX(B.BIZTM_ID) AS BIZTM_ID
             , MAX(B.JIGU_ID) AS JIGU_ID
             , MIN(B.BIZ_DATE) AS BIZ_BGN_DATE
             , MAX(B.BIZ_DATE) AS BIZ_END_DATE
          FROM (
                SELECT A.PCODE_ID
                       , A.BIZTM_ID
                       , A.JIGU_ID
                       , A.BIZ_DATE
                       , SUM(A.CHK) OVER(ORDER BY A.BIZ_DATE) AS CUM_SUM
                  FROM (
                 SELECT /*+PARALLEL(4) */ 
                      BIZ_DATE                                      /* 영업일자 */
                    , BIZTM_ID                                      /* 영업점코드 */
                    , JIGU_ID                                       /* 지구코드 */
                    , PCODE_ID                                      /* 판매점ID */
                    , LAG(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) AS  PCODE_ID_PREV
                    , LEAD(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) AS  PCODE_ID_NEXT
                    , CASE WHEN PCODE_ID <> LAG(PCODE_ID,1,'NULL') OVER(ORDER BY BIZ_DATE) THEN 1
                         ELSE 0
                         END AS CHK
                   FROM WEB_PCODE_SL_PFMC_I
                  WHERE 1=1
                  AND BIZTM_ID = '2793'
                  AND JIGU_ID = '13'
                  AND PCODE_ID IS NOT NULL /* 해당지구에 매니저 정보가 없다면 매출로 산입하지 않음 */
                    AND BIZ_DATE >= TO_DATE('20170101','YYYYMMDD') /* 20170101시점부터 적재 */
                        ) A
            ) B
         GROUP BY B.PCODE_ID 
                , B.CUM_SUM
         ORDER BY MIN(B.BIZ_DATE)     
      ) C
  LEFT OUTER JOIN ( 
                    SELECT TO_CHAR(HDY_DATE, 'YYYYMM') AS STD_YYMM
                         , TO_DATE(TO_CHAR(HDY_DATE, 'YYYYMM')||'01','YYYYMMDD') AS FIRST_DATE
                         , LAST_DAY(MAX(HDY_DATE)) AS LAST_DATE
                         , COUNT(*) AS CNT 
                        FROM CMM_HDY  /* 휴일정보 */
                       WHERE USE_YN = 'Y'
                         AND HDY_TP_CD = '20' /* 20:은행휴일 */
                       GROUP BY TO_CHAR(HDY_DATE, 'YYYYMM')
                       ORDER BY TO_CHAR(HDY_DATE, 'YYYYMM')
                     ) D
               ON D.FIRST_DATE <= C.BIZ_END_DATE /* 월중 교체 된 경우는 제외된다. */
              AND D.LAST_DATE >= C.BIZ_BGN_DATE 
             WHERE 1=1
              AND D.STD_YYMM IS NOT NULL
             GROUP BY D.STD_YYMM
                 , C.PCODE_ID  
 ORDER BY STD_YYMM      
;

/* 매니저마스터 지구별 근무기간 겹침 검증 */
SELECT B.BIZTM_ID
     , B.JIGU_ID
     , COUNT(*) AS CNT 
  FROM (
/* 근무기간 겹침 */
SELECT A.BIZTM_ID
                , A.JIGU_ID
                , A.PCODE_ID
                , A.STRIN_DATE
                , A.STROUT_DATE                
                , A.NEXT_STRIN_DATE
     , CASE WHEN A.STROUT_DATE > A.NEXT_STRIN_DATE THEN 'Y'
            END AS INTERSECT_YN             
  FROM (
        SELECT   BIZTM_ID
                , JIGU_ID
                , PCODE_ID
                , STRIN_DATE
                , STROUT_DATE
                , LEAD(STRIN_DATE,1,TO_DATE('99991231','YYYYMMDD') ) OVER(PARTITION BY BIZTM_ID,JIGU_ID ORDER BY STRIN_DATE) AS NEXT_STRIN_DATE
                
          FROM CMM_PCODE_M#
         WHERE 1=1
           AND STRIN_DATE <> STROUT_DATE
          ORDER BY BIZTM_ID
                 , JIGU_ID
                 , STRIN_DATE
                 , STROUT_DATE   
        ) A            
) B 
WHERE INTERSECT_YN = 'Y'
GROUP BY B.BIZTM_ID
     , B.JIGU_ID
ORDER BY B.BIZTM_ID
     , B.JIGU_ID  
 


;


 
 


SELECT *
  FROM CMM_PCODE_M#
 WHERE 1=1
--  AND USE_YN = 'Y'
  AND BIZTM_ID = '2006'
--  AND JIGU_ID = '08'
;

SELECT /*+PARALLEL(4) */
      * 
  FROM ORD_RGLR_ORD_CTR_M
 WHERE 1=1
   AND DEL_YN = 'N'
  AND BIZTM_ID = '2006'
  AND JIGU_ID = '08'
;

SELECT *
  FROM CMM_ORGN_M
 WHERE 1=1
   AND ORGN_LVL = 5
   AND ORGN_ID = '2006'
;