컬럼 분포 수집

컬럼 분포 수집

현행 데이터 모델 분석에서 현행 테이블의 컬럼 분포를 측정해서 수집하는 경우에 활용한다.

컬럼의 분포 조사 항목은 DISTINCT 값/건수, NULL수, 최소값, 최대값 등이 있다. 

 

먼저, 시스템 카탈로그에서 컬럼의 분포를 조사할 수 있다.

SELECT  OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
       ,NUM_DISTINCT		-- DISTINCT 값 	
       ,CASE WHEN DATA_TYPE = 'NUMBER'   THEN TO_CHAR(UTL_RAW.CAST_TO_NUMBER(LOW_VALUE))
             WHEN DATA_TYPE = 'VARCHAR2' THEN UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE)
             ELSE UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE)
        END LOW_VALUE		-- 최소값
       ,CASE WHEN DATA_TYPE = 'NUMBER'   THEN TO_CHAR(UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE))
             WHEN DATA_TYPE = 'VARCHAR2' THEN UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
             ELSE UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
        END HIGH_VALUE		-- 최대값
       ,NUM_NULLS		-- NULL수
FROM   ALL_TAB_COLUMNS 
WHERE  OWNER = 'SCOTT'		-- 해당 OWNER로 변경
AND    TABLE_NAME = 'TAB1'	-- 해당 테이블로 변경

 

ALL_TAB_COLUMNS 테이블의 LOW_VALUE(최소값)와 HIGH_VALUE(최대값)의 데이터 타입이 RAW 타입인데 RAW 타입을 DATE로 변환하는 함수는 제공되지 않으므로 변환 함수를 작성해서 적용하거나 원천 테이블에서 직접 MIN(), MAX() 함수를 사용해서 수집해야 한다.

 

컬럼의 도메인이 번호나 (수치) 값 등은 테이블의 건수와 유사하거나 또는 건수가 매우 많으므로 해당 컬럼의 DISTINCT값을 추출하는 것은 의미가 없다.

 

컬럼의 도메인이 코드인 경우 DISTINCT값 및 DISTINCT건수를 추출하는 것은 의미가 있으며 해당 공통코드와 매핑하여 추출하는 것은 더욱 유용하다.  코드의 업무적인 의미와 오류 데이터 존재 여부 등을 파악하는데 활용된다.

 

예를 들어, TAB1 테이블의 코드1 칼럼의 코드 분포를 조사하는 SQL은 아래와 같다.  공통코드를 관리하는 테이블은 CMN_CD라 가정한다.

SELECT A.코드1, B.CMN_CD_NM, COUNT(*) 
FROM   TAB1 A 
  LEFT OUTER JOIN CMN_CD B
          ON A.코드1 = B.CMN_CD
         AND B.CD_GRP_ID = '1234'	-- 해당 코드그룹ID 로 변경
GROUP BY A.코드1, CMN_CD_NM

 

현행 데이터 모델링 측면에서 중요한 것은 해당 코드의 코드그룹 ID(예: ‘1234’)를 찾는 것이다.  컬럼의 코드그룹 ID를 관리하지 않고 프로그램 소스에만 존재하는 경우도 존재하기 때문이다.

 

코드그룹 ID를 매핑하고 추출하였는데 코드명이 존재하지 않는 경우는 오류 데이터 가능성이 높고 만약 그렇다면 정제(cleansing) 대상이 된다.

 

또한, 여부 컬럼의 경우 DISTINCT값 및 DISTINCT건수를 추출하여 ‘Y/N’ 이 아닌 값이 존재하는 경우 오류 데이터를 정제하거나 컬럼명을 변경하는 등의 작업을 수행한다.

 

<끝>