[👩‍💻TIL 48일차 ] 유데미 스타터스 취업 부트캠프 4기

 

📍 발표 피드백

• 쿼리 결과 테이블, 바 차트같은 간단한 차트까지 나타내주면 이해하기 쉽다
• 결론도 중요한 부분은 색깔을 다르게 하거나 두껍게 해주기
• 코드, 결과 테이블, 차트, 설명까지 한번에 넣어주기
• 주제, 목표 -> 전체에서 부분으로 가는 흐름
• 사진 첨부로 이해하기 좋았다.

---

📍 제품/카테고리 매출 지표 분석

• 전체 제품 매출 순위 및 매출 비율
• 카테고리별 제품 매출 순위 및 매출 비율

 

➡️ orders, order_details(unit_price: 판매될 당시의 단가), products(unit_price: 현재 절대적 단가), categories, suppliers

 

 

cte로 임시테이블 만들기

 

with cte_products_sale as(
select o.order_id , o.customer_id , order_date 
	, to_char( o.order_date, 'yyyy') as year
    , to_char( o.order_date, 'q') as quarter
    , to_char( o.order_date, 'mm') as month
    , to_char( o.order_date, 'dd') as day
    , od.product_id , od.unit_price , od.unit_price 단가, od.quantity , od.discount
    , od.unit_price * od.quantity * (1-od.discount) as 매출액
    , c.category_id , c.category_name 
    , p.product_name , p.unit_price 고정단가, p.discontinued 
    , s.supplier_id , s.company_name , s.country , s.city 
from orders o , order_details od , categories c , products p , suppliers s 
where o.order_id = od.order_id 
and p.category_id = c.category_id 
and od.product_id = p.product_id 
and p.supplier_id = s.supplier_id 
)
select count(*)
from cte_products_sale;

➡️ 조인이 잘 되었는지 확인해주기 위해서 count로 행 개수를 세어보기

 

 

제품별 매출액 순위

 

-- 제품별 매출액 순위
select category_name, category_id, product_name, supplier_id, country
	, sum(매출액) as 전체매출액
	, rank () over (order by sum(매출액) desc) as 순위
	, round(sum(매출액)::numeric / sum(sum(매출액)::numeric) over() * 100, 1) || '%' as 비율
from cte_products_sale
group by 1,2,3,4,5
order by sum(매출액) desc;

매출액이 높은 이유 (가설)

• 단가가 높아서
• 많이 팔려서
• 판매 기간이 길어서

 

-- 카테고리별 매출액 순위
select category_name, category_id, sum(매출액) as 전체매출액
	, rank () over (order by sum(매출액) desc) as 순위
	, round(sum(매출액)::numeric  / sum(sum(매출액)::numeric) over() * 100, 1) || '%' as 비율
from cte_products_sale
group by 1,2
order by 3 desc;

 

※ round 오류날 때 numeric으로 형변환해주기

---

📍 ABC 분석

ABC분석

 

: 통계적 방법에 의해 관리대상을 A, B, C 그룹으로 나누고, 먼저 A그룹을 최중점 관리대상으로 선정하여 관리노력을 집중함으로써 관리효과를 높이려는 분석방법

➡️ A: 70%, B: 90%

 

① 매출액이 많은 순으로 정리한다.

② 총매출액을 100%로 하여 고객별 백분비를 산출한다.

③ 그 누적 구성비율을 상위의 고객부터 순서대로 누적해 간다.

④ 그래프의 세로에 매출액 점유비의 누적치를, 가로축에 고객을 기입하고 고객별 누적구성비를 표시해 간다.

⑤ 세로축의 70%와 90%의 누적치 해당점에서 가로선을 긋고, 그래프의 선과의 교차점에서 수직선을 긋는다

 

-- 1.제품별 매출액
, cte_amount as (
select product_id, product_name
     , round(sum(amount)) as 매출액
from cte_products_sale
group by 1,2
order by product_id
)

 

-- 2. 구성비
, cte_ratio as(
select *
     , sum(매출액) over() as 전체매출액
     , 매출액/sum(매출액) over()*100 as 구성비
from cte_amount
)

 

 

-- 3. 구성비 누계
, cte_ratio_agg as(
select * 
	, sum(구성비) over(order by 구성비 desc) as 구성비누계
from cte_ratio
order by 구성비 desc
)

 

 

