"데이터 품질 쿼리"의 두 판 사이의 차이
DB CAFE
(새 문서: 데이터 품질 체크(프로파일링 문장) 프로필사진 delphkws권위수 (delp****) 채팅 작성일 2018.09.20. 14:06 | 조회 7 유효성 체크 SELECT M.DOC_SE_CODE F...) |
|||
(같은 사용자의 중간 판 2개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | 데이터 품질 체크(프로파일링 문장) | + | == 데이터 품질 체크(프로파일링 문장) == |
− | + | === 유효성 체크 === | |
− | + | <source lang=sql> | |
− | |||
− | |||
− | |||
− | 유효성 체크 | ||
SELECT M.DOC_SE_CODE | SELECT M.DOC_SE_CODE | ||
FROM (SELECT DOC_SE_CODE | FROM (SELECT DOC_SE_CODE | ||
17번째 줄: | 13번째 줄: | ||
AND CD.GROUP_CODE = '1063' | AND CD.GROUP_CODE = '1063' | ||
WHERE CD.DETAIL_CODE IS NULL | WHERE CD.DETAIL_CODE IS NULL | ||
− | + | </source> | |
− | 날짜 체크 | + | === 날짜 체크 === |
+ | <source lang=sql> | ||
SELECT COUNT(CREATE_DE) AS ANA_CNT | SELECT COUNT(CREATE_DE) AS ANA_CNT | ||
FROM M.GVMAGNC_STD_CODE | FROM M.GVMAGNC_STD_CODE | ||
36번째 줄: | 33번째 줄: | ||
END != 1 | END != 1 | ||
− | + | </source> | |
− | + | === 데이터 패턴 체크 === | |
+ | <source lang=sql> | ||
SELECT COUNT(*) AS ERR_CNT | SELECT COUNT(*) AS ERR_CNT | ||
FROM (SELECT DATA_TYPE | FROM (SELECT DATA_TYPE | ||
56번째 줄: | 54번째 줄: | ||
) | ) | ||
) | ) | ||
+ | </source> | ||
+ | [[Category:oracle]] |
2020년 12월 18일 (금) 10:58 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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
)
)
)
)