[STARTERS 4기 TIL] SQL 심화 #10 - 미니 프로젝트 3일차 발표 및 최종 평가 (230421)

 

📖 오늘 내가 배운 것

 

1. 발표 및 피드백

2. 최종평가대비

 

---

1. 발표 및 피드백

1.1 발표

 

📌 분석 프로세스

📌 우리는?

: Olist에 입점하려는 셀러

 

📌 분석 목적

: Olist 상위 고객들 특성 파악 후 이에 맞춰 성공적인 입점/셀링 전략 수립

 

📌 분석 흐름

1. 어떤 고객을 대상으로 해야 할까?

• RFM 기준
• 최근 활성화 여부, 구매 횟수, 총 구매액 3가지 지표를 통해 고

2. 어떤 품목을 판매해야 할까?

• 매출 TOP 10 카테고리
• 꾸준히 판매되고 있는 카테고리 = 카테고리별 재구매율

3. 어떤 지역에서 판매해야 할까?

• 주문자와 판매자가 거리, 배송기간, 주문건수 상관관계
• 2번 품목이 주로 판매되는 지역 TOP 3

 

📌 피드백

📌 우리 조

- 셀러 입장에서의 고객 분석이라 한다면? RFM 적절한가?

 우리가 가지고 있는 데이터를 봤을 때는 RFM 적절하지 않다~

 셀러라면? 여기에 입점을 하기 위해 고려해야 할 것?

 어떤 상품이 잘 팔릴까? 경쟁자?

- flow는 좋았다

- 목적 부분이 부실하다! 우리가 뭘 할 거다~가 앞에서 명확해야 함!

 

📌 다른 조

- 어떻게 추출해서 SQL 결과 나온 거 테이블 보여주기

- 글씨가 너무 작다!! 크게 확대해서 주요 부분은 가시성 챙겨주기

- TOP DOWN으로 분석 개요 들어가는 거 좋았다!

- 분석 목표 확실히 보인다.

 

----

📌 공동 피드백

1. 회사에 대한 분석이 먼저 들어가면 좋겠다.

2. 브라질의 전자 상거래의 특성을 분석한다거나, 수도가 어디고 어떤 카테고리가 어느 지역에서 경제 수준이 ~하기 때문에 잘 팔린다! 요런 거 들어가기 전에 고려하면 좋음

-> 데이터에 매몰되지 말고 대상에 대해 전체적으로 (2조의 문제 정의 접근 흐름 굳!)

3. 사용한 컬럼 이야기 시 orders 테이블의 ~한 컬럼입니다! 이건 주문 이력 확인하기 위해 사용했다.

4. 글자 많은 건 좋지 않지만, 장표 보고 이해할 수 있고 보면서 들을 수 있게 매치

 

+++ 실무를 하다보면?

SQL 잘 짜고 대시보드 잘 만드는 건 엔지니어의 역할!

이건 기본으로 탑재해야 하는 것들이다~

못하던 사람들도 실무하면 잘해진다.

 

그렇다면 역량의 차이는?

내가 속해있는 분야에 대해 도메인 지식이 얼마나 되냐?

PT/발표/커뮤니케이션 등이 굉장히 중요하다!

 

- 분석 전 과정에 녹여놓은 내 주장을 대상에게 전달할 수 있어야 한다🫠

---

2. 최종평가대비 복습

2.1 Tableau

📌 live connection extract
1. incremental refresh vs full refresh
full refresh는 모든 데이터 행 업데이트, 속도 느림

incremental refresh는 새롭게 추가, 변경되는 행만 업데이트

2. tableau do when you connect to a data source?
-> creates a live connection to the data

📌 정렬, 서식, 계산, 이미지 등 각종 잡다한 방법
1. 참조선 추가할 수 있는 타입
측정값, 계산된 필드 - 수치로 떨어지는 녀석들

2. 라벨로 그룹, 마크로 그룹 차이는?
라벨 - 해당 그룹으로 마크 생성
마크 - 하이라이팅

3. Sort 방법?
데이터 필드, 헤더, 축, 메뉴바 단축 버튼

4. add Total to a view
Analysis Tab
Analytics Pane

