[220908] SQL(postgres) 기초 2

##join
join이 필요한 이유: 필요한 정보(상품명, 유저정보, 카테고리 정보 등)가 각기 다른 테이블에 분산 저장되어 있을 때, 이를 하나의 테이블로 가져와 보기 좋게 데이터를 추출하기 위해

join의 위치 및 문법
select 컬럼명
from 테이블명 (as) a
join 테이블명2 (as) b on a.컬럼명 = b.컬럼명


두 개의 테이블에서 교집합으로 나타나는 부분만 필터링되는 효과까지 있다.

#inner join: 속도가 빠름
#left join:
이 두 가지가 가장 중요함
right join은 left join으로 바꾸어서 사용하면 됨
full join은 초보자 수준에서는 잘 쓰지 않음


1. 행 중복!
category_id 값이 같은 값이면 중복이 일어남

2. where절 실수
where 자리에 and가 오지 않았는지 확인

3. on절 실수
on절 다음에 =,<,>,<=,between 등의 연산자가 있는지 확인한다.
오류: boolean 연산자가 들어 있어야 함


--실습 3) 주문 데이터로 데이터 가공하기 연습
--2021년 6월 1일 하루동안의 주문 건
--실제 존재하는 패션 이커머스 데이터라고 생각하고 분석을 진행해 주세요!


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

--a) 숫자를 문자열로 바꿔주기
select dt, cast(dt as varchar)
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 형식으로 이어주기(concat쓰기, || || 쓰기)
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


--d) null 값인 경우 임의값으로 바꿔주기('NA'로 바꾸려하면 오류뜸)
select oo.userid, coalesce (oo.userid, 0)
from online_order oo
left join user_info ui on oo.userid = ui.userid

select coalesce (ui.gender, 'NA') as gender, coalesce (ui.age_band, 'NA') as age_band,
sum(oo.gmv) as gmv
from online_order oo
left join user_info ui on oo.userid = ui.userid
group by 1,2
order by 1,2


--e) 내가 원하는 컬럼 추가해보기
select distinct case when gender = 'M' then '남성'
      when gender = 'F' then '여성'
      else 'NA' end as gender
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 = '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 1



--g) TOP3 카테고리와 그 외 상품의 매출액 비교하기
select
case when cate1 in ('스커트', '티셔츠', '원피스') then 'TOP 3'
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




--h) 특정 키워드가 담긴 상품과 그렇지 않은 상품의 매출 비교하기 (+item 개수도 같이 확인!)
select case when item_name like '%깜직%' then '깜찍 컨셉'
  when item_name like '%시크%' then '시크 컨셉'
  when item_name like '%청순%' then '청순 컨셉'
  when item_name like '%기본%' then '기본 컨셉'
  else '미분류'
  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






--2. 날짜 관련 함수 활용하기--------------------------------------------------------------

--a) 오늘을 나타내는 기본 구문
select now()

select current_date

select current_timestamp


--b) 날짜 형식에서 문자 형식으로 변환하기
select to_char(now(), 'yyyymmdd')

select to_char(now(), 'yyyy / mm / dd')

--c) 날짜 더하기/빼기
select now() + interval '1 day'

select now() - interval '1 month'

--d 날짜로부터 연도, 월, 주 확인하기
select date_part('year', now())

select date_part('day', now())

--d) 최근 1년 동안의 매출액 확인하기
select *
from gmv_trend gt
where cast(yyyy as varchar) || cast(mm as varchar)
>= cast(date_part('year', now() - interval '1 year') as varchar) || cast(date_part('month', now() - interval '1 year') as varchar)
order by 2,3

--3. 할인률, 판매가, 이익률 계산하기(sum은 분자와 분모 모두에 입력)
select c.cate1 ,
round(sum(cast(discount as numeric)) /sum(gmv), 2) * 100 as discount_rate,
sum(gmv - discount) as paid_amount,
round(sum(cast(product_profit as numeric)) / sum(gmv), 2)*100 as product_margin,
round(sum(cast(total_profit as numeric)) / sum(gmv)*100)|| '%' as total_margin
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 3 desc


