[ 유데미 스타터스 부트캠프 4기 ] 데이터분석/시각화(태블로)_10주차 학습 일지

🌻 10주차 4L  회고 🌻

4L  회고형
1) Liked ( 이번주 수업에서 좋았던 점은? )
2) Learned ( 이번주에 새롭게 배운 점은? )
3) Lacked ( 배운 것에 관해서 내가 부족했던 부분은? )
4) Longed ( 앞으로 뭘 더 하면 좋을까? )

 

📍 Liked ( 이번주 수업에서 좋았던 점은? )

✅ CTE 테이블 사용법을 배운 것

이번주 SQL 수업에서 WITH절을 활용한 CTE 테이블을 배웠다. 서브쿼리를 사용하는 것이 어렵고 익숙하지 않아 복잡한 쿼리를 작성하는데 부담감이 있었는데, CTE 테이블을 통해 단계별로 필요한 컬럼을 추출하고 작성하는 법을 배울 수 있어 좋았다. 어떠한 심화 함수를 배우더라도 CTE 테이블을 활용해 차근차근 쿼리를 작성해 나가는 법을 배웠고, 예제와 과제를 통해 연습하며 복잡하고 긴 쿼리를 작성하는 것에 대한 두려움을 조금은 없앤 것 같아 만족스럽다!! 😊

 

✅ SQL로 무엇을 할 수 있는지 알게된 것

SQL은 학교 수업에서도 배웠고 SQLD 자격증도 취득했기에 어느 정도 기본 문법은 알고 있었다. Data Analyst에게 SQL 활용 역량은 필수이기에 어딜 가서든 SQL을 사용하여 분석에 필요한 데이터를 추출하고 정제된 데이터셋을 만들 수 있다! 라고 말해왔지만 SQL로 무언가를 해본 적은 없어서 정확히 어떻게 활용해야 하는지 몰랐다. 이번 SQL 수업을 통해 고급 쿼리를 배워서 매출 지표를 분석하고, 결과 테이블을 Tableau에 연결하여 시각화까지 하며 SQL로는 무엇을 해야 하는지, 그리고 Data Analyst의 데이터 분석 프로세스는 어떤 식으로 이루어지는지 경험할 수 있어 좋았다. 👍

 

📍 Lacked ( 배운 것에 관해서 내가 부족했던 부분은? )

✅ 응용 쿼리 작성 + 느린 속도

개인 과제와 수업 중간중간에 응용 쿼리를 작성하는 실습을 하는데, 차근차근 잘 따라 하고 있는 것 같지만 속도가 조금 느린 것 같다. 또 가끔은 너무 예제에 함몰되어 함수의 본래 역할을 잊어 헤매기도 했다. 해커톤에서 SQL의 역할이 매우 중요하기 때문에 속도와 정확성을 모두 챙겨갈 수 있도록 연습을 많이 해야겠다!! 

 

📍 Learned ( 이번주에 새롭게 배운 점은? )

1) 테이블 정의서 작성하기

 

 

2) 구매 지표 추출 (단일 행 함수, 다중 행 함수 활용)

 

3) CTE 테이블 작성하기

with 
cte_order_details as (
select o.order_id, o.customer_id, o.order_date
		, to_char(o.order_date, 'YYYY') as year
		, to_char(o.order_date, 'mm') as month
		, to_char(o.order_date, 'dd') as day
		, to_char(o.order_date, 'q') as quarter
		, od.product_id, od.unit_price, od.quantity, od.discount
		, od.unit_price * od.quantity * (1-od.discount) as amount
from orders o, order_details od
where o.order_id = od.order_id
)

 

4) 윈도우 함수

 

4)-1 집계

 

전체 집계	집계함수 OVER()
그룹 집계	집계함수 OVER(PARTITION BY <컬럼이름>)
프레임지정	집계함수 OVER(윈도우프레임지정)

 

▶ 누적합계

with
cte_order_details as (
-- 생략
)
select year, month, day
    -- 레코드 전체 합계. 비율을 계산할 때 사용 (전체 대비 몇 %)
	, sum(sales) over()
    -- 년도 단위로 누적 합계
	, sum(sales) over(order by year)
    -- 년도, 월, 일 단위로 누적 합계
	, sum(sales) over(order by year, month, day)
    -- 년도가 바뀌면 새로 누적
    , sum(sales) over(partition by year order by year, month)
