[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜

이번 포스팅에서는 Greenplum DB, Postgresql 에서 테이블을 생성한 후에 SQL로 데이터 전처리하는 몇 가지 방법을 소개하겠습니다. 

예제로 사용할 간단한 고객 정보 테이블을 생성하고, 행 번호, 고객번호, 이름, 나이, 성별, 지역, 등록일 값을 입력해보겠습니다. 

DROP TABLE IF EXISTS public.cust; CREATE TABLE public.cust ( seq_num integer , cust_id text not null , name text not null , age integer , gender text , region text , regist_date date ); INSERT INTO public.cust VALUES  (1, 'A001', 'choi', 25, 'M', 'seoul', '2018-01-25'),  (2, 'A002', 'kang', 30, 'F', 'Busan', '2019-02-08'),  (3, 'A003', 'lee', 29, NULL, 'seoul', '2018-05-30'),  (4, 'B001', 'kim', 35, 'F', 'seoul', '2018-12-22'),  (5, 'B002', 'sung', 34, 'M', 'busan', '2019-02-19'), (6, 'B003', 'park', NULL, NULL, 'SEOUL', '2019-03-15'); SELECT * FROM public.cust ORDER BY seq_num;

위의 테이블에서 

(1) 고객ID(cust_id) 문자열의 첫 번째 문자열을 가져다가 group 칼럼 만들기
    : SUBSTRING(cust_id, 1, 1) AS group

(2) group별로 seq_num 순서에 따라 행 번호 부여하기
    : ROW_NUMBER() OVER(PARTITION BY SUBSTRING(cust_id, 1, 1) ORDER BY seq_num) AS grp_num

(3) 나이(age) 결측값을 전체 평균 값으로 채우기 
    : COALESCE(age, AVG(age) OVER())::INTEGER AS age

(4) 성별(gender) 결측값을 "Unknown" 값으로 채우기 
    : COALESCE(gender, 'Unknown') AS gender

(5) 지역(region) 대문자를 소문자로 바꾸기 
    : LOWER(region) AS region

(6) 이름(name)이 'choi', 'park', 'lee', 'kim'은 그대로 두고, 그 외는 'others'로 바꾸어서 name_2 칼럼 만들기
    : CASE WHEN name IN ('choi', 'park', 'lee', 'kim') THEN name ELSE 'others' END AS name_2

-- substring of id's first character -- insert row number -- fill missing value of 'age' with average -- fill missing value of 'gender' with 'Unknown' -- convert upper letter into lower letter -- if name IN ('choi', 'park', 'lee', 'kim') then name, else 'other' DROP TABLE IF EXISTS public.cust_preprocessed CASCADE; CREATE TABLE public.cust_preprocessed AS  ( SELECT  seq_num,  cust_id,  SUBSTRING(cust_id, 1, 1) AS group,  ROW_NUMBER() OVER(PARTITION BY SUBSTRING(cust_id, 1, 1) ORDER BY seq_num) AS grp_num,  COALESCE("age", AVG(age) OVER())::INTEGER AS "age",  COALESCE(gender, 'Unknown') AS gender, LOWER(region) AS region,  name,  CASE WHEN name IN ('choi', 'park', 'lee', 'kim') THEN name ELSE 'others' END AS name_2,  regist_date FROM public.cust ORDER BY cust_id ) DISTRIBUTED RANDOMLY; SELECT * FROM public.cust_preprocessed ORDER BY seq_num;

다음으로 날짜 형식의 데이터에서 년(year), 월(month), 일(day), 현재 날짜(now), 입력 날짜로 부터 현재 날짜까지의 소요 일(day until now)을 계산해보겠습니다. 

(7) 등록 날짜에서 년(year) 정보 추출
  : EXTRACT (YEAR FROM regist_date)::int AS year

(8) 등록 날짜에서 월(month) 정보 추출
  : EXTRACT (MONTH FROM regist_date)::int AS month

(9) 등록 날짜에서 일(day) 정보 추출
  : EXTRACT (DAY FROM regist_date)::int AS day

(10) 현재 날짜 자동 입력
  : now()::DATE

(11) 이전 등록 날짜에서 현재까지의 소요 일 계산
  : AGE(regist_date) AS time_from_regist

-- extract year, month, day from regist_date DROP TABLE IF EXISTS public.cust_date CASCADE; CREATE TABLE public.cust_date AS  ( SELECT *,  EXTRACT (YEAR FROM regist_date)::int AS year,  EXTRACT (MONTH FROM regist_date)::int AS month,  EXTRACT (DAY FROM regist_date)::int AS day,  now()::DATE, AGE(regist_date) AS time_from_regist FROM public.cust_preprocessed ORDER BY cust_id ) DISTRIBUTED RANDOMLY; SELECT  seq_num, regist_date, year, month, day,  now, time_from_regist  FROM public.cust_date  ORDER BY seq_num;

많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요. :-)