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

[출처 : Udemy - STARTERS 블로그 학습일지 안내]

 

안녕하세요! 오늘은 유데미 스타터스 부트캠프 10주차 학습일지입니다! 🐸

 

이번주는 SQL 오프라인 강의를 시작하며 다양한 데이터 값을 SQL 쿼리를 통해 산출하는 실습을 진행하였습니다. 

 

그럼 10주차에는 어떤 내용을 학습했는지, 스타터스 부트캠프 10주차 학습일지 시작해보겠습니다 !💨

 

---

❤️‍🔥 LIKED

SQL을 이용한 통계 분석 방법

ABC 분석

통계적 방법에 의해 관리대상을 A, B, C 그룹으로 나누고,

먼저 A그룹을 최중점 관리대상으로 선정하여 관리노력을 집중함으로써 관리효과를 높이려는 분석방법.

ABC분석

 

📌 ABC 테스트 📌

with cte_products_sale as(
select o.order_id , o.customer_id , o.order_date
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id , od.unit_price as 판매단가 , od.quantity , od.discount , (od.unit_price * od.quantity * (1-od.discount)) as amount
	 , c.category_id , c.category_name 
	 , p.product_name , p.unit_price as 마스터단가 , 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 od.product_id = p.product_id 
  and p.category_id = c.category_id 
  and p.supplier_id = s.supplier_id 
)
✅ 1. 제품별 매출액
, cte_amount as (
select product_id , product_name
	 , 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. 등급
, cte_class as (
select *
	 , case 
	 	when 구성비누계 <= 70 then 'A'
	 	when 구성비누계 <= 90 then 'B'
	 	else 'C'
	 end as 등급
from cte_ratio_agg
order by 구성비 desc
)
✅ 최종 테이블

 

z차트

 

📌 1️⃣ 1997년 6월 ~ 1998년 4월 Z차트 📌

-- order_details : unit_price, quantity, discount, 월별매출, 매출누계, 이동연계
-- orders : order_date, year, month

with cte_z_sales as (
select to_char(o.order_date, 'yyyy-mm') order_ym
	 , sum(od.quantity * od.unit_price * (1-od.discount)) sales
from orders o, order_details od
where o.order_id = od.order_id
group by order_ym
order by 1
)
✅ 이동연계 산출
, cte_z_moving_sum as (
select *
	, round(sum(sales) over(order by order_ym rows between 10 preceding and current row)::NUMERIC, 2) as 이동연계
from cte_z_sales
)
✅ 매출누계 산출
, cte_z_chart as (
select order_ym
	 , sales as 월별매출
	 , sum(sales) over(ORDER BY order_ym) as 매출누계
	 , 이동연계
from cte_z_moving_sum
where order_ym >= '1997-06' and order_ym <= '1998-04'
)
select * from cte_z_chart;

 

 

 

 

 

---

 

🌟 LEAREND

1. SQL 기본 개념

SQL 데이터 분석

① 분석 목적 설정
② 지표 설정 및 분석 계획
③ 데이터 추출/정제/가공/분석 -> BI 도구, Python, R, 스프레드시트 
④ 리포트 작성 및 발표

 

테이블 관계

ERD 상에 나타난 각 테이블 간의 관계를 표시하는 방법은 아래와 같다.

 

예시

1번	2번
· 하나의 상품에 0~1개의 카테고리 존재 · 하나의 카테고리에 0~N개의 상품이 존재	· 하나의 주문에 0~N개의 주문내역 존재 · 하나의 주문 내역에 1개의 주문 존재

 

기본 데이터 타입

 

2. 단일행 함수

칼럼 연산자 - 합성연산자

✅ concat 함수를 사용하여 customers 테이블에서 주소를 하나의 문자열로 만들기
  address, city, region, postal_code, country 합성
  공백(' ')으로 각 컬럼 구분
  
select concat(address, ' ', city, ' ', region, ' ', postal_code, ' ', country) 
from customers c ;

 

✅ 합성연산자('||') 사용하여 customers 테이블에서 주소를 하나의 문자열로 만들기
  address, city, region, postal_code, country 합성
  공백(' ')으로 각 컬럼 구분
  
select address || ' ' || city || ' ' || region || ' ' || postal_code ||' ' || country 
from customers c ;

 

✅ coalesce 함수 : 인자로 주어진 칼럼들 중 null이 아닌 첫번째 값을 반환하는 함수

select homepage, fax, phone, coalesce(homepage, fax, phone) as coalesce
from suppliers s ;

 

✅ nullif 함수 : 특정 값을 NULL 처리하기

select *, nullif(category_name, 'Beverages')
from categories c;

 

날짜/시간형 데이터 다루기

데이터 타입	크기	설명	기본 형태
timestamp	8byte	날짜와 시간	YYYY-MM-DD HH:MI:SS.MS [TIMEZONE]
date	4byte	날짜(시간 미포함)	YYYY-MM-DD
time	8byte	시간(날짜 미포함)	HH:MI:SS.MS [TIMEZONE]
interval	16byte	날짜 차이	1 days, 1 mon, 100 years

날짜 함수 (단일행)	반환 값	반환 값 자료형
now()	현재 날짜와 시각 출력
extract('part' from 날짜/시간)	날짜/시간 데이터에서 part를 반환
date_part('part' , 날짜/시간)	날짜/시간 데이터에서 part를 반환	정수형
date_trunc('part' , 날짜/시간)	날짜/시간 데이터에서 part 이하 초기화하여 반환	타임스탬프형
to_char(날짜/시간 , 'part')	날짜/시간 데이터에서 part를 문자열로 반환	문자형

✅ 자료형 변환
 1) cast(변환대상 as 자료형)
 2) 변환대상 :: 자료형

 