from cte_order_details;

 

4)-2 행 순서 함수

 

집계함수	집계함수 (count, sum 등)
순위함수	RANK	중복 순위 존재 시 건너뜀 (1,1,3,4,5,5,7,...)
	DENSE_RANK	중복 순위 존재 시 건너뛰지 않음 (1,1,2,3,4,4,5,...)
	ROW_NUMBER	동일 순위 존재하여도 연속번호 부여 (1,2,3,4,5,6,7,...)
행 순서 함수	FIRST_VALUE	파티션 별 윈도우에서 가장 먼저 나오는 값 (min)
	LAST_VALUE	파티션 별 윈도우에서 가장 늦게 나오는 값 (max)
	LAG	파티션 별 윈도우에서 이전 행 값
	LEAD	파티션 별 윈도우에서 다음 행 값
그룹 내 비율 함수	RATIO_TO_REPORT
	PERCENT_RANK	백분위 수
	CUME_DIST
	NTILE	구간 나누기

 

▶ 이전 행, 다음 행 추출

with
cte_order_details as (
-- 생략
)
, cte_pre_amount as(
select year, month, day, sum(amount) as amount
from cte_order_details
group by 1,2,3
order by 1,2,3
)
select * 
	, lag(amount) over(order by year, month, day) as 전일매출
	, lag(amount, 3) over(order by year, month, day) as 삼일전매출
	, lead(amount) over(order by year, month, day) as 익일매출
from cte_pre_amount;

 

4)-3 윈도우 프레임 지정

 

ROWS BETWEEN START AND END

CURRENT ROW	현재 행
n PRECEDING	n행 앞
n FOLLOWING	n행 뒤
UNBOUNDED PRECEDING	이전 행 전부
UNBOUNDED FOLLOWING	이후 행 전부

 

▶ 5일 이동평균

with
cte_order_details as (
-- 생략
)
-- 일별 매출액
, cte_sales as (
select year, month, day, sum(sales) as 매출액
from cte_order_details
group by 1,2,3
order by 1,2,3
)
select *
	, avg(매출액) over(order by year, month, day rows between 4 preceding and current row) as "5일이동평균1" -- 앞 4개의 행은 이동평균이 아님
	, count(매출액) over(order by year, month, day rows between 4 preceding and current row) as cnt
	, case 
		when count(매출액) over(order by year, month, day rows between 4 preceding and current row) = 5 then
		avg(매출액) over(order by year, month, day rows between 4 preceding and current row)
	end as "5일이동평균2"
from cte_sales;

 

5) ABC 분석

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

 

▶ 제품 ABC 분석 지표 구하기

-- 1) 제품 별 매출액
, cte_amount as (
 select product_id, product_name, sum(매출액) 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
order by 구성비 desc;

 

6) 피벗하기

 

▶ 카테고리 별 매출 상위 3개 제품

-- 4) 피벗하기
select category_name
	, max(case when rank=1 then product_name end) as "1위"
	, max(case when rank=2 then product_name end) as "2위"
	, max(case when rank=3 then product_name end) as "3위"
from cte_top3
group by 1;

 

▶ 카테고리 별 제품 매출순위

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 DairyProducts
, coalesce(max(case when category_name='Grains/Cereals' then product_name end), '') as GrainsCereals
, coalesce(max(case when category_name='Meat/Poultry' then product_name end), '') as MeatPoultry
, 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 매출순위
order by 매출순위;

 

7) Z차트

✔ 월별매출(단기적 추이)

✔ 매출누계(단기적 추이)

: 해당 월 매출에 이전 월까지 매출 누계

✔ 이동년계(장기적 추이)

: 해당 월의 매출에 과거 11개월의 매출을 합한 값 (최근 1년차 누적 합계)

→ 그래프에 표시되지 않은 과거 1년동안의 매출 추이도 읽을 수 있음

 

