[코멘토] SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시보드 개발 4주차 과제

해당 포스팅은 [코멘토] SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시보드 개발을 수강하면서 만들었던 대시보드와 받은 피드백을 바탕으로 수정한 내용을 업로드한 것입니다. 

 

 

 

 

데이터 소개

 

• 가상의 Northwind 식품회사의 데이터
• DB는 직원, 고객, 상품, 주문, 발주 등 총 20개의 테이블로 구성됨
• 데이터의 경우 2006년 1월부터 6월까지의 주문에 관한 데이터로 구성

 

 

 

테이블 소개

 

• products : 식품회사에서 취급하는 상품 정보를 담은 테이블.
  • 45개의 레코드가 존재.
  • 제품 코드, 제품명, 가격, 카테고리 등을 보여줌.
• orders : 고객이 주문한 정보를 담은 테이블.
  • 2006년 1월부터 6월까지의 주문에 관한 데이터로 총 48개의 레코드가 존재.
  • 고객이 어떤 직원에게 주문을 했으며 주문한 날짜, 배송된 날짜, 배송원, 수령인, 배송 주소, 배송 도시, 배송상태 등을 보여줌. 
• order_details : orders 테이블의 세부 사항을 나타내는 테이블.
  • 58개의 레코드 존재.
  • 고객이 어떤 제품을 구매했는지, 구매 수량, 가격 등을 보여줌.

 

 

 

대시보드 설명 

 

• 대시보드는 현시점의 상태를 보여주는 현황판.
• 따라서 최신 데이터가 들어오면 실시간 혹은 주기적으로 업데이트가 되어야 함.      
• 사용 데이터의 경우 과거 시점에서 끊겨있는 데이터이므로 자동으로 업데이트되는 것은 구현할 수 없지만, 적어도 기간에 대한 개념이 들어가 있어야 함. ex) 특정 시점을 기준으로 최근 한 달 혹은 몇 월 등
• 대시보드의 경우 특별한 분석이 들어갈 필요는 없고, 그냥 차트를 보는 것 만으로 그때그때 현황을 잘 알 수 있도록 만들어져 있으면 됨. 
• Redash에서 대시보드 생성 시 스크롤이 가능하여 여러 내용을 담을 수 있지만, 보통 스크롤을 하지 않고 한눈에 내용 파악이 가능한 액기스를 담는 것이 좋음.
• 대시보드 생성 시, 전사적인지 특정 팀에서 사용하는 것인지 생각해 보는 것이 필요.

 

 

 

---

 

 

1차 생성본

 

• 매출에 관한 내용으로  대시보드 생성
• 총 매출액, 월간 매출액, 카테고리별 매출, 월간 이용자수, 월간 구매건수, 월간 고객 평균 구매금액에 관한 내용을 담음.

 

 

 

과제물 피드백

 

• 총 매출액 배너의 경우 2006년 1월부터 6월까지의 누적 매출액을 알려주는 쿼리 작성하여 생성.
• 그러나 대시보드는 현황을 나타내는 것이므로 어디서부터 누적액인지 표시해 주는 것이 필요함.
• (+ 대시보드 생성자의 경우 언제의 데이터를 사용하는지 알지만, 대시보드를 처음 보는 사람이라면 어느 기간부터 누적액인지 모름으로) 
• 사용 데이터의 경우 과거 시점에서 끊겨있는 데이터이므로, 다음 데이터가 들어올 때 업데이터 되는 것은 구현할 수 없지만 업데이트가 되는 DB라면 월간 매출액 배너에서 7월의 데이터가 들어오면 7월에 해당하는 매출액 그래프가 생성되어야 함. 
• 카테고리별 매출 배너의 경우도 기간에 대한 내용 부족.
• 이용 유저가 많다고 해서 무조건 구매건수가 많은 것이 아님으로 월간 매출액을 볼 때 월간 이용자 수, 월간 구매건수를 같이 보는 것이 일반적.

 

 

 

---

 

 

수정본

 

• 총 매출액 배너와 카테고리별 매출 배너에 기간이 관한 내용을 추가함.
• 총 매출액 → 상반기 매출액, 카테고리별 매출 → 상반기 카테고리별 매출

 

 

 

 

---

 

 

추가 생성본

 

• 카테고리별 매출액 배너의 경우 파이차트로 표현함.
• 파이차트는 표현해야 하는 카테고리의 수가 많아지면 작은 비율을 차지하는 것이 많아져, 차트는 의미 없어질 확률이  크다는 단점을 가짐.
• 그러므로 top 5와 나머지 카테고리를 하나로 통합해 표현하는 것이 좋음.  

 

 

 

 

• 카테고리별 매출액 배너를 생성할 때 작성한 쿼리

-- 카테고리별 매출액
select p.category, 
        sum(od.quantity * od.unit_price) as sales
from order_details od left join products p on od.product_id = p.id
group by p.category
order by sales desc;




-- 상위 5개와 나머지로 구분 
select p.category, sum(od.quantity * od.unit_price) as sales,
case when p.category in (select *  
                          from (select p.category
                          		from order_details od left join products p on od.product_id = p.id
                          		group by p.category
                          		order by sum(od.quantity * od.unit_price) desc
                          		limit 5) x 
                          ) then p.category
else 'Others'
end categorys2
from order_details od left join products p on od.product_id = p.id
group by p.category;