[PostgreSQL초격차] _3_주문데이터_컬럼 가공 함수(Cast, Substring, Left, Right, Concat, ||, Coalesce, Case When Then End 조건문)
0
데이터베이스(SQL)
목록 보기
6/40
본문
-- 주문 데이터로 데이터 가공하기
-- 2021년 6월 1일 하루 동안의 가상의 패션 이커머스 데이터 분석
select * from online_order
cast()로 데이터 형태 변환
cast(기존컬럼명 as 데이터형태) as 새컬럼명 : 컬럼 생성
-- online_order테이블에서 dt컬럼을 숫자형 -> 문자형으로 바꾼 새 컬럼 yyyymmdd 생성
-- 기본 dt 컬럼, 새 yyyymmdd컬럼
select dt , cast(dt as varchar) as yyyymmdd
from online_order 
substring(), left()로 일부 추출
substring(cast(컬럼명 as 데이터형태),n번째부터,n개추출 : 일부 추출해서 컬럼 생성
left(cast(컬럼명 as 데이터형태), 왼쪽에서부터 시작해서 n번째까지 : 일부 추출해서 컬럼 생성
right(cast(컬럼명 as 데이터형태), 왼쪽에서부터 시작해서 n번째까지 : 일부 추출해서 컬럼 생성
-- online_order테이블에서 dt컬럼에서 yyyy, mm, dd 세 컬럼으로 나눠서 생성
-- 기본 dt컬럼, yyyy컬럼, mm컬럼, dd컬럼
select
dt,
left(cast(dt as varchar), 4) as yyyy,
substring(cast(dt as varchar), 5, 2) as mm,
right(cast(dt as varchar), 2) as dd
from online_order oo 
concat()과 ||으로 텍스트 연결
concat(합칠거, 합칠거, 합칠거, 합칠거..): 함수로 연결해주기 , 사용
합칠거 || 합칠거 || 합칠거 || 합칠거.. : | and파이프로 연결해주기 , 미사용
--online_order테이블에서 dt컬럼에서 yyyy-mm-dd 형식으로 이어주는 컬럼 생성
--이어주는 2가지 방식
select
dt,
concat(
left(cast(dt as varchar), 4) , '-',
substring(cast(dt as varchar), 5, 2) , '-',
right(cast(dt as varchar), 2)
) as yyyymmdd
from online_order oo
select
dt,
left(cast(dt as varchar), 4) || '-' || substring(cast(dt as varchar), 5, 2) || '-'|| right(cast(dt as varchar), 2) as yyyymmdd
from online_order oo 
coalesce()로 Null값 변환
코얼리스 함수를 사용합니다.
coalesce(컬럼, null값을 뭐로 바꿀건지) : null값을 임의값으로 바꿔주는 컬럼 생성
--online_order테이블에서 userid컬럼의 null값을 임의값으로 바꿔주기
--userid컬럼과 null값을 0으로 바꿔주는 컬럼
select userid , coalesce (userid,'0')
from online_order oo
order by coalesce 
case when end 조건문
case when 조건1 then 변경1 when 조건2 then 조건2 end
case로 시작하고 end로 끝냅니다.
--조건절로 데이터값 수정하는 새 컬럼 생성 (마치 python의 if문)
--user_info테이블에서 gender컬럼의 M을 남성으로 , F를 여성으로 , 그 외에는 NA로 변환한 컬럼명을 newgender라 지칭
select
gender,
case
when gender = 'M' then '남성'
when gender = 'F' then '여성'
else 'NA'
end as newgender
from user_info ui 
-- 위의 바꾼 newgender컬럼에 대해서 중복값을 제거하여 제대로 변환이 되었는지 확인
select distinct gender, case when gender = 'M' then '남성' when gender = 'F' then '여성' else 'NA' end as newgender
from user_info ui
--user_info 테이블에서 gender나이를 그룹화하여 age_group 컬럼 생성
select
gender,
case when age_band = '20~24' then '20s'
when age_band = '25~29' then '20s'
when age_band = '30~34' then '30s'
when age_band = '35~39' then '30s'
when age_band = '40~44' then '40s'
when age_band = '45~49' then '40s'
else 'NA'
end as age_group
from user_info ui 
--online_order테이블과 user_info테이블을 userid로 left join하여 나이그룹별 매출합계 확인
select
case when age_band = '20~24' then '20s'
when age_band = '25~29' then '20s'
when age_band = '30~34' then '30s'
when age_band = '35~39' then '30s'
when age_band = '40~44' then '40s'
when age_band = '45~49' then '40s'
else 'NA'
end as age_group,
sum(gmv)as gmv
from online_order oo
left join user_info ui on oo.userid = ui.userid
group by 1
order by 2 desc
--카테고리별 매출합계 (매출 top3 카테고리 확인)
select c.cate1, sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
join category c on i.category_id = c.id
group by 1
order by 2 desc
-- 스커트 > 티셔츠 > 원피스 순으로 top3 
--매출 top3 카테고리와 그 외 상품의 매출액 비교
select
case when cate1 in ('스커트','티셔츠', '원피스') then 'TOP3' else '기타' end as item_type,
sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
join category c on i.category_id = c.id
group by 1
order by 2 desc 
특정 키워드가 담긴 상품과 그렇지 않은 상품의 매출 비교 (+item 갯수도 같이 확인)
-- 1. 먼저 item테이블에서 아이템명 확인
select item_name
from item
-- '필수', '시크', '공용' , '깜찍' 이 많이 들어가 있는 것 같습니다.
-- Basic , Chic, Unisex, Cute 로 바꿔서 아이템 갯수가 몇 개 인지 확인해보겠습니다. 
-- 2. 텍스트 추출한 컬럼 생성
select
item_name ,
case
when item_name like '%필수%' then 'Basic'
when item_name like '%시크%' then 'Chic'
when item_name like '%공용%' then 'Unisex'
when item_name like '%깜찍%' then 'Cute'
else 'Other'
end as item_concept
from item 
-- 3. 텍스트 추출한 item_concept컬럼과 거래액 데이터를 합쳐서 같이 확인
select
case
when item_name like '%필수%' then 'Basic'
when item_name like '%시크%' then 'Chic'
when item_name like '%공용%' then 'Unisex'
when item_name like '%깜찍%' then 'Cute'
else 'Other'
end as item_concept,
sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
group by 1
order by 2 desc
-- 만약 깜찍, 시크 등이 동시에 포함된 경우 --> 먼저 온 조건 값으로 맵핑됩니다. 만약 깜찍, 시크 등이 동시에 포함된 경우 --> 먼저 온 조건 값으로 맵핑됩니다.
정리
-- 주문 데이터로 데이터 가공하기
-- 2021년 6월 1일 하루 동안의 가상의 패션 이커머스 데이터 분석
select * from online_order
--------------------------------------------------- 1. 원하는 형식으로 컬럼 가공
-- online_order테이블에서 dt컬럼을 숫자형 -> 문자형으로 바꾼 새 컬럼 yyyymmdd 생성
-- 기본 dt 컬럼, 새 yyyymmdd컬럼
select dt , cast(dt as varchar) as yyyymmdd
from online_order
-- online_order테이블에서 dt컬럼에서 yyyy, mm, dd 세 컬럼으로 나눠서 생성
-- 기본 dt컬럼, yyyy컬럼, mm컬럼, dd컬럼
select
dt,
left(cast(dt as varchar), 4) as yyyy,
substring(cast(dt as varchar), 5, 2) as mm,
right(cast(dt as varchar), 2) as dd
from online_order oo
--online_order테이블에서 dt컬럼에서 yyyy-mm-dd 형식으로 이어주는 컬럼 생성
--이어주는 2가지 방식
select
dt,
concat(
left(cast(dt as varchar), 4) , '-',
substring(cast(dt as varchar), 5, 2) , '-',
right(cast(dt as varchar), 2)
) as yyyymmdd
from online_order oo
select
dt,
left(cast(dt as varchar), 4) || '-' || substring(cast(dt as varchar), 5, 2) || '-'|| right(cast(dt as varchar), 2) as yyyymmdd
from online_order oo
--online_order테이블에서 userid컬럼의 null값을 임의값으로 바꿔주기
--userid컬럼과 null값을 0으로 바꿔주는 컬럼
select userid , coalesce (userid,'0')
from online_order oo
order by coalesce
select gender
from user_info ui
--조건절로 데이터값 수정하는 새 컬럼 생성 (마치 python의 if문)
--user_info테이블에서 gender컬럼의 M을 남성으로 , F를 여성으로 , 그 외에는 NA로 변환한 컬럼명을 newgender라 지칭
select
gender,
case
when gender = 'M' then '남성'
when gender = 'F' then '여성'
else 'NA'
end as newgender
from user_info ui
-- 위의 바꾼 newgender컬럼에 대해서 중복값을 제거하여 제대로 변환이 되었는지 확인
select distinct gender, case when gender = 'M' then '남성' when gender = 'F' then '여성' else 'NA' end as newgender
from user_info ui
--user_info 테이블에서 gender나이를 그룹화하여 age_group 컬럼 생성
select
gender,
case when age_band = '20~24' then '20s'
when age_band = '25~29' then '20s'
when age_band = '30~34' then '30s'
when age_band = '35~39' then '30s'
when age_band = '40~44' then '40s'
when age_band = '45~49' then '40s'
else 'NA'
end as age_group
from user_info ui
--online_order테이블과 user_info테이블을 userid로 left join하여 나이그룹별 매출합계 확인
select
case when age_band = '20~24' then '20s'
when age_band = '25~29' then '20s'
when age_band = '30~34' then '30s'
when age_band = '35~39' then '30s'
when age_band = '40~44' then '40s'
when age_band = '45~49' then '40s'
else 'NA'
end as age_group,
sum(gmv)as gmv
from online_order oo
left join user_info ui on oo.userid = ui.userid
group by 1
order by 2 desc
--카테고리별 매출합계 (매출 top3 카테고리 확인)
select c.cate1, sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
join category c on i.category_id = c.id
group by 1
order by 2 desc
-- 스커트 > 티셔츠 > 원피스 순으로 top3
--매출 top3 카테고리와 그 외 상품의 매출액 비교
select
case when cate1 in ('스커트','티셔츠', '원피스') then 'TOP3' else '기타' end as item_type,
sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
join category c on i.category_id = c.id
group by 1
order by 2 desc
-- 특정 키워드가 담긴 상품과 그렇지 않은 상품의 매출 비교 (+item 갯수도 같이 확인)
-- 1. 먼저 item테이블에서 아이템명 확인
select item_name
from item
-- '필수', '시크', '공용' , '깜찍' 이 많이 들어가 있는 것 같습니다.
-- Basic , Chic, Unisex, Cute 로 바꿔서 아이템 갯수가 몇 개 인지 확인해보겠습니다.
-- 2. 텍스트 추출한 컬럼 생성
select
item_name ,
case
when item_name like '%필수%' then 'Basic'
when item_name like '%시크%' then 'Chic'
when item_name like '%공용%' then 'Unisex'
when item_name like '%깜찍%' then 'Cute'
else 'Other'
end as item_concept
from item
-- 3. 텍스트 추출한 item_concept컬럼과 거래액 데이터를 합쳐서 같이 확인
select
case
when item_name like '%필수%' then 'Basic'
when item_name like '%시크%' then 'Chic'
when item_name like '%공용%' then 'Unisex'
when item_name like '%깜찍%' then 'Cute'
else 'Other'
end as item_concept,
sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
group by 1
order by 2 desc
-- 만약 깜찍, 시크 등이 동시에 포함된 경우 --> 먼저 온 조건 값으로 맵핑됩니다. 
데이터기반 스토리텔링을 통해 인사이트를 얻습니다.