▶ 11개월의 Z차트 그리기 (1997-06 ~ 1998-04)

-- 1) 월매출 구하기
, cte_amount as (
select year, month, category_id, category_name, sum(sales) as 월매출
from cte_products_sale
group by 1,2,3,4
)
-- 2) 기준월매출 구하기
, cte_base_amount as (
select *
	, case 
	when year||month between '199706' and '199804' then 월매출
	else 0
	end as 기준월매출
from cte_amount
order by 1,2
)
-- 3) 매출 누계
, cte_agg as (
select *, sum(기준월매출) over(order by year, month) as 매출누계
from cte_base_amount
)
-- 4) 전월 10개월 이동연계
, cte_pre10_sum as (
select *
	, sum(월매출) over(order by year, month rows between 10 preceding and current row) as 이동합계
from cte_agg
)
select category_id, category_name, year||month as 연일, 월매출, 매출누계, 이동합계
from cte_pre10_sum
where 기준월매출 != 0

 

8) 그룹 함수

 

8)-1 GROUPING SETS

 

▶ 지표들을 한번에 추출하기

-- 1) 카테고리, 제품명, 공급업체명, 공급국가명, 공급국가도시명으로 그룹핑하여 집계
, cte_grouping_sets as (
select category_name, product_name, company_name, country, city, sum(sales) 매출액
from cte_products_sale
group by grouping sets (category_name, product_name, company_name, country, city, (country, city), ())
)
-- 2) 지정한 컬럼의 각 그룹별 집계
-- 카테고리별 매출액
, cte_category_amount as (
select category_name, 매출액
from cte_grouping_sets
where category_name is not null
)
select * from cte_category_amount;

▲ cte_category_amount

 

8)-2 ROLLUP

 

▶ 카테고리, 제품별 매출액 소계

, cte_category_product_rollup as (
select category_name, product_name, sum(sales) as 매출액
from cte_products_sale
group by rollup (category_name, product_name)
order by 1,2
)

 

 

📍 Longed ( 앞으로 뭘 더 하면 좋을까? ) 

✅ 태블로 활용하기

SQL 수업이지만 강사님께서 과제를 할 때 시각화까지 해보라고 하셔서 수업 시간에도 Tableau를 사용할 수 있었다. 그러나 우리 팀은 SQL 활용에 중점을 두었기 때문에 다른 팀에 비해 시각화를 많이 시도하지 않았다. 역할을 나누기보다는 하나의 분석 주제도 모두 함께 시도를 하며 다음 주제로 넘어가는 방식으로 진행했는데, 매우 만족스러웠다. 하지만 그만큼 부족한 부분은 계속 채워나갈 것이다!!

이번 주 내로 Tableau 자격증을 취득하는 것이 목표였는데 성공했다!! 평일에 또 공부하느라 힘들었지만 뿌듯하다. 🎉

---

🌻 10주차 TIL 🌻

✍️ 45일차 - 테이블 정의서, 테이블 상세 파악

[ 유데미 스타터스 ] 45일차 TIL 프로젝트 기반 SQL 실전 트레이닝 - 테이블 정의서, 테이블 상세 파

✍️ 46일차 - 단일 행 함수, 다중 행 함수

[ 유데미 스타터스 ] 46일차 TIL 프로젝트 기반 SQL 실전 트레이닝 - 단일 행 함수, 다중 행 함수

✍️ 47일차 - CTE테이블, 윈도우 함수

[ 유데미 스타터스 ] 47일차 TIL 프로젝트 기반 SQL 실전 트레이닝 - CTE테이블, 윈도우 함수

✍️ 48일차 - 제품/카테고리 매출 지표 분석, ABC 분석, 피벗 테이블

[ 유데미 스타터스 ] 48일차 TIL 프로젝트 기반 SQL 실전 트레이닝 - 제품/카테고리 매출 지표 분석,

✍️ 49일차 - Z차트, 그룹 함수(GROUPING SETS, ROLLUP, CUBE)

[ 유데미 스타터스 ] 49일차 TIL 프로젝트 기반 SQL 실전 트레이닝 - Z차트, 그룹 함수(GROUPING SETS, ROLL

 

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

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

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