5. 마크에 텍스트 레이블을 추가하는 방법?
marks card에 drag-drop
toolbar에 show mark label
analysis in menu bar - show mark label

6. how worksheet visualisation as an image
click on the worksheet in menu bar
right click on worksheet and choosing copy and image

7. row, column bold 방법 (다른 것들에 영향 없이)
Menu bar - format - select Rows or Column
Right Click rows or columns - format - font

7-1. 축 폰트 진하게 만드는 방법?
축 우클릭 - 포맷 - 폰트 어둡게

8. 툴팁 수정하는 방법
marks card에서 tooltip 눌러서
워크시트 - 툴팁
워크시트 포맷팅 들어가면 됨
뷰 마크 우클릭 - sheet - default - tooltip

8-1. 툴팁의 글씨체 수정?
마크 카드의 툴팁
메뉴바 워크시트 - 툴팁
메뉴바 포맷 - - 폰트 - 툴팁

9. 대시보드를 다수의 이미지로 추출하는 방법
메뉴 바의 대시보드에서만 가능

10. 대시보드 사이즈 옵션
범위, 자동, 고정

11. 대시보드에서 시트 결합의 베네핏
필터 함께 적용
비교하며 분석
분석 속도

12.트렌드 라인 종류?
리니,로가,익포,폴리,파워

13. 애니메이션 특징
통합 워크시트에서 한 번에 킬 수 있다.
개별적으로 킬 수 있다.
디폴트로 꺼져 있다.
기본 0.3초
maps, ploygons, density marks in web borwser 작동 불가능
pie, text , axes, header, ofrecasts, tends, referencline
page history trails 도 불가능

📌 blending
1. 일반 계산된 필드, blending 후 계산된 필드의 차이?
혼합에 사용되는 필드는 무조건 집계

블렌딩해서 두 개의 데이터 사용할 때 계산된 필드로 각 데이터 끌어와서 사용해야 한다면?
반드시 각 데이터는 집계된 상태여야 한다.
집계 없이 가져온 데이터 값 그대로 넣으면 raw로 뿌려서 인식하지 못함.

2. 블렌드 후 null 값 이유?
대소문자
혼합 필드 데이터 유형이 일치하지 않는 경우
보조 데이터 원본에 주 데이터 원본 값이 포함되어 있지 않은 경우

📌 True False
1. Measure로 discrete 만들 수 있나? Yes
2. demansion으로 measure 만들 수 있나? Yes
다만, date or geomatric일 때만
3. Measure로 Sets 만들 수 있나? No (집합은 차원으로만 가능)
4. Sets로 Measure 만들 수 있나? No
5. relationship? lod 안 해친다
6. 메타 데이터 매니저는 단순히 보고 체크하는 용
데이터 해석기는 자동으로 수정하는 용 -> data interpreter
7. 조인은 결합 후 집계, 블렌딩은 집계 후 결합
8. 개별적 mark에 대한 similar character? -> clustering
9. join 시 중복을 피하는 방법? - relationship
10. relationship 사용하는 거! 모든 측정값 유지함
11. 뷰, 비주얼에 사용된 데이터를 추출하는 형식?
-> (window)mdb or (mac)csv
12.  bins를 만들기 위해 계산된 필드를 사용 - yes

 

📌 +++
1. LOD

INCLUDE=포함한 차원에 대해 집계, 
EXCLUDE 상위 차원 끌고오기
FIXED 차원의 고정

 

2. YOY, MOM은?

-YoY:전년도 대비 절대적 수치 증감률, YTD:ytodate:누계로 연초에 많이 사용
MoM : 전월 - 지금 월 비교하는 것
MTD : 지금이 2023/3/21이면? 3월 MTD의 경우 3월 1일 ~ 3월 21일까지 합계를 보여주거나 나열해서 보여주거나 총합
- 태블로는 뭐든 집계시켜 버리는 성질이 있다. 잘 못 추출한 데이터는 중복 문제를 야기시킬 수 있는데, 확인 방법은 count, countd 함께 사용해서 체크하는 것.
- 날짜 계산은 DATEDIFF ('기준단위',[시계열],[타겟시계열]) = 1
-> 기준단위가 month라면 타겟시계열
- 날짜 형식은 DATEPARSE()로 바꾸기

 

