[MySQL] 데이터 정합성 검증(클렌징)

데이터 정합성 검증(클렌징)이란?

데이터 정합성 검증(클렌징)은 단순 데이터 전처리 과정이 아닙니다.
정확한 분석 결과를 도출해내기 위한 필수적인 과정입니다.

그렇기 때문에 프로젝트 초기단계에서부터 적재하고 있는 데이터의 형태/결측치/NULL값 확인이 필요합니다.

오늘은 튼튼하고 완벽한 데이터분석을 위한 데이터 정합성 검증(클렌징) 방법에 대해서 정리해보고자 합니다.

1. 데이터 양(Quantity) 확인

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = SCHEMA();

2. 데이터 Data Type 확인

1). SHOW COLUMNS (특정 테이블의 data type, collation, Null여부)

- 전체 필드 보기
SHOW COLUMNS FROM 테이블명;

- 특정 필드 보기
SHOW FULL COLUMNS FROM olist_sales;

2). DESC (특정 테이블의 data type, Null여부)

DESC 테이블명;

3) information_schema (현재 스키마에 있는 모든 테이블의 data type)

- 전체 필드 보기
SELECT
 ORDINAL_POSITION AS NO, TABLE_NAME, COLUMN_COMMENT,  COLUMN_NAME, COLUMN_TYPE,  IF(IS_NULLABLE = 'YES','','V') AS NOT_NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME IN (
 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = SCHEMA()
)
ORDER BY TABLE_NAME, ORDINAL_POSITION;

3. 데이터 Character Set/Collation 확인

1) Character Set/Collation 확인

- 전체 스키마 
SELECT SCHEMA_NAME AS 'database', DEFAULT_CHARACTER_SET_NAME AS 'character_set', DEFAULT_COLLATION_NAME AS 'collation'
FROM information_schema.SCHEMATA;

- 특정 스키마
mysql> USE database_name;
mysql> SHOW VARIABLES LIKE 'character_set_database';

2) Collation 확인

- 전체 테이블 collation 확인
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = SCHEMA();

- 특정 테이블 collation 확인
SHOW TABLE STATUS WHERE NAME LIKE '테이블명';

- 특정 컬럼 collation 확인
SHOW FULL COLUMNS FROM 테이블명;

4. 데이터 중복 확인 및 삭제

1) 중복데이터 확인

SELECT  COLUMN_NAME ,  -- 중복되는 데이터
         COUNT(COLUMN_NAME) -- 중복 갯수
FROM TABLE_NAME              -- 중복조사를 할 테이블 이름
GROUP BY COLUMN_NAME      -- 중복되는 항목 조사를 할 컬럼
HAVING COUNT(COLUMN_NAME) > 1 ;  -- 1개 이상 (갯수)

2) 중복데이터 삭제

#JOIN을 통한 중복 데이터 삭제
DELETE t1
FROM   TABLE_NAME t1
       JOIN TABLE_NAME t2
         ON t1.column_a = t2.column_a
            AND t1.column_b = t1.column_b
WHERE  t1.idx > t2.idx;


#UPDATE 문을 통한 Soft Delete
UPDATE TABLE_NAME t1
       JOIN TABLE_NAME t2
         ON t1.column_a = t2.column_a
            AND t1.column_b = t2.column_b
SET    t1.delete_yn = true
WHERE  t1.idx > t2.idx;

5. 데이터 이상값(너무 많거나, 적을때) 확인(rank, percentage_rank)

6. 데이터 결손값(있어야하는게 없거나, 없어야하는게 있을 때) 확인

1. 컬럼값이 null인지 아닌지, 조건에 부합하는지에 따라 점수를 부여
2. 각 항목의 평균값을 계산해서 컬럼값의 결손 정도를 판단(1에 가까울 수록 결손도가 없는 것)

SELECT
    round(avg(case when order_delivered_carrier_date is not null then 1.0
					    else 0.0 end),2)as order_delivered_carrier_date,
	
	round(avg(case when order_delivered_customer_date is not null then 1.0
 					    else 0.0 end),2)as order_delivered_customer_date
FROM olist_orders_dataset;