<TIL> 2024-01-02

내일배움캠프 본 캠프 10일차 TIL 작성

2024년 새해 첫 학습일지

---

• 오늘 학습한 것
  1. 내일배움캠프 SQL 개인과제 문제
  2. 정현석 튜터님 SQL 특강

---

지난 주 금요일에 개인과제를 다 완료하지 못해서 오늘 마저 다 풀게 되었다.

 

오늘 애를 먹었던 문제는 아래 사진의 문제이다.

 

우선 테이블이 3개가 있고 3개의 테이블을 통해서 조회해야하는 문제 요구사항은 다음과 같다.

1. 각 주(State)별로 주문의 총 금액(Amount)을 계산
2. 각 주(State)별로 주문의 총 이익(Profit)을 계산 
3. 각 주(State)내에서 주문의 매출 순위를 결정
4. 각 주(State)내에서 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지의 여부를 판단

우선 조회해야하는 데이터가 그룹을 지을 것을 요구하는데 각 요구사항마다 그룹이 다른 경우가 있다 

따라서 group by가 아니라 window 함수로 파티션을 나누는 방식으로 각각을 구하고자 했다. 

 

각각의 데이터 테이블은 아래와 같다. 

list_of_orders

order_details

sales_target

 

위 3개의 테이블을 조인을 해야하는데 요구사항에서 사용할 데이터만 뽑고 원하는 형태로 사전에 가공하기 위해

임시 테이블을 만들어야했는데 이 때까진 with문으로 사용했는데 미리 만들어놓고 그 때 그 때 쿼리 수정을 용이하게 하려면 View를 생성하는 것이 더 낫겠다는 생각이 들었다. 

 

* 모든 문제는 MySQL로 풀었다.

 

첫 번째 View는 list_of_orders와 order_details를 조인하고 필요한 컬럼만 조회하였다

create or replace view order_view as(
select lo.order_id, 
       date_format(lo.order_date, '%Y-%m') as order_month,
       lo.State,
       od.Amount,
       od.Profit,
       od.Category
from list_of_orders lo inner join order_details od on lo.order_id = od.order_id
where lo.order_id<>''
)

 

이 때 list_of_orders 테이블에 order_id가 공백으로 지정되어 있는 데이터를 갖고 있지 않은 행들이 여럿 있어서 해당 행을 없애는 조건을 집어넣었고 아래의 코드로 만든 두 번째 view와의 조인을 위해 order_date의 형태를 yyyy-mm의 형태로 가공하여 order_month로 조회하였다. 

 

create or replace view sales_view as(
select concat(year, '-', month) as order_month, 
       Category, 
       Target
from 
(
select case when substr(month_of_order, 5, 2) = '18' then '2018'
            when substr(month_of_order, 5, 2) = '19' then '2019'
            end as 'year',
       case when substr(month_of_order, 1, 3) = 'Jan' then '01' 
            when substr(month_of_order, 1, 3) = 'Feb' then '02' 
            when substr(month_of_order, 1, 3) = 'Mar' then '03' 
            when substr(month_of_order, 1, 3) = 'Apr' then '04' 
            when substr(month_of_order, 1, 3) = 'May' then '05' 
            when substr(month_of_order, 1, 3) = 'Jun' then '06' 
            when substr(month_of_order, 1, 3) = 'Jul' then '07' 
            when substr(month_of_order, 1, 3) = 'Aug' then '08' 
            when substr(month_of_order, 1, 3) = 'Sep' then '09' 
            when substr(month_of_order, 1, 3) = 'Oct' then '10'
            when substr(month_of_order, 1, 3) = 'Nov' then '11'
            when substr(month_of_order, 1, 3) = 'Dec' then '12'
            end as 'month',
	   Category, 
	   Target
from sales_target
order by 1, 2
) a
);

 

그리고 두 번째 뷰는 month_of_order의 데이터가 Apr-18과 같은 형태인 것을 볼 수 있는데 이 경우 앞의 데이터와 

조인이 불가능하므로 SUBSTR()을 이용해서 년도와 월을 분할하고 이 데이터를 서브쿼리로 하여 concat 하는 것을 통해서 yyyy-mm의 형태로 만들어주었고, 카테고리와 목표 매출액을 조회하여 뷰로 만들었다.

 

