[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜
Greenplum and PostgreSQL Database 2019. 3. 16. 22:30이번 포스팅에서는 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 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; |
많은 도움이 되었기를 바랍니다.
이번 포스팅이 도움이 되었다면 아래의 '공감~
'를 꾹 눌러주세요. :-)