--4. 고객 관점에서의 분석 (인당 평균 구매수량 / 인당 평균 구매금액)
--100명의 고객이 구매를 하였고, 총 판매수량이 200개
--인당 평균 구매수량 = 총 판매수량 / 총 고객 수
--인당 평균 구매금액 = 총 구매금액 / 총 고객 수





--인당 구매수량이 높은 상품은?(중복값을 해재하고 고유의 값을 셀 때 쓰는 함수 count(distinct))
select i.item_name
, sum(unitsold) as unitsold
, count(distinct userid) as user_count
, round(sum(cast(unitsold as numeric)) / count(distinct userid), 2) as avg_unitsold_per_customer
, round(sum(cast(gmv as numeric)) / count(distinct userid)) as avg_gmv_per_customer
from online_order oo
join item i on oo.itemid = i.id
group by 1
order by 4 desc




--인당 구매금액이 높은 성/연령대는? (지난 실습에서, 단순 구매금액 총합으로는 20대 여성이 높았는데...!)
select gender, age_band,
sum(gmv) as gmv,
count(distinct oo.userid) as user_count,
sum(gmv) / count(distinct oo.userid) as avg_gmv_per_customer
from online_order oo
join user_info ui on oo.userid = ui.userid
group by 1,2
order by 5 desc



#데이터 유형 정리
문자형: CHAR, VARCHAR, LONG, CLOB
숫자형: INT, BICINT(좀 더 큰 정수), NUMERIC, FLOAT(소수점 길이에 제한이 없는 상세한 숫자), DOUBLE
날짜형: DATE(년월일), TIMESTAMP(몇분몇초까지 나타냄)
참/거짓: BOOOLEAN

#데이터 유형 바꾸기
[숫자형<->문자형]
cast(컬럼명 as ~~ )
컬럼명:: ~~
[문자형 <-->날짜형]
to_date(컬럼명, yyyymmdd)
to_char(컬럼명, yyyymmdd)

#데이터 가공하기
특정 문자열만 잘라내기: left, right, substring
문자열 합치기: A || B, concat(A,B)
조건별로 그룹핑하기: case when ~ else ~ end
cf. else가 없으면 해당되지 않는 애들은 다 null이 됨
cf. end가 없으면 오류남!!

null값 치환하기(문자):coalesce(컬럼명, ~~~) nvl(컬럼명, ~~~)
특정 문자열 자르기: trim, ltrim, rtrim
특정 문자열을 임의값으로 변경하기: replace
대문자로 변경: upper
소문자로 변경: lower
문자열의 길이 반환: length, len
반올림 / 올림 / 내림: round / ceiling / trunc
절대값 반환: abs
null값 치환하기(숫자): coalesce(컬럼명, 0) nvl(컬럼명, 0)
날짜 더하기: 날짜 – interval ‘1 day’, dateadd(‘day’,01,날짜)
날짜간 빼기: datediff(‘day’,날짜,날짜)
날짜에서 특정 월/주/분기 등 추출하기: date_part(‘mounth’,날짜), to_char(날짜, ‘mm’)
현재 날짜의 월 1일, 분기 일, 주 시작일 구하기: date_trunc(‘month’, now()), date_trunc(‘quarter’, now()), date_trunc(‘week’, now())

#서브쿼리
sql절 안에 sql절이 들어있음(하나의 sql문으로 풀 수 없는, 조금 더 복잡한 데이터를 추출할 때 사용)
서브쿼리의 위치: 거의 대부분 from절 혹은 join절에 들어옴

서브쿼리의 예시)
지난달 첫 구매 고객의 월별 주문빈도는?
->(지난달 첫 구매 고객 추출)까지 쿼리 1, (이에 대한 월별 주문빈도를 물어보는 것)이 쿼리 2
지난달 첫 구매 고객 중 이번달도 구매를 진행한 고객은?
지난달 구매한 상품을 이번달에재주문한 상품은?
이번달 매출액이 높은 카테고리의 월별 성장률은?