SQL 실무 : 이커머스 주문 데이터 분석하기

 

오늘은 가상의 이커머스 데이터를 활용해 여러 테이블을 join해보면서 이커머스 주문 데이터를 분석해보고자 합니다. 살펴볼 데이터는 아래와 같은 구조도를 가집니다. 

 

주문정보가 들어가 있는 orders 테이블을 기준으로 주문ID, 고객ID, 사원ID 등을 join해 필요한 정보를 가지고 올 수 있는 다양한 테이블들이 있습니다.

 

■ e-commerce 데이터 스키마

그럼 해당 테이블 정보들을 통해 몇가지 이커머스 주문 데이터 분석을 해보도록 하겠습니다.

 

 

1. 상품(product) 카테고리별로 상품 수와 평균 가격대가 궁금한 경우

 

SELECT category, COUNT(*) AS quantity, AVG(list_price) AS avg_price
FROM products
GROUP BY category

-- quantity 주문건수, avg_price 평균가격

 

카테고리별로 취급하고 있는 상품의 수와 평균 가격대를 계산하는 쿼리입니다. 아래와 같이 음료(Beverages) 카테고리에는 5개의 상품이 있는데 물, 콜라, 이온음료 등이 있을 수 있겠죠? 

 

✏️ 실행결과

 

category	quantity	avg_price
Baked Goods & Mixes	4	11.92
Beverages	5	17
Candy	1	12.75
Canned Fruit & Vegetables	8	6.19
Canned Meat	3	8.13
Cereal	2	4.5
Chips, Snacks	1	1.8
Condiments	3	15
Dairy products	1	34.8
Dried Fruit & Nuts	5	23.95
Grains	1	7
Jams, Preserves	2	53
Oil	1	21.35
Pasta	2	28.75
Sauces	3	26.02
Soups	3	4.5

 

2. 2006년 1분기 고객별 주문횟수, 주문상품의 카테고리수, 총 주문금액이 궁금한 경우

 

SELECT customer_id, 
       CONCAT(first_name,' ',last_name) AS customer_name, 
       COUNT(*) AS orders,
       COUNT(DISTINCT category) AS order_category, 
       SUM(quantity*unit_price) AS total_price
FROM order_details AS A
     LEFT JOIN orders AS B ON A.order_id = B.id
     LEFT JOIN products AS C ON A.product_id = C.id
     LEFT JOIN customers AS D ON B.customer_id = D.id
WHERE order_date between '2006-01-01' and '2006-03-31'
GROUP BY customer_id, CONCAT(first_name,' ',last_name);

 

전체 주문 데이터에서 2006년 1분기에 발생한 주문 내역만 보고 싶을 수가 있습니다. 여기에서는 1분기에 발생한 고객별 주문횟수와 주문상품의 카테고리 수, 총 주문금액을 계산해주겠습니다. 고객이름은 customers 테이블, 주문 카테고리는 products 테이블, 총 주문금액은 order_details 테이블에서 quantity와 unit_price을 곱한 금액을 활용하면 됩니다.

 

✏️ 실행결과

 

customer_id	customer_name	orders	order_category	total_price
1	Anna Bedecs	3	1	1,674.75
3	Thomas Axen	1	1	1,930.00
4	Christina Lee	4	2	1,049.00
6	Francisco Pérez-Olaeta	1	1	680
7	Ming-Yang Xie	1	1	13,800.00
8	Elizabeth Andersen	2	2	1,551.00
10	Roland Wacker	4	4	1,160.00

 

 

3. 2006년 3월에 주문이 발생한 건에 대한 주문상태 확인하고 싶은 경우

 

-- 스칼라 서브쿼리 사용
SELECT DATE_FORMAT(order_date, '%Y-%m') AS quarter, ship_name, status_id, 
        (SELECT status_name
         FROM orders_status AS B
         WHERE A.status_id = B.id) AS status_name
FROM orders AS A
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2006-03';

