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

 

📍 Z차트

 

➡️ 시간의 흐름에 따라 매출의 변화 확인하기

• 월별매출: 1자이면 월별매출이 다 똑같은 것
• 매출누계: 해당 월 매출에 이전 월까지 매출 누계를 합한 값
• 이동합계(년): 해당 월의 매출에 과거 11개월의 매출을 합한 값 (최근 1년치 누적 합계 = 12개월)
  - 그래프에 표시되지 않은 과거 1년동안의 매출 추이도 읽을 수 있음

---

 

• A: 추가성장을 위한 액션 필요
• C: 단기, 장기 모두 감소세 / 성장전환을 위한 액션 필요

➡️ 추세파악을 했다면, 추세에 대한 원인을 파악하기 위해 추가 분석 필요

---

실습

 

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

➡️ 위와 같은 테이블을 만들어보자.

 

[✔️ 강사님 코드]

-- 1.월매출
, cte_amount as(
	select year, month, round(sum(amount)) as 월매출
	from cte_products_sale 
	-- where category_id=1
	group by 1,2
	order by 1,2
)

 

※ 나중에 카테고리에 따른 월매출을 확인하고 싶으면 where절을 추가하면 된다.

 

-- 2.기준월매출 : 199706~199804만 남기고 0으로 처리
, 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
)

 

--5. 최종결과
select year||'-'||month as 연월, 월매출, 매출누계, 이동합계
from cte_pre10_sum
where 기준월매출 != 0;

 

[✔️ 내가 짠 코드]

-- 1단계: cte 만들어주기 (지난 시간에 만든 것 사용)
with cte_products_sale as(
    select o.order_id , o.customer_id , order_date 
        , to_char( o.order_date, 'yyyy-mm') as ym
        , 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 , c2.country , c2.city 
    from orders o , order_details od , categories c , products p , suppliers s , customers c2 
    where o.customer_id = c2.customer_id 
    and 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 
)
-- 2단계: 매출액 설정 만들기
, cte_sum as(
    select ym
        , round(sum(매출액)) 월별매출
        , case when ym between '1997-06' and '1998-04' then
            round(sum(매출액)) end 월별매출2
        , round(sum(sum(매출액)) over (order by ym rows between 10 preceding and current row)) as 이동연계
    from cte_products_sale
    group by ym
    order by ym
)
-- 3단계: 매출누계 만들기
, cte_cul as(
    select ym, sum(월별매출) 월별매출
        , round(sum(sum(월별매출2)) over (order by ym)) 매출누계
        , sum(이동연계) 이동연계
    from cte_sum
    group by ym
)
-- 4단계: 원하는 기간 설정
select ym, 월별매출, 매출누계, 이동연계
from cte_cul
where ym between '1997-06' and '1998-04';

 

 

✔️ 매출누계를 원하는 기간에서 시작하는 부분이 어려웠는데 강사님께서 앞부분을 0으로 처리하면 된다고 힌트를 주셔서 case문을 활용하여 원하는 누계값을 구할 수 있었다!

※ 카테고리별 Z함수를 그리고 싶다면 2단계 where 절에 category_id = '숫자'를 입력해주면 된다!

 

시각화

 

✔️ 태블로로 시각화까지 해보았다. 처음에는 문제를 잘못 이해해서 연도별로 파티션을 걸어서 차트 모양이 이상하게 나왔는데, 문제를 다시 이해해서 시각화해보니 알맞은 Z모양이 나왔다. 어떻게 이런 개념을 발견했을까? 신기하다 👀

 

➡️ 단기적: 살짝 떨어졌다가 올라오고 있다. (상승중)

 

 

💡 실습하면서 알게 된 개념

 

(나중에는 방법을 알아서 사용하지 않았지만🙃)

[PostgreSQL] SELECT LIMIT ~ OFFSET 사용하기 (ft. 페이징 활용)

SELECT * FROM [TABLE] LIMIT [NUM_A] OFFSET [NUM_B];

• LIMIT: 개수를 제한하는 것
• OFFSET: 시작 위치를 지정하는 것

---

📍 그룹함수

• grouping sets 함수: 지정한 컬럼의 각 그룹별 집계

 

-- 1)
select category_name, product_name, round(sum(매출액)) 매출액
from cte_products_sale
group by grouping sets (1,2);

-- 2)
select category_name, product_name, company_name, country, city, round(sum(amount)) as 매출액
from cte_products_sale
group by grouping sets (1,2,3,(4,5),())

1

2

 

• 속성 별로 집계를 하고 싶을 때 grouping sets() 함수 사용
• ()는 전체 합계를 보여준다

---

📍 [미션] 연도, 분기, 월, 일별 매출액, 주문건수 집계

 

-- 연도, 분기, 월, 일, 매출액, 주문번호
, cte_select as(
select year||'년' as year
     , year||'-'||quarter||'분기' as quarter
     , year||'-'||month||'월'  as month
     , order_date  as day
     , amount, order_id
from cte_products_sale
)

 

-- 연도, 분기, 월, 날짜로 각각 그룹핑 하여 집계
, cte_grouping_sets as(
select year, quarter, month, day
     , sum(매출액) as 총매출액
     , count(distinct order_id) as 주문건수
from cte_select
group by grouping sets (year, quarter, month, day,())
order by 1,2,3,4
)

 

-- 연도별 매출액, 주문건수
, cte_year_amount_ordercnt as(
	select year, 총매출액, 주문건수
	from cte_grouping_sets
	where year is not null
)

➡️ year을 quarter, month, day로 변경하면 각각을 확인할 수 있다.

---

