조건

--1. 원하는 형식으로 컬럼 가공하기--------------------------------------------------------------

--a) 숫자를 문자열로 바꿔주기
select dt, cast(dt as varchar) as yyyymmdd
from online_order oo
;
--b) 문자열 컬럼에서 일부만 잘라내기
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 
;
--c) yyyy-mm-dd 형식으로 이어주기
select dt, cast(dt as varchar) as yyyymmdd,
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 
;
select dt, cast(dt as varchar) as yyyymmdd,
concat(
left(cast(dt as varchar),4), '-',
substring(cast(dt as varchar),5,2), '-',
right(cast(dt as varchar),2))
from online_order oo
;
select dt, cast(dt as varchar) as yyyymmdd,
left(cast(dt as varchar),4) || '-' ||
substring(cast(dt as varchar),5,2) || '-' ||
right(cast(dt as varchar),2)
from online_order oo 
;
--d) null 값인 경우 임의값으로 바꿔주기
select oo.userid, coalesce (oo.userid, 0) as userid 
from online_order oo 
left join user_info ui on oo.userid  = ui.userid 
;
select coalesce (ui.gender,'NA'),coalesce (ui.age_band,'NA') , sum(oo.gmv)
from online_order oo 
left join user_info ui  on oo.userid = ui.userid
group by 1,2
order by 2
;

--e) 내가 원하는 컬럼 추가해보기
select case when gender = 'F' then '여성' when gender = 'M' then '남성' else 'NA' end 
from user_info ui 
;
--f) 연령대 그룹 만들어보기 (20대, 30대, 40대)
select
case when ui.age_band = '20~24' then '20s' 
     when ui.age_band = '25~29' then '20s' 
     when ui.age_band = '30~34' then '30s' 
     when ui.age_band = '35~39' then '30s' 
     when ui.age_band = '40~44' then '40s'
     when ui.age_band = '40~49' then '40s'
     else 'NA'
end 
, sum(gmv)
from online_order oo
left join user_info ui on oo.userid = ui.userid
group by 1
order by 1
;
--g) TOP3 카테고리와 그 외 상품의 매출액 비교하기
select case when c.cate1 in ('스커트','티셔츠','원피스') then 'TOP3' else '기타' end
, sum(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
;
--h) 특정 키워드가 담긴 상품과 그렇지 않은 상품의 매출 비교하기 (+item 개수도 같이 확인!)
select 
case when i.item_name like '%깜찍%' then '깜찍 컨셉'
     when i.item_name like '%시크%' then '시크 컨셉'
     when i.item_name like '%청순%' then '청순 컨셉'
     when i.item_name like '%기본%' then '기본 컨셉'
     else '기타'
     end ,
sum(gmv)
from item i 
left join online_order oo on i.id  = oo.itemid 
group by 1
order by 2 desc

cast(dt as varchar) -> dt 숫자를 문자로 가공

left(cast(dt as varchar),4) -> 4글자 yyyy

substring(cast(dt as varchar),5,2) -> 5번째 2글자 mm

right(cast(dt as varchar),2) -> 2글자 mm

concat(
left(cast(dt as varchar),4), '-',
substring(cast(dt as varchar),5,2), '-',
right(cast(dt as varchar),2)) as yyyymmdd
출력 값 yyyy-mm-dd
select dt, cast(dt as varchar) as yyyymmdd,
left(cast(dt as varchar),4) || '-' ||
substring(cast(dt as varchar),5,2) || '-' ||
right(cast(dt as varchar),2)
출력 값 yyyy-mm-dd

coalesce(oo.userid , 0) -> userid null값을 0으로 바꿈

​

case when ~ = 'O' then '~' //O라면 ~로 바꿈

when ~ = 'X' then '~~' //X라면 ~~로바꿈

...

else '^^' // 그 외 ^^로 바꿈

end

​

case when ~ in ('~','~') then 'O' //~,~라면 O

when ~ in ('d','d') then 'D' //d,d라면 D ...

else 'X' //그 외 X로 바꿈

end

 

case when ~ like '%jh%' then 'O' // jh 포함하면 O

when ~ like '%oo%' then 'D' // oo포함하면 D ...

else 'X' //그 외 X로 바꿈

end

​