이렇게 미리 데이터를 정리하고 이 두 view를 조인하여 쿼리문을 작성하였다.

select order_id, 
       Category, 
       State, 
       Amount, 
       rank_in_state,
       sum_profit, 
       sum_amount, 
       order_category_amount,
       avg_target,
       case when order_category_amount / avg_target >= 0.5 then 'success'
            else 'fail'
            end as target_success
from
(
select o.order_id,
       o.Category,
       o.State,
       o.Amount,
       rank() over(partition by State order by o.Amount desc) rank_in_state,
       sum(o.Profit) over (partition by o.State) sum_profit,
       sum(o.Amount) over (partition by o.State) sum_amount,
       sum(o.Amount) over (partition by o.Category, o.State) order_category_amount,
       avg(s.Target) over (partition by o.Category, o.State) avg_target
from order_view o inner join sales_view s on (o.order_month, o.Category) = (s.order_month, s.Category)
) a 
order by State, rank_in_state;

 

우선 조인을 할 때 문제 요구사항에서 각 주별로 총 주문금액이 카테고리별 매출 목표액의 기준의 50%를 만족했는지를 알기 위해서는 sales_view의 Target 데이터가 해당하는 order_month와 Category에 따라 정해져야하므로

두 컬럼을 기준으로 조인을 해주었다.

 

먼저 주 별로 각 주문의 매출액 순위를 알기 위해 State를 기준으로 파티션을 나누고 Amout기준으로 내림차순 정렬하여서 

rank()함수를 이용해 조회를 했다. rank() over(partition by State order by o.Amount desc)

 

또 주 별 총 매출액과 총 이익을 조회하기 위해서  State를 기준으로 파티션을 나누어 sum()함수를 이용해 총합을 구했다.

sum(o.Profit) over (partition by o.State) sum_profit
sum(o.Amount) over (partition by o.State) sum_amount

 

마지막으로 주, 카테고리별 총 매출액이 매출 목표 평균의 50%를 넘었는지를  계산하기 위해서 주별, 그리고 카테고리별로 Amount를 합계를 내고  Target을 평균을 낸 값을 계산하였다.

sum(o.Amount) over (partition by o.Category, o.State) order_category_amount,
avg(s.Target) over (partition by o.Category, o.State) avg_target

이렇게 집계하고 계산한 쿼리를 서브쿼리 테이블로 하여 문제 요구사항을 풀어냈다. 

 

뷰를 사용해서 문제를 처음 풀어보았는데 서브쿼리를 작성했을 때보다 훨씬 더 용이하게 작성할 수 있었다. 

오늘 특강에서도 튜터님이 뷰가 제일 많이 사용하는 쿼리문 중에 하나라고 하셨으니 앞으로도 쿼리 작성에 적극 활용해야겠다. 

---

오늘 SQL 특강은 SQL의 기초부터 복잡한 CTE문과 window함수까지 진행되었는데 

대부분 기존에 알고 있던 SQL의 내용을 빠르게 복습하는 내용으로 진행되었다. 

 

하지만 현업에서 그러한 내용들이 어떻게 사용이 되는지 들을 수 있어서 좋았다.

 

특강에서 튜터님이 말씀해주신 현업에 관한 내용

• 현업에서는 서브쿼리를 사용하기가 어렵다. 대용량 데이터의 경우 컴퓨터 자원을 너무 많이 소모하게 되기 때문이다.
• Group by 로직이 distinct에 비해서 속도가 평균적으로 빠르지만 데이터에 따라서 distinct를 사용했을 때 속도가 엄청 느려지는 경우가 있다. 
• alias는 가급적 이해가 가능한 약자를 사용하는 것이 좋다. 쿼리가 길어지면 alias가 기억이 안나서 가독성이 매우 떨어진다.
• 쿼리에 대한 주석을 잘 작성하라
• View는 테스트 코드를 하거나 복잡한 쿼리를 간단하게 만들기 위해, 여러번 반복적으로 사용될만한 통계, 집계들을 미리 수행하여서 여러번 집계하지 않도록 하기 위해 사용된다.