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

해당 포스팅은 [코멘토] SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시보드 개발을 수강하면서 했던 과제를 업로드한 것입니다. (본 코드는 MySQL 5.7 환경에서 작성되었습니다.)

 

 

---

데이터 소개

 

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

 

 

 

테이블 소개

 

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

 

 

 

 

1. 상품(product)의 카테고리(category)별 상품 수와 평균 가격대(list_price)를 찾는 쿼리를 작성하세요.

 

 

[작성 코드]

 

select category, count(id) as cnt, avg(list_price) as avg
from products
group by category;

 

1번 문제의 경우 예시 답안과 풀이가 동일하고 count(id) 부분을 count(1)으로 변경 가능합니다. 

 

주의해야 하는 것은 group by절이 사용된 경우

select 절의 맨 앞에 적는 것과 group by 기준이 여러개이면 select절에 기준을 다 밝혀줘야 한다는 것입니다. 

 

 

 

 

---

 

 

 

 

2. 2006년 1분기에 고객(customer)별 주문(order) 횟수, 주문한 상품(product)의 카테고리(category) 수,

총 주문 금액(quantity * unit_price)를 찾는 쿼리를 작성하세요.

 

[작성 코드]

 

select o.customer_id, count(o.id) as cnt_order,
        count(distinct p.category) as cnt_category,
        sum(od.quantity * od.unit_price) as sum_of_order_price
from orders o 
left join order_details od on o.id = od.order_id
left join products p on p.id = od.product_id
where o.order_date between '2006-01-01' and '2006-03-31'
group by o.customer_id;

 

 

 

[예시 답안]

 

select o.customer_id,
        count(distinct o.id) as cnt_order,
        count(distinct p.category) as cnt_category,
        sum(od.quantity * od.unit_price) as sum_of_order_price
from orders o 
left join order_details od on o.id = od.order_id
left join products p on p.id = od.product_id
where '2006-01-01'<= o.order_date and o.order_date < '2006-04-01'
group by o.customer_id;

 

위의 두 코드 실행 결과는 동일합니다.

다만 다른것은 select 절의 cnt_order 부분입니다.

 

orders와 order_details는 부모 자식 관계라고 할 수 있습니다.

정리하면 orders 테이블의 PK인 id가 order_details 테이블에서 order_id라는 변수명으로 존재하며

order_details에서 FK로 작동하고 있습니다. 

 

그렇기 때문에 두 테이블을 조인하여 사용하는 경우,

order_details.order_id는 중복을 허용하기 때문에 order.id를 사용하는 경우에는 distinct를 사용해서 코드를 작성하는 것이 좋습니다.  

 

 

 

---

 

 

 

 

3 . 2006년 3월에 주문(order)된 건의 주문 상태(status_name)을 찾는 쿼리를 작성하세요. (sub-query)

 

[작성 코드]

 

select o.id, o.status_id, 
      (select os.status_name
      from orders_status os
      where o.status_id = os.id ) as status_name
from orders o
where o.order_date between '2006-03-01' and '2006-03-31';

 

3번 문제의 경우 예시 답안과 풀이가 동일합니다.

 

 

 

---

 

 

 

 

4. 2006년 1분기 동안 세 번 이상 주문(order)된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요.

 

[작성 코드]

 

select od.product_id, count(od.product_id) as cnt
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
having count(od.product_id) >= 3;

 

 

 

[예시 답안]

 

select od.product_id, count(distinct od.product_id) as cnt
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
having count(distinct od.product_id) >= 3;

 

위의 두 코드 실행 결과는 동일합니다.

작성 코드와 예시 답안의 차이는 2번 문제와 동일한 이유로 count함수 사용 시 distinct를 이용한 것입니다.

 

 

 

---

 

 

 

 

5-1. 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)을 찾는 쿼리 작성하세요. (sub-query, inner join)

 

[작성 코드]

 

select distinct a.employee_id
from orders a
where employee_id in (select b.employee_id
                      from orders b
                      where date_format(b.order_date, '%Y-%m-%d') between '2006-01-01' and '2006-03-31')
                      
      and 
     employee_id in (select c.employee_id
                     from orders c
                     where date_format(c.order_date, '%Y-%m-%d') between '2006-04-01' and '2006-06-30')
order by a.employee_id;

 

 

 

[예시 답안]

 

select o1.employee_id
from (select distinct employee_id
      from orders
      where '2006-01-01' <= order_date and  order_date < '2006-04-01') o1
inner join
(select distinct employee_id
 from orders
 where '2006-04-01' <= order_date and order_date < '2006-07-01') o2
 on o1.employee_id = o2.employee_id;

 

예시 답안은 sub-query와 inner join을 이용한 것입니다. 

 

문제를 풀 때 sub-query가 어려워서 어떤 식으로 풀어야 할지 이것저것 고민하다가 where절에 in을 이용하여 풀었는데

예시 답안을 보니 이런 식으로도 풀 수 있구나 배웠습니다.

 

멘토님께서 설명해 주실 땐 그렇구나 하고 넘어갔는데 스스로 복습하니까 또 생각이 안 나고

역시 공부는 복습이 최고라는 걸 느낀..^^

 

참고로 결과물은 동일합니다. 

 

 

 

---

 

 

 

 

5-2 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)별로, 2006년 월별 주문 수 를 찾는 쿼리를 작성하세요. (sub-query 중첩, date_format())

 

[작성 코드]

 

select a.employee_id, date_format(a.order_date, '%Y-%m') as ym, 
        count(date_format(a.order_date, '%Y-%m')) as cnt
from orders a
where employee_id in (select b.employee_id
                      from orders b
                      where date_format(b.order_date, '%Y-%m-%d') between '2006-01-01' and '2006-03-31')
                      
      and 
     employee_id in (select c.employee_id
                     from orders c
                     where date_format(c.order_date, '%Y-%m-%d') between '2006-04-01' and '2006-06-30')
group by a.employee_id, date_format(a.order_date, '%Y-%m');

 

 

[예시 답안]

 

select employee_id, date_format(order_date, '%Y-%m') as ym, count(distinct id) as cnt
from orders
where employee_id in (select o1.employee_id
                        from (select distinct employee_id
                                from orders
                                where '2006-01-01' <= order_date and order_date < '2006-03-31') o1
                        inner join
                        (select distinct employee_id
                        from orders
                        where '2006-04-01' <= order_date and order_date < '2006-07-01') o2
                        on o1.employee_id = o2.employee_id )
group by employee_id, ym;

 

5-2 문제의 경우 sub-query 중첩을 이용한 풀이를 요구하셨는데 저는 5-1의 풀이를 이용해서 풀었습니다.

 

세부 구조는 아래와 같습니다. 

sub-query 중첩이 아직 어려워서 저와 같은 분들이 계실까 봐 풀어 적어봤습니다. 도움이 되셨으면 좋겠습니다 ㅎㅎ

 

 

 

+ 한 가지 의문은 sql 실행 순서가 from - where - group by - having - select - order by으로 알고 있는데

group by절에서 왜 date_format(order_date, '%Y-%m')으로 적는 게 아니라 ym으로 적어도 작동하는지...

사실 ym으로 작동하는 게 작성하기도 쉽고 보기도 쉬워서 더 좋긴 한데 실행 순서는 그게 아니라 좀 궁금하네요

(혹시 아시는 분 계시면 댓글 달아주세요!)