DATEDIFF ('YEARORMONTH',ORDERDATE, NOW) = [PARAMETER]
/ DATEDIFF ('YEARORMONTH',ORDERDATE, NOW) = [PARAMETER]+1- 1
MTD YTD는?
DATEDIFF ('YEARORMONTH',ORDERDATE, PARAMETER) = 0 AND  DATEDIFF ('YEARORMONTH',ORDERDATE, PARAMETER) > 0
즉 파라미터로 설정한 ORDERDATE는 작거나 같아야 한다.

3. PRIMARY 함수
- LOOKUP() -> MOM 쓸 때 많이 사용! SQL의 LAG/LEAD와 같이 당기고 미는데 -+ 인자로 결정
- TOTAL() VLOD 측정값 TOTAL
- WINDOW_SUM,AVG - 문자 그대로 구간 나눠서 해당 구간의 평균, 합을 모는 거
- RANK! SQL에서 RANK 뽑으면 안 되는 이유? 차원이 바뀌면 RANK도 바뀌기 때문에 의미가 없다

 

4. tmp
- IF에는 항상 집계 씌워줘야 한다.
- ATTR은? 단 하나의 값을 가지면 걔 반환, 아니라면 *반환! 즉, 이질성테스트- 라이브와 추출의 차이?
- demansion을 measure로 변경하는 건 가능하다. 다만, date or geomatric일 때만
- VLOD의 마크를 변경할 수 있는 건 오직 차원!
- measure의 드래그드롭/더블클릭차이는? 2개째 더블클릭부터 measure name이 필터로 들어간다!! 드래그 드롭은 타겟을 사용자가 설정

 

2.2 SQL

📌 자료형 및 연산 기타 등등

1. null 연산 및 합성 결과는 null

NULL 값 처리 : coalesce! (~, ~, ~) null이면 pass해서 null이 아닌 값 반환

 

2. 자료형 변환

cast(변환대상 as 자료형)

변환대상::자료형

 

3. 단일 행 함수 > 날짜 함수

now( ) 현재 날짜와 시각 출력

extract( ‘part’ from 날짜/시간타입)	날짜/시간 데이터에서 part를 반환
date_part(‘part’, 날짜/시간타입)	날짜/시간 데이터에서 part를 반환
date_trunc(‘part’, 날짜/시간타입)	날짜/시간 데이터에서 part 이하 초기화하여 반환
to_char(날짜/시간데이터, ‘part’)	날짜/시간 데이터에서 part를 문자열로 반환

to_char : 보통 year, month, day로 딱 떨어지게, 혹은 YYYY-mm 등의 형태를 정의해서 뽑아야 할 때 많이 사용

date_trunc : timestamp 형으로 결과가 반환되기 때문에 interval이나 -+ ‘1 days’ 등 날짜 연산이 완전 가능하다!

날짜가 to_char로 year/month 등등으로 떨어져 있다면?

|| 으로 문자열 합칠 수 있고

between 써서 year and year

‘yearmm’ and ‘yearmm’ 등등으로 비교 가능하다!

 

4. 쿼리의 실행 순서 - SELECT는 집계에 조건 다 끝나고 5번째

5 SELECT

1 FROM & JOIN

2 WHERE

3 GROUP BY

4 HAVING

6 ORDER BY

7 LIMIT

 

5. Pivot Table 사용 시 Case When에 MAX 걸면 한 개씩 뽑을 수 있음.

주로 Rank에 이어서 씀.

각 카테고리 별 매출 1,2,3 순위라면?

max(case when rank = ‘1’ then ~ end)

하면 rank가 1인 어차피 한 놈만 나옴

 

6. 등급화 시 누계 사용한다면 ≤ 등호 시작!

→ 작은 조건부터 큰 조건으로

 

7. 차집합은? except로 구한다

 

8. lower 사용해서 새로운 컬럼 추가하는 방법