3. 복잡한 데이터셋을 다루기 위한 테이블 생성법

SQL에서 복잡한 데이터셋을 다룰 경우

복잡한 데이터셋의 문제점
가독성↓  재사용성↓  유지보수성↓  실수↑

 

임시테이블 temporary table

📌 임시 테이블 tmp_order_details 생성 📌

✅ 주문번호, 고객번호, 주문일, 연, 월, 일, 제품번호, 제품단가, 수량, 할인율, 매출액

create temporary table tmp_order_details as
select o.order_id , customer_id , order_date 
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(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 ;

🖍 세션이 유지되는 동안 일반 테이블처럼 사용(alter, drop 가능)

📌 임시 테이블 tmp_order_details 이용 📌

✅ 월별 매출액, 주문건수 추출, 주문자 수, 건당 평균 주문액, 고객당 평균 주문액

select year , month , sum(amount) as 매출액, count(distinct order_id) as 주문건수
	 , count(distinct customer_id) as 주문자수
	 , sum(amount)/count(distinct order_id) as 건당평균주문액
	 , sum(amount)/count(distinct customer_id) as 고객당평균주문액
from tmp_order_details
group by 1, 2;

 

공통 테이블 표현식 CTE(Common Table Expression)

📌 공통 테이블 cte_order_details 생성 📌

✅ 주문번호, 고객번호, 주문일, 연, 월, 일, 분기, 제품번호, 제품단가, 수량, 할인율, 매출액

with 
cte_order_details as(
select o.order_id , customer_id , order_date 
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(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
)
select * from cte_order_details;

📌 공통 테이블 cte_order_details을 이용하여 cte_order_info 테이블 생성 📌

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,'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.quantity , od.discount
	, od.unit_price*od.quantity*(1-od.discount) as total
from orders o , order_details od 
where o.order_id = od.order_id
)
-- 월별 매출액, 주문건수, 주문자수, 건당평균주문액, 고객당평균주문액
, cte_order_info as (
select year, month
	, sum(total) as 매출액
	, count(distinct order_id) as 주문건수
	, count(distinct customer_id) as 주문자수
	, sum(total)/count(distinct order_id) as 건당평균주문액
	, sum(total)/count(distinct customer_id) as 고객당평균주문액  
from cte_order_details
group by year, month
)
select sum(매출액) as 매출액
	 , sum(주문건수) as 주문건수
from cte_order_info;

 

4. 윈도우 함수

 윈도우 함수

 

 

 

 

 

 

5. 그룹 함수

GROUPING SETS

📌 1️⃣ 제품 속성 별 grouping sets 📌

-- order_details : unit_price, quantity, discount, amount
-- products : product_name, unit_price, discontinued
-- categories : category_id, category_name
-- suppliers : supplier_id, company_name, country, city

with cte_products_sales as (
select c.category_id , c.category_name 
	 , od.quantity , od.unit_price , od.discount 
	 , od.quantity * od.unit_price * (1-od.discount) as amount
	 , p.product_name , p.unit_price as master_unit_price, p.discontinued 
	 , s.supplier_id , s.company_name , s.country , s.city 
from order_details od, products p , categories c , suppliers s 
where d.product_id = p.product_id 
  and p.category_id = c.category_id 
  and p.supplier_id = s.supplier_id 
)
✅ 제품 속성 별 매출액 : grouping sets 
, cte_grouping_sets as (
select category_name, product_name, company_name, country, city, sum(amount) as sales
from cte_products_sales
group by grouping sets(category_name, product_name, company_name, country, city, (country, city), ())
)
✅ 공급 국가 별 매출액 : cte_country_amount
, cte_country_amount as (
select country, sales
from cte_grouping_sets
where country is not null and city is null
)
✅ (공급 국가, 도시) 별 매출액 : cte_country_city_amount
, cte_country_city_amount as (
select country, city, sales
from cte_grouping_sets
where country is not null and city is not null
order by country, city
)
✅ 전체 매출액 : cte_all_amount
, cte_all_amount as (
select 'all' as 전체 , sales
from cte_grouping_sets
order by sales desc
limit 1
)
-- 공급 국가 별 매출액 : cte_country_amount
-- (공급 국가, 도시) 별 매출액 : cte_country_city_amount
-- 전체 매출액 : cte_all_amount
select * from cte_all_amount;

 

 

4️⃣ 공급 국가 별 매출액 : cte_country_amount   /   5️⃣ (공급 국가, 도시) 별 매출액 : cte_country_city_amount 

5️⃣ 전체 매출액 : cte_all_amount

 

 

ROLLUP

📌 (카테고리, 제품), 시기 별 매출액 rollup 📌

-- order_details : unit_price, quantity, discount, amount
-- products : product_name, unit_price, discontinued
-- categories : category_id, category_name
-- suppliers : supplier_id, company_name, country, city

with cte_products_sales as (
select c.category_name ,p.product_name
	 , 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.unit_price * od.quantity *(1-discount) as amount
, od.quantity , od.order_id
, s.company_name , s.country , s.city
from orders o , order_details od , products p , categories c, suppliers s
where o.order_id = od.order_id
and od.product_id = p.product_id
and p.category_id = c.category_id
and s.supplier_id = p.supplier_id 
)
✅ 카테고리, 제품 별 매출액 소계 : cte_category_product_rollup
, cte_category_product_rollup as (
select category_name, product_name, sum(amount) as sales
from cte_products_sales
group by rollup(category_name, product_name)
order by 1, 2
)
✅ 시기 별 매출액 소계 : cte_period_rollup
, cte_period_rollup as (
select year, quarter, month, sum(amount) as sales
from cte_products_sales
group by rollup(year, quarter, month)
order by 1, 2, 3
)
-- 카테고리, 제품 별 매출액 소계 : cte_category_product_rollup
-- 시기 별 매출액 소계 : cte_period_rollup
select * from cte_period_cube;

1️⃣ 카테고리, 제품 별 매출액 소계 : cte_category_product_rollup   /   2️⃣ 시기 별 매출액 소계 : cte_period_rollup

 

CUBE

📌 (카테고리, 제품), 시기 별 매출액 cube 📌

-- order_details : unit_price, quantity, discount, amount
-- products : product_name, unit_price, discontinued
-- categories : category_id, category_name
-- suppliers : supplier_id, company_name, country, city

with cte_products_sales as (
select c.category_name ,p.product_name
	 , 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.unit_price * od.quantity *(1-discount) as amount
, od.quantity , od.order_id
, s.company_name , s.country , s.city
from orders o , order_details od , products p , categories c, suppliers s
where o.order_id = od.order_id
and od.product_id = p.product_id
and p.category_id = c.category_id
and s.supplier_id = p.supplier_id 
)
✅ 시기 별 매출액 집계 : cte_period_cube
, cte_period_cube as (
select year, quarter, month, sum(amount) as sales
from cte_products_sales
group by cube(year, quarter, month)
order by 1, 2, 3
)
-- 시기 별 매출액 집계 : cte_period_cube
select * from cte_period_cube;

1️⃣ 시기 별 매출액 집계 : cte_period_cube

 

---

 

💦 LACKED

4️⃣ 1997년 분기별 판매수량 순위변화 cte_quarter_sales
-- products : product_id, product_name,
-- order_details : quantity
-- orders : order_date, 연도, 분

with cte_quarter_sales as (
select p.product_id , p.product_name 
	 , od.quantity , od.unit_price , od.discount , od.quantity * od.unit_price * (1-od.discount) as amount
	 , to_char(order_date, 'YYYY') as year
	 , to_char(order_date, 'q') as quarter
from order_details od , products p , orders o 
where od.product_id = p.product_id 
  and od.order_id = o.order_id 
)
-- 1. 분기 넘버링
, cte_quarter_no as (
select *
	, case 
	when year = '1996' and quarter = '4' then 1
	when year = '1997' and quarter = '1' then 2  
	when year = '1997' and quarter = '2' then 3 
	when year = '1997' and quarter = '3' then 4 
	when year = '1997' and quarter = '4' then 5 end as no
from cte_quarter_sales
)
-- 2. 필요 분기만 추출
, cte_need_year as (
select *
from cte_quarter_no
where not no isnull 
)
-- 3. 그루핑
, cte_group as (
select no, year, quarter, product_name
	 , sum(quantity) as total_quantity
	 , sum(amount) as total_sales
from cte_need_year
group by no, year, quarter, product_name
order by no, year, quarter, product_name
)
-- 4. 순위
, cte_rank as (
select rank () over (partition by no order by total_quantity desc, total_sales desc), *
from cte_group)
-- 5. 순위변화 산출
, cte_lag as (
select *, lag(rank,1) over(partition by product_name order by no)-rank as "순위변화"
from cte_rank)
-- 6. 피벗 진행
, cte_pivot as (
select rank
	 , max(case when no = 2 then product_name end) as "1997년 1분기"
	 , max(case when no = 2 then 순위변화 end) as "순위변화"
	 , max(case when no = 3 then product_name end) as "1997년 2분기"
	 , max(case when no = 3 then 순위변화 end) as "순위변화"
	 , max(case when no = 4 then product_name end) as "1997년 3분기"
	 , max(case when no = 4 then 순위변화 end) as "순위변화"
	 , max(case when no = 5 then product_name end) as "1997년 4분기"
	 , max(case when no = 5 then 순위변화 end) as "순위변화"
from cte_lag
group by rank
order by rank
limit 10
)
select * from cte_pivot;

 🤷‍♀️ 어려웠던 점  : 각 분기 별 순위 변화 구하기

💡해결방법  : lag 함수를 쓰며 각 상품별로 순위가 구분되도록 partition by product_name를 사용

 

---

그럼 이상으로 10주차 학습일지를 마치겠습니다 !

11주차도 열심히 달려보겠습니다 !!!💨

 

 

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

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

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