-- 4.등급 부여
select * 
	, case 
		when 구성비누계<=70 then 'A'
		when 구성비누계<=90 then 'B'
		else 'C'
	end as 등급
from cte_ratio_agg;

 

✔️ 시각화

 

 

---

📍 피벗: 카테고리별 1~3위 제품 피벗

-- 1.매출액 계산
, cte_amount as (
	select category_name, product_name, round(sum(amount)) as 매출액
	from cte_products_sale
	group by 1,2
	order by 1,2
)

 

-- 2.카테고리별 순위계산
, cte_rank as(
	select *
		, row_number() over(partition by category_name order by 매출액 desc) as rank
	from cte_amount
)

 

-- 3. top3
, cte_top3 as(
select *
from cte_rank
where 매출순위<=3
)

 

-- 4. 피벗
select category_name
		, max(case when 매출순위=1 then product_name end) "1위"
		, max(case when 매출순위=2 then product_name end) "2위"
		, max(case when 매출순위=3 then product_name end) "3위"
from cte_top3
group by 1;

 

---

📍 카테고리명: 컬럼으로 가게

• 매출순위가 다 나오도록
• null 안보이도록

 

✔️ 2번까지는 위와 동일

 

-- 3. 피벗
select 매출순위 
	, coalesce(max(case when category_name='Beverages' then product_name end),'') as Beverages
	, coalesce(max(case when category_name='Condiments' then product_name end),'') as Condiments
    , coalesce(max(case when category_name='Confections' then product_name end),'') as Confections
    , coalesce(max(case when category_name='Dairy Products' then product_name end),'') as "Dairy Products"
    , coalesce(max(case when category_name='Grains/Cereals' then product_name end),'') as "Grains/Cereals"
    , coalesce(max(case when category_name='Meat/Poultry' then product_name end),'') as "Meat/Poultry"
    , coalesce(max(case when category_name='Produce' then product_name end),'') as Produce
    , coalesce(max(case when category_name='Seafood' then product_name end),'') as Seafood
from cte_rank
group by 1
order by 1;

 

• coalesce: 문자열 =  '',  숫자 = 0
• 열 이름에 띄어쓰기나 특수기호 들어가면 ""로 묶어주기
• 자동화X 원하는 만큼 복붙해야 한다.
  수동으로는 잘 사용하지 않게 될 기능인것같다.

---

📍 개인과제

-- 1. 필요한 데이터 가져오기
, cte_sum as(
select category_name as 카테고리명, product_name
     , sum(quantity) as 총판매수량
     , round(sum(amount))as 총매출액
from cte_products_sale
group by 1,2
order by 3 desc
)

 

-- 2. 순위 지정하기
, cte_rank as(
select rank() over(order by 총판매수량 desc), *
from cte_sum
)

 

💡 rank로 순위를 지정해서 총매출액이 같을 시 같은 순위가 부여된다.

      ※ 중복순위 표현 후 건너뜀 

 

---

 

top5

 

-- 1. 국가-제품별 판매수량 구하기
, cte_sum_quantity as(
	select country, product_name
	     , sum(quantity) as 판매수량
	     , round(sum(amount)) as 매출액
	from cte_products_sale
	group by 1,2
    order by 1,2
)

 

-- 2. 국가별 총매출액 구하기
, cte_tot_amount as(
	select * 
		, sum(매출액) over(partition by country) 국가총매출액
	from cte_sum_quantity
)

 

-- 3.국가-제품별 rank (매출액 높은 순으로)
, cte_rank as (
	select row_number() over(partition by country order by 매출액 desc) as 순위, *
	from cte_tot_amount
)

 

-- 4. top5 가져오기
, cte_top5 as(
	select * 
	from cte_rank
	where 순위<=5
)

 

-- 5. 피벗
, cte_pivot as(
select country, 국가총매출액
     , max(case when 순위=1 then product_name end) "1위"
     , max(case when 순위=2 then product_name end ) "2위"
     , max(case when 순위=3 then product_name end ) "3위"
     , max(case when 순위=4 then product_name end ) "4위"
     , max(case when 순위=5 then product_name end ) "5위"
from cte_top5
group by 1,2
order by 2 desc
)

 

-- 6. 최종 (top 5)
select country, "1위", "2위", "3위", "4위", "5위"
from cte_pivot
limit 5;

 

---

 

 