with cte_customers as (
select 	 -- customers
	  c.customer_id
	 , c.company_name
	 , c.contact_title
	 , c.country
	 , c.city
	 -- order
	 , o.order_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
	 , date_part('dow' ,order_date) as dow
	 -- order_details
	 , od.product_id 
	 , od.unit_price 
 	 , od.quantity 
 	 , od.discount 
	 , round( od.unit_price * od.quantity * (1-od.discount) ) as amount
	 -- categories
	 , c2.category_id
	 , c2.category_name
	 --, p.product_id as 제품ID order_details와 중복!
 	 , p.product_name
from customers c
	 LEFT join orders o ON o.customer_id = c.customer_id
	 left join order_details od ON od.order_id = o.order_id
	 LEFT join products p on p.product_id = od.product_id
	 LEFT join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
	 , count(distinct customer_id) as 고객수
	 , sum(amount) as 매출액
	 , count(distinct order_id) as 주문건수 
from cte_customers
group by country
order by 3 desc
)
-- 상관계수 뽑기
, cte_country_customercnt_amount_ordercnt_corr as (
select corr(고객수, 매출액) as "고객수_매출액"
	 , corr(매출액, 주문건수) as "매출액_주문건수"
	 , corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
)
-- 지역 추가하기
, cte_country_group as (
select *
	 , case
	 	when lower(국가) in ('usa','canada','mexico') then 'NorthAmerica'
	 	when lower(국가) in ('brazil','venezuela','argentina') then 'SouthAmerica'
	 	else 'Europe'
	 end as 지역
from cte_country_customercnt_amount_ordercnt

 

📌 Window 함수

1. 윈도우 함수에서 partitiona by 의미는 그룹을 나눠서 그 안에서만 order by 대로 계산하는 거

 

2. ntile시 준 인자에 따라 번호를 매겨주는데 desc 하면 큰 순서대로 1번!

recency 구하는 경우 date_diff가 큰 순서대로 1 매겨짐.

따라서 값이 작아질수록 번호가 커지는 것. Score로 활용 가능

 

3. Rank 종류

ROW_NUMBER : 우선순위

 

4. lag는 걱정할 게 없는 게 12개월 전이다?

그럼 rows between 12 preceding and current row

 

5. rows between

rows between <start> and <end>

CURRENT ROW : 현재 행

n PRECEDING : n행 앞

n FOLLOWING : n행 뒤

UNBOUNDED PRECEDING : 이전 행 전부

UNBOUNDED FOLLOWING : 이후 행 전부

 

주의점 : 함수 바로 뒤에 인자 주는 경우, 안 주는 경우 있음

rank 녀석들 같은 경우에는 order by에 기준 주는 것으로 해결

집계 함수 같은 경우에는 무조건 인자 줘서 얘네로 집계를 하겠다!

윈도우 함수는 새로운 창을 열어 계산을 해주는 것이기 때문에 기존 정렬 수준의 영향을 받지 않는다.

그래서 정렬을 직접 정의하고 들어가는데 그게 order by, order by 조건으로 정렬 후 함수 적용한다.

 

📌 주요 분석 기법

1. ~별 몇 위
~별에 해당하는 column을 partition으로 rank 구해주고 pivot

2. 이동평균
lag 사용, rows between n preceding and current row
여기서 n=4면 현재 행까지 총 5칸 -> 금일 포함 5일 이평선

3. 이전 ~ 대비 순위 변화
요건 정렬을 ~인 애, 로 해야 함.

4. lag가 () 안에 ,int 인자를 주는 경우가 있는데?
이건 해당 칸 값을 당겨오는 거!
작대비 구할 때 사용
그럼 n preceding이랑 무슨 차이? 값 갯수 차이

5. 시간 관련 함수
to_char로 형식 바꿀 수 있다 -> str로 반환
date_trunc -> timestamp 반환, 지정한 단위의 시계열만 뽑히고 나머지는 default로

6. 백분위수, 최빈값
percentile_cont

----
- ABC (구성비누계 70 아래 A, 90 아래 B)
1. 매출액이 많은 순으로 정리
2. 총매출을 100%로 하여 고객별 백분비 산출
3. 그 누적 구성비율을 상위의 고객부터 순서대로 누적해 간다.
4. 그래프의 세로에 매출액 점유비의 누적치를, 가로축에 고객을 기입하고 고객별 누적구성비를 표시해 간다
5. 세로축의 70%와 90%의 누적치 해당점에서 가로선을 긋고, 그래프의 선과의 교차점에서 수직선을 긋는다

- Z차트 (매출, 누적 매출, 이동연계(10 preceding 줘야 함))
1. 제품별 연-월에 따른 월별 매출
2. 매출누계
나는 limit으로 했는데 where 기준월매출 != 0 도 쓸 수 있다!

요기서 연/월이 깔끔하게 안 떨어진다면?
1. 월별 매출 구하기
2. 이동연계 구하기
3. 매출누계 구하고 where 연월 between '1997-06' and '1998-04'

- DECIL (고객 등급 나누고 그룹화해서 관리하는 기법)
1. 고객의 총 매출액 기준 정렬
2. 상위부터 10%씩 나누어 10개의 그룹 할당! 10등분 (ntile? 사용하면 된다고 하심)
3. decil 별 매출합계 (1등급은 ~, 2등급은 ~)
4. decil 별 구성비 (전체 매출 중 구성비)
5. decil 별 구성비 누계 (구성비의 누계!)

- RFM
1. recency 구하기 위한 datediff 구하기
2. 고객 별로 각 지표 값 추출
3. 지표에 스코어 부여
4. 스코어 종합


------------------ 과제 ------------------
- 전체 판매수량 TOP10
1 먼저 판매수량 기준 RANK로 정렬!
2 RANK 제품ID 제품명 총판매수량 퐁 매출액 카테고리명
3 여기서 LIMIT 10

- 국가별 판매수량 TOP5
1. 필요 컬럼 선정 + 판매수량, 매출액
2. 국가 별 판매수량 랭크 구하기
3. 국가 별 판매수량 top 5
4. PIVOT 및 국가별 총 매출액 순 내림차순 정렬
5. 국가별 판매량 TOP 5 제품 - 최종정리

- 1997년 분기별 판매수량 TOP10
1. 1997년의 분기, 판매수량 등 필요 컬럼 가져오기
2. 각 분기 partition 놓고 rank 구하기
3. 각 분기 별 rank top 10 조건 걸어주기
4. pivot - 최종정리

- 1997년 분기별 판매수량 TOP10 - 순위변화
1. 1997년의 분기, 판매수량 등 필요 컬럼 가져오기
2. 랭크 구하기 (연 분기별이기 때문에 partition 연, 분기)
3. LAG 사용해서 이전 분기와 연산/비교하기

- 월별 재구매율
1. 고객 구매월 중복되지 않게 불러오기
2. 다음 월과 매칭되도록 self join (a.date+1=b.date)
3. 월별 구매자 수 집계하여 재구매율 계산

- 제품의 연도별 재구매율 (동일한 고객이 재구매)
1. 고객별 연도별 제품 구매 기록을 중복 없이 불러온다!
2. 제품에 대한 동일한 고객의 재구매율이기 때문에 
self join 시 연도+1과 고객, 제품이 같은 경우를 조건으로!
3. product_name 별 연도 별 구매자, 재구매자, 재구매율 구하기

 

---

💪🏻 좋았던 점, 앞으로 개선해야 할 점 (추가로 배워야 할 점)

 

📌 미니 프로젝트 피드백 시간에서 분석 시 보완해야 할 점들에 대해 배울 수 있었다.

 

특히 핵심이라 생각되는 부분은 데이터만 보고 분석을 진행하면 그만큼 사고가 편협해질 수 있기에

먼저 해당 데이터 관련 기업, 도메인, 시장에 대한 조사를 수행해야 한다는 것!

안 그래도 이번 분석을 진행하며 창의적인 목적 정의/계획 수립 방안에 대한 고민이 존재했는데

적절한 피드백을 받을 수 있어 행복했다🦦🥎

 

앞으로 분석 진행 시 이러한 부분을 꼭 고려하며 진행해봐야겠다💪🏻

 

#유데미, #유데미코리아, #유데미부트캠프, #취업부트캠프, #부트캠프후기, #스타터스부트캠프, #데이터시각화 #데이터분석 #태블로