데이터 품질 쿼리
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
데이터 품질 체크(프로파일링 문장) 유효성 체크 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
날짜 체크 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
- 데이터 패턴 체크 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
)
)
)
)