2020. 12. 02 수요일

두번째 프로젝트에 대한 지표 선정을 하였다.

• 우선 이번 프로젝트에는 구해야할 지표가 명확히 정해져있다.(판매량 TOP 10 카테고리)

• 주어진 지표 외에도 다른 지표가 있으면 좋을 것 같다고 생각했다.

  • 추가적인 지표를 생각하기 위해 분석의 이유에 대해 생각해보았다.

    • 이번 분석을 진행하는 이유는 홍보영상에 등장시킬 품목을 선정하기 위함이다.

    • 홍보영상에 인기있는 제품을 등장시켜 광고 시청자의 흥미를 유발한다.

    • 그러기 위해서는 인기있는 제품을 선정할 필요가 있다.

  • 인기있는 제품은 전체 기간 누적 판매량보다는 최근 기간에서 가장 많이 팔린 상품으로 선정하는 것이 트렌드 반영에 있어서 바람직하다고 생각하였다.

  • 따라서 최근 6개월 내 판매량 TOP 10 카테고리를 추가 지표로 선정하여, 전체 기간과 비교해보면 좋을 것 같았다.

전체 기간 누적 TOP 10을 구할 Query를 작성하였다.

• 우선 카테고리별 판매량을 집계해야하니 COUNT 와 GROUP BY 를 사용하였다.

  • 그리고 중복항목을 제거하기 위해 COUNT 에 DISTINCT 를 추가하였다.

• 사용할 테이블은 총 3개이다.

  • order_item : 주문id와 주문한 상품의 id가 저장된 테이블이다.
  • product : 상품의 id와 상세 정보, 그리고 카테고리가 저장된 테이블이다.
  • product_translation : 상품의 카테고리를 영어로 번역해둔 테이블이다.
  • 위 3개의 테이블 중 order_item은 FROM 을 통해 불러오고 나머지 두개는 JOIN을 통해 불러왔다.

• 각각의 테이블을 product_id 와 카테고리명으로 매칭시켜주었다.

• 최종적으로 SELECT 하는 Column은 product_translation 테이블의 영어 카테고리와 COUNT(DISTINCT order_id) 이다.

6개월 내 판매량 TOP 10을 구할 Query를 작성하였다.

• 전체적인 Query는 첫번째 Query와 비슷하다.

• 하지만 6개월이란 기간 설정을 추가해주어야 한다.

  • 간단하게 WHERE 에서 6개월 전의 날짜를 지정하여 필터링할 수 있지만, 6개월 전의 날짜를 달력이 아닌 Query를 통해 구해보고 싶었다.

  • 따라서 FROM에 Subquery를 적용하여 '가장 최근 날짜'와 '가장 최근 날짜 -6개월' 을 구하고, 두 날짜를 WHERE BETWEEN AND 에 적용하여 기간을 설정하였다.

SELECT 
	MAX(CAST(FORMAT_TIMESTAMP("%F", order_purchase_timestamp) AS DATE)) AS max_date, 
    	DATE_ADD(MAX(CAST(FORMAT_TIMESTAMP("%F", order_purchase_timestamp) AS DATE)), 
	INTERVAL -6 MONTH) AS before_6month
 #이하 생략