-- 1. 분기별 총판매수량
, cte_period_rank as(
	select concat(year,'-',quarter) as period
		, product_name
		, sum(quantity) as 총판매수량
		, sum(amount) as 총매출액
	from cte_products_sale
	group by 1,2
	order by 1,3 desc
)

 

-- 2. 순위
, cte_period_rank as(
	select row_number() over(partition by period order by 총판매수량 desc, 총매출액 desc) as rank
		, *
	from cte_period
)

➡️ 총판매수량이 같을 경우 총매출액으로 내림차순

 

-- 3. 피벗
select rank
     , max(case when period='1997-1' then product_name end) as "1997-1분기"
     , max(case when period='1997-2' then product_name end) as "1997-2분기"
     , max(case when period='1997-3' then product_name end) as "1997-3분기"
     , max(case when period='1997-4' then product_name end) as "1997-4분기"
from cte_period_rank
where rank <= 10
group by 1
order by 1;

 

 

---

 

✔️ 2번까지는 이전과 동일

 

-- 3. 이전분기 컬럼 생성
, cte_pre_period as(
	select rank, period, product_name
		, case 
			-- 이전분기가 0이면 (현재 분기가 1) 전년도 4분기를 가져오기
			when pre_quarter = 0 then concat((year-1),'-','4')
			else concat(year,'-',pre_quarter)
			end as pre_period	
	from (
		select *
			, substr(period,1,4)::int as year
			, substr(period,6,1)::int-1 as pre_quarter
		from cte_period_rank2
	)a
)

 

서브쿼리 결과

 

최종결과

 

-- 4. 이전 분기로 self 조인하여 rank_diff 구하기
, ces_pre_period_rank_diff as(
	select c1.*
		, c2.rank as rank2
		-- rank 차이가 null이라면(전분기값이 없어서 then new)
		, coalesce((c2.rank-c1.rank)::varchar(10),'new') as rank_diff
	from cte_pre_period c1 left join cte_pre_period c2
	on c1.pre_period = c2.period
	and c1.product_name = c2.product_name
)

 

-- 5. 피벗
select rank
	, max(case when period='1997-1' then product_name end) as q9701
	, max(case when period='1997-1' then rank_diff end) as 순위변화
	, max(case when period='1997-2' then product_name end) as q9702
	, max(case when period='1997-2' then rank_diff end) as 순위변화
	, max(case when period='1997-3' then product_name end) as q9703
	, max(case when period='1997-3' then rank_diff end) as 순위변화
	, max(case when period='1997-4' then product_name end) as q9704
	, max(case when period='1997-4' then rank_diff end) as 순위변화
from ces_pre_period_rank_diff
group by rank
having rank<=10
order by 1;

 

※ 분기별로 1등이 있기 때문에 rank로 group by 해준것

 

• sql & 테이블 캡쳐(PPT)
• rank: row_number (중복없이)

---

기말고사 4/21

• 오픈북X..............
• 시험시간: 오후 3시~5시
• 태블로: 30분 (노트북 사용X, 종이로)
  • 문항: 10개 (객관식(6): 고급 쿼리에서 주요하게 사용하는 것, 빈칸 채우기(1), 단답형 (1), 서술형(1): 유형 설명, 함수식 예시를 들어 설명 / 상황부여 = 작업순서 제시) ➡️ 이론
  • 특징 같은거 살펴보기
• sql: 1시간 30분 (실습형 때문에 노트북으로)
  • 문항: 7개 (객관식 (4), 단답형 (1), 실습형 (2): 쿼리 순차적 풀어내기 - 부분점수O, 강의 flow 보기, 결과 업로드)
  • 인재상, 요구사항 보기

---

해커톤

• 해커톤 첫날에 테이블 명세서 첨부, 연구소에서 데이터 사용현황에 대한 설명 O
• ⭐ SQL 활용을 어떻게 풀어내느냐가 중요하다. 
• 시중에 있는 큰 데이터로 연습해보기 (800메가?)
• 중간제출있음 (전날)

---

역대급 머리아팠던 개인과제 😓

주말에 다시 복습해봐야겠다.

---

* 유데미 큐레이션 바로가기 : https://bit.ly/3HRWeVL

* STARTERS 취업 부트캠프 공식 블로그 : https://blog.naver.com/udemy-wjtb

📌 본 후기는 유데미-웅진씽크빅 취업 부트캠프 4기 데이터분석/시각화 학습 일지 리뷰로 작성되었습니다.