[Day 8] solvesql | Advent of SQL 2024 | 온라인 쇼핑몰의 월 별 매출액 집계

온라인 쇼핑몰의 월 별 매출액 집계

 

문제 조건

1. 주문 정보 테이블과 주문 상세 정보 테이블을 조합하여 월 별 매출액 집계

2. 취소 주문을 제외한 주문 금액의 합계, 취소 주문의 금액 합계, 총 합계를 포함하는 쿼리 작성

3. YYYY-MM 형식의 'order_month' 컬럼의 값으로 오름차순 정렬

 

Solution

 

✅ SQLite

SELECT strftime('%Y-%m', o.order_date) as order_month, 
       sum(case when o.order_id not like 'C%' then oi.price * oi.quantity else 0 end) as ordered_amount,
       sum(case when o.order_id like 'C%' then oi.price * oi.quantity else 0 end) as canceled_amount,
       sum(oi.price * oi.quantity) as total_amount 
FROM orders o 
  JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY order_month 
ORDER BY order_month

 

strftime 함수를 사용하여 거래일(order_date) 컬럼을 YYYY-MM 형식으로 가공한다. 

date, datetime 함수와 달리 strftime 함수는 반환되는 결과 포맷을 지정할 수 있는 것이 특징이다.

 

cf) 포맷 지정은 아래를 참고하기 바란다.

 

 

✅ MySQL + 정규표현식

SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS order_month
     , SUM(CASE WHEN o.order_id NOT REGEXP '^C' THEN oi.price * oi.quantity END) AS ordered_amount
     , SUM(CASE WHEN o.order_id REGEXP '^C' THEN oi.price * oi.quantity END) AS canceled_amount
     , SUM(oi.price * oi.quantity) AS total_amount
FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY order_month
ORDER BY order_month

 

REGEXP '^C' : C로 시작하는 경우

NOT REGEXP '^C' :  C로 시작하지 않는 경우

 

피드백

 

쿼리를 작성하기 전에 order_date 컬럼의 데이터 타입에 따라 접근 방법이 달라질 수 있기 때문에 아래와 같은 사전 작업을 수행하였다.

PRAGMA table_info(orders)

 

확인 결과 date 형식이었으며, SQLite에서는 MySQL과 달리 date 형식은 text 형식으로 저장되기 때문에 문자열 처리를 그대로 사용할 수 있다는 것을 알게 되었다.

 

따라서 SQLite의 유연한 텍스트 기반 처리 덕분에 substr 함수를 사용하여 동일한 결과를 얻을 수 있다.

SELECT substr(o.order_date, 1, 7) as order_month, 
       sum(case when o.order_id not like 'C%' then oi.price * oi.quantity else 0 end) as ordered_amount,
       sum(case when o.order_id like 'C%' then oi.price * oi.quantity else 0 end) as canceled_amount,
       sum(oi.price * oi.quantity) as total_amount 
FROM orders o 
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY order_month 
ORDER BY order_month

 

✔️ 0209 0813 복습