-- 카테고리별 매출액
, cte_category_amount as(
    select category_name, 총매출액
    from cte_grouping_sets
    where category_name is not null
)

 

-- 제품별 매출액
, cte_product_amount as(
select product_name, 총매출액
from cte_grouping_sets
where product_name is not null
)
select *
from cte_product_amount;

 

-- 공급국가 별 매출액
, cte_country_amount as(
    select country, 매출액
    from cte_grouping_sets
    where country is not null and city is null -- city 주의
)

➡️ 4,5로 그룹핑 해주었기 때문에 country가 중복으로 나온다.

따라서 city is null 조건을 추가로 걸어주어야 한다.

 

-- (공급국가,공급도시) 별 매출액
, cte_country_city_amount as(
    select country, city, 매출액
    from cte_grouping_sets
    where country is not null and city is not null -- city 주의
)

➡️ 둘 다 있는 값을 보기 위해선 두 값 모두 not null이어야 한다. 

 

-- 전체 매출액, 주문건수
-- 방법1
, cte_all_amount as(
    select 총매출액
    from cte_grouping_sets
    order by 1 desc
    limit 1
)

-- 방법2
, cte_all_amount as(
    select max(매출액) 매출액_all
    from cte_grouping_sets
)

 

➡️ 모두 합쳐진 값은 가장 클 수밖에 없으므로 max로 구하는 것이 좋을 것 같다!

---

 

총계

---

📍 rollup 함수

✔️ 단계별 소계 (소/중/대분류 별 소계할 때 유용)

-- 카테고리, 제품 별 매출액 소계
, cte_category_product_rollup as(
	select category_name, product_name ,round(sum(amount)) 매출액
	from cte_products_sale 
	group by rollup(1,2)
	order by 1,2
)

 

-- 연도, 분기, 월, 일별 소계
, cte_orderdate_rollup as(
	select year, quarter, month, day ,round(sum(amount)) 매출액
	from cte_products_sale 
	group by rollup(year, quarter, month, day)
	order by 1,2,3,4,5
)

➡️ year-quarter-month-day /  year-quarter-month / year-quarter / year / 전체 소계를 나타낸다.

 

 

---

📍 CUBE 함수

➡️ 지정한 컬럼-그룹별 모든 조합에 대한 집계

: 가능한 모든 조합에 대한 grouping

, cte_cube as(
	select year, quarter, round(sum(amount)) as 매출액
	from cte_products_sale 
	group by cube(year, quarter)
	order by 1,2
)

• year-month
• year
• month

---

📍 과제: 고객 속성, 구매 이력에 대한 집계 (group 함수 이용)

• 고객을 분석하기 위한 테이블을 만들어서 (고객 속성별, 고객 주문기간, 속한 국가 등)  그룹함수 적용하기?
• 결과 테이블 도출 -> 주석까지 달아서

 

나는 국가와 도시, 직급로 그룹함수를 적용해보았다.

-- country, city, contact_title grouping sets (매출액, 주문건수, 상품개수)
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, 'mm') as month
	, to_char( o.order_date, 'Q') as quarter
	, to_char( o.order_date, 'YYYY-mm-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 amount
    , c.category_id , c.category_name 
    , p.product_name , p.unit_price 고정단가, p.discontinued 
    , s.supplier_id , s.company_name , c2.country , c2.city , c2.contact_title 
from orders o , order_details od , categories c , products p , suppliers s , customers c2 
where o.customer_id = c2.customer_id 
and 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 
)
-- 국가, 도시, contact_title별 grouping sets
, cte_grouping_sets as(
select country, city, contact_title, round(sum(amount)) 매출액, count(distinct order_id) 주문건수, sum(quantity) 상품개수
from cte_products_sale
group by grouping sets (1,(1,2),3, ())
order by 1,2,3
)
-- 국가별 매출액, 주문건수, 상품개수
, cte_country as(
select country, 매출액, 주문건수, 상품개수
from cte_grouping_sets
where country is not null
)
-- 도시별 매출액, 주문건수, 상품개수
, cte_city as(
select city, 매출액, 주문건수, 상품개수
from cte_grouping_sets
where city is not null
)
-- contact_title별 매출액, 주문건수, 상품개수
, cte_contact_title as(
select contact_title, 매출액, 주문건수, 상품개수
from cte_grouping_sets
where contact_title is not null
)
-- 전체 매출액, 주문건수, 상품개수
, cte_all as(
select max(매출액) 매출액_all, max(주문건수) 주문건수_all, max(상품개수) 상품개수_all
from cte_grouping_sets
)
/* 국가별 매출액, 주문건수, 상품개수: cte_country
 * 도시별 매출액, 주문건수, 상품개수: cte_city
 * contact_title별 매출액, 주문건수, 상품개수: cte_contact_title
 * 전체 매출액, 주문건수, 상품개수: cte_all */
select *
from cte_all
order by 2 desc;



-- country, city, contact_title rollup (매출액, 주문건수, 상품개수)
, cte_rollup as(
select country, city, contact_title, round(sum(amount)) 매출액, count(distinct order_id) 주문건수, sum(quantity) 상품개수
from cte_products_sale
group by rollup(1,2,3)
order by 1,2,3
)
select *
from cte_rollup;



-- country, city, contact_title cube (매출액, 주문건수, 상품개수)
, cte_rollup as(
select country, city, contact_title, round(sum(amount)) 매출액, count(distinct order_id) 주문건수, sum(quantity) 상품개수
from cte_products_sale
group by cube(1,2,3)
order by 1,2,3
)
select *
from cte_rollup;

 

---

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

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

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