COMENTO 직무 부트캠프 <SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시보드 개발> 수강생 자격으로 제공받은 Redash 프로그램을 사용했습니다.
❔ NorthWind Database
MSSQL에선 NorthWind Database라는 예제 DB를 제공하고 있다. 이 예제는 NorthWind라는 회사의 기본 Database가 저장되어 있다. COMENTO 부트캠프에서 이 예제 DB가 제공된 상태로 Redash에 접근할 수 있게 해주었다.
👁🗨 NorthWind Database ERD
기본적으로 이 Database를 이용해 SQL Query를 작성하고 Redash에서 제공하는 Visualization 기능을 이용해 Query 결과를 바로 시각화할 수 있다.
✔ SQL Query Example
* 일단 기본적으로 제공되는 NorthWind Database는 2006년 1월부터 6월까지의 Record들로만 구성되어 있다. 따라서 '2006년 각 분기'는 2006년 1~2분기임을 작성한다.
1. 2006년 각 분기별 매출은? 분기별로 카테고리별 매출이 어떻게 되는지?
select quarter(order_date) as '분기', category as '품목', sum(quantity*unit_price) as '매출' from orders, order_details, products where orders.id = order_details.order_id and products.id = order_details.product_id group by quarter(order_date), category
(1) 먼저 매출 지표는 주문량 * 가격으로 하여 추출할 수 있다. order_details table의 quantity와 unit_price 속성을 사용하였다.
(2) QUARTER(DATE)라는 예약어를 이용하였다. 'YYYY-MM-DD'형태로 되어 있는 order_date 속성을 QUARTER 함수에 대입하여 바로 int 형태로 몇 분기인지 확인할 수 있게 하였다.
(3) (분기, 카테고리) 별로 GROUP BY 하였다.
2006년 1분기 매출은 1930, 2분기 매출은 868.5 였다.
카테고리별 매출 분포는 다음과 같았다. 왼쪽이 1분기 지표, 오른쪽이 2분기 지표이다. 1분기땐 Beverages 항목이 매출의 큰 비중을 차지한 반면 2분기땐 비교적 매출이 품목별로 고르게 분포되어 있는 모습을 확인할 수 있다.
2. 월 별 매출의 지역별 비율은?
select city as '지역', month(order_date) as '월', sum(quantity*unit_price) as '매출' from customers, orders, order_details where orders.id = order_details.order_id and customers.id = orders.customer_id group by city, month(order_date) order by '월'
(1) 구매 고객의 지역은 customers table의 city 속성을 사용하였다.
(2) INNER JOIN을 이용해 필요한 속성이 있는 세 테이블을 JOIN하였다.
(3) MONTH(DATE)라는 예약어를 이용하였다. 'YYYY-MM-DD'형태로 되어 있는 order_date 속성을 QUARTER 함수에 대입하여 바로 int 형태로 몇 월인지 확인할 수 있다.
(4) (지역, 월) 별로 GROUP BY 하였다.
월 별로 지역별 매출 비율을 다음과 같이 pie chart형태로 시각화해보았다.
3. 1분기에 상품을 구매한 고객들 중 2분기에도 상품을 구매한 고객들의 2분기 지출 총액은?
select concat(c2.last_name, ' ', c2.first_name) as '이름', sum(quantity*unit_price) as '지출총액' from customers as c2, orders as o2, order_details where c2.id = o2.customer_id and order_details.order_id = o2.id and c2.id IN ( select distinct c.id from customers as c, orders as o where o.customer_id = c.id and quarter(o.order_date) = 1 ) and quarter(o2.order_date) = 2 group by concat(c2.last_name, ' ', c2.first_name)
(1) 고객 이름은 last_name 속성과 first_name 속성을 concat하여 보여지게 하였다.
(2) WHERE절에 INNER QUERY를 사용하여 1분기에 상품을 구매한 고객들 중에서만 MAIN QUERY가 작동하도록 하였다.
