행위

데이터 품질 쿼리

DB CAFE

thumb_up 추천메뉴 바로가기


1 데이터 품질 체크(프로파일링 문장)[편집]

1.1 유효성 체크[편집]

SELECT M.DOC_SE_CODE
   FROM (SELECT DOC_SE_CODE
           FROM SPHSUSR.ATCHMNFL
          WHERE 1 = 1
            AND DOC_SE_CODE IS NOT NULL
            AND DOC_SE_CODE IS NOT NULL
          ) M
        LEFT OUTER JOIN SPHSUSR.DETAIL_CODE  CD
          ON M.DOC_SE_CODE = CD.DETAIL_CODE
         AND CD.GROUP_CODE = '1063'
  WHERE CD.DETAIL_CODE IS NULL

1.2 날짜 체크[편집]

SELECT COUNT(CREATE_DE) AS ANA_CNT
   FROM M.GVMAGNC_STD_CODE
  WHERE 1 = 1
    AND CREATE_DE IS NOT NULL
    AND (CASE WHEN SUBSTR(CREATE_DE, 1,1) BETWEEN '0' AND '9' 
                     AND SUBSTR(CREATE_DE, 2,1) BETWEEN '0' AND '9' 
                     AND SUBSTR(CREATE_DE, 3,1) BETWEEN '0' AND '9' 
                     AND SUBSTR(CREATE_DE, 4,1) BETWEEN '0' AND '9' 
                     AND SUBSTR(CREATE_DE, 5,1) BETWEEN '0' AND '9' 
                     AND SUBSTR(CREATE_DE, 6,1) BETWEEN '0' AND '9' 
   THEN CASE WHEN SUBSTR(CREATE_DE,5,2) IN ('01','02','03','04','05','06','07','08','09','10','11','12') 
                             THEN 1
                             ELSE 0
                         END
   ELSE 0 

   END != 1

1.3 데이터 패턴 체크[편집]

SELECT COUNT(*) AS ERR_CNT
   FROM (SELECT DATA_TYPE
               ,TRANSLATE(TRAN_DATA1,TRAN_DATA2, LPAD('C',TRAN_DATA2_LENGTH, 'C')) AS PATTERN
           FROM (SELECT DATA_TYPE
                        ,TRAN_DATA1
                        ,NVL(REPLACE(TRANSLATE(TRAN_DATA1,'9Aa~`!@#$%^&*()_-+=|{}[]:;?/<>,.|""B', LPAD(' ', 36,' ')), ' ', ''),'C') AS TRAN_DATA2
                        ,LENGTH(NVL(REPLACE(TRANSLATE(TRAN_DATA1,'9Aa~`!@#$%^&*()_-+=|{}[]:;?/<>,.|""B', LPAD(' ', 36,' ')), ' ', ''),'C')) AS TRAN_DATA2_LENGTH
                   FROM (SELECT DATA_TYPE
                               ,TRANSLATE(DATA_TYPE,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ~`!@#$%^&*()_-+=|{}[]:;?/<>,.|"" '
                                                    ,'9999999999aaaaaaaaaaaaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAA~`!@#$%^&*()_-+=|{}[]:;?/<>,.|""B') AS TRAN_DATA1
                           FROM M.USER_PH
                          WHERE 1 = 1
                             AND DATA_TYPE IS NOT NULL

                         )
                 )
         )
)