-- JOIN 사용
SELECT order_date, DATE_FORMAT(order_date, '%Y-%m') AS quarter, ship_name, status_id, B.status_name
FROM orders AS A
     LEFT JOIN orders_status AS B ON A.status_id = B.id
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2006-03';

 

주문 건에 대한 발송현황이 궁금할 수도 있습니다. 여기서는 2006년 3월에 발생한 주문 건에 대한 주문상태를 확인하는 쿼리를 만들어주었는데요. WHERE 절을 활용해 2006년 3월에 발생한 주문 건만 가져오도록 해주어야 합니다. 그리고 서브쿼리나 조인 등을 활용해 order_status 테이블의 status_name 정보를 가져와 주어야 합니다.

 

 

✏️ 실행결과

 

year_month	ship_name	status_name
2006-03	Francisco Pérez-Olaeta	Closed
2006-03	Amritansh Raghav	Closed
2006-03	Elizabeth Andersen	Closed
2006-03	Roland Wacker	Closed
2006-03	Ming-Yang Xie	New
2006-03	Roland Wacker	Shipped
2006-03	Peter Krschne	New
2006-03	Anna Bedecs	New

 

 

4. 2006년 1분기에 세 번 이상 주문된 상품과 그 상품의 주문 수가 궁금한 경우

 

-- HAVING절
SELECT product_id, product_name, COUNT(*) AS orders
FROM order_details AS A
	 LEFT JOIN orders AS B ON A.order_id = B.id
	 LEFT JOIN products AS C ON A.product_id = C.id
WHERE QUARTER(order_date) = 1
GROUP BY product_id, product_name
HAVING COUNT(*) >= 3;

-- 인라인뷰 서브쿼리
SELECT *
FROM (SELECT od.product_id, COUNT(DISTINCT o.id) orders
      FROM orders o LEFT JOIN order_details od ON o.id = od.order_id
      WHERE o.order_date BETWEEN '2006-01-01' AND '2006-03-31'
      GROUP BY od.product_id
     ) a
WHERE a.orders >= 3;

 

특정 시점에 주문이 많이 된 상품이 있는 지 분석하는 경우 입니다. 여기서는 2006년 1분기에 3번 이상 주문된 상품이 있는지를 확인하기 위해 HAVING절 혹은 인라인뷰 서브쿼리를 통해 아래와 같이 3번 이상 주문된 상품의 ID와 이름 정보를 확인할 수 있습니다.

 

✏️ 실행결과

 

product_id	product_name	orders
19	Northwind Traders Chocolate Biscuits Mix	3
43	Northwind Traders Coffee	4
80	Northwind Traders Dried Plums	3
81	Northwind Traders Green Tea	3

 

 

5. 2006년 1분기, 2분기 연속으로 주문을 받은 직원이 있을까?

 

SELECT o1.employee_id, CONCAT(c.first_name, ' ', c.last_name) employee_name
FROM (SELECT DISTINCT(employee_id)
      FROM orders
      WHERE order_date >= '2006-01-01' AND order_date <= '2006-03-31'
      ) o1
      
      INNER JOIN 
      
      (SELECT DISTINCT(employee_id)
      FROM orders
      WHERE order_date >= '2006-04-01' AND order_date <= '2006-06-30'
      ) o2
      
      ON o1.employee_id = o2.employee_id

      LEFT JOIN customers c ON o1.employee_id = c.id
ORDER BY o1.employee_id;

 

영업사원의 실적은 항상 일정하지 않을 수 있기 때문에 실적이 일정한 직원을 파악해보려고 합니다. 여기서는 2006년 1분기와 2분기에 연속으로 주문을 받은, 즉 분기별로 연속 영업에 성공한 직원을 찾는 쿼리를 만들었습니다. INNER JOIN을 활용해 각 분기에 주문이 발생한 직원들의 교집합을 구하면 연속으로 주문을 받은 직원을 찾을 수 있습니다.

 

 

✏️ 실행결과

 

employee_id	employee_name
1	Anna Bedecs
3	Thomas Axen
4	Christina Lee
6	Francisco Pérez-Olaeta
8	Elizabeth Andersen
9	Sven Mortensen

 

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.