컬럼별 데이터 프로파일링

 DW 마트나 데이터를 분석하기 위해서는 테이블 내 컬럼별 데이터의 정보가 중요하다. 하지만 모르기도 하고 귀찮기도 해서 살펴보지 않고 사용하며 심지어 PK 컬럼이 스페이스가 들어가 있거나 DW 테이블임에도 불구하고 null 있는 컬럼이 많이 존재하거나 도메인이 맞지 않는 데이터가 있음을 쉽게 볼수 있다. 특이한 점은 많은 프로젝트를 수햄해 왔지만 최근 데이터베이스의 접근이 통제되면서 더욱 데이터에 대한 품질은 떨어지는 듯 하다.

 별도의 데이터 품질을 관리하는 솔루션이 없는 프로젝트에서 수행하다가 필요성에 의해 간단하게나마 쿼리를 작성하여 사용하고 있다. 샘플값과 데이터 패턴도 추가하여 쓰고 있으나 간단한 부분이고 별도의 테이블로 생성하여 관리하는 터리 제외했다.

 

## 컬럼별 데이터 프로파일링
WITH COL_SUMMARY AS(
 SELECT 'COL_NAME' AS COL, COL_NAME AS VAL, COUNT(*) AS FREQ
 FROM TABLE_NAME
 GROUP BY COL_NAME
)
SELECT
    COL_SUMMARY.COL,
    COUNT(*) AS NUM_VALUES,
    MAX(FREQNULL) AS FREQ_NULL,
    MIN(MINVAL) AS MIN_VAL,
    SUM(CASE WHEN VAL = MINVAL THEN FREQ ELSE 0 END) AS NUM_MIN_VALS,
    MAX(MAXVAL) AS MAX_VAL,
    SUM(CASE WHEN VAL = MAXVAL THEN FREQ ELSE 0 END) AS NUM_MIN_VALS,
    MIN(CASE WHEN FREQ = MAXFREQ THEN VAL END ) AS MOST_VAL,
    SUM(CASE WHEN FREQ = MAXFREQ THEN 1 ELSE 0 END ) AS NUM_MOST,
    MAX(MAXFREQ) AS MOST_FREQ,
    MIN(CASE WHEN FREQ = MINFREQ THEN VAL END ) AS LEAST_VAL,
    MIN(CASE WHEN FREQ = MINFREQ THEN 1 ELSE 0 END ) AS NUM_LEAST_VAL,
    MAX(MINFREQ) AS LEAST_VAL_FREQ,
    SUM(CASE WHEN FREQ = 1 THEN FREQ ELSE 0 END) AS NUM_UNIQUE
FROM COL_SUMMARY
    CROSS JOIN (SELECT
                MIN(FREQ) AS MINFREQ,
                MAX(FREQ) AS MAXFREQ,
                MIN(VAL) AS MINVAL,
                MAX(VAL) AS MAXVAL,
                SUM(CASE WHEN VAL IS NULL THEN FREQ ELSE 0 END ) AS FREQNULL
            FROM COL_SUMMARY
            ) SUMMARY_CNT
GROUP BY COL_SUMMARY.COL ;

 

참고: data analysis suing sql and excel