내배캠 TIL 10일차

1. SQL 개인과제

"조건에 맞는 쿼리문을 작성하고 해설을 작성하기"

쿼리문을 올렸놨으니 자유로운 훈수 부탁드립니다

근데 오늘 특강에서 서브쿼리를 지양하라는 말씀이 있으셨다 이말이야

다 고쳐보도록

 

 

**문제 1:** "최소 주문 날짜 찾기"

주어진 테이블 **`list_of_orders`**에는 여러 주문들의 날짜가 저장되어 있습니다.

주문 날짜는 'dd-mm-yyyy' 형식으로 저장되어 있습니다. 
이 테이블에서 가장 이른 주문 날짜를 찾아보세요.

select a.ORDER_DATE
from (
  select STR_TO_DATE(order_date, "%Y-%m-%d") as ORDER_DATE 
  from list_of_orders
  ) a
where ORDER_DATE is not null
order by 1
limit 1

 

 

좀 살펴보니 order_date에 null값이 있어서 where로 처리

 

SELECT 
MIN(order_date) 
FROM list_of_orders
WHERE STR_TO_DATE(order_date, '%d-%m-%Y');

 

 

str_to_date의 두번째 인수는 읽어오는 데이터의 양식을 넣는 것임

order_date의 빈 값은 의도된 것이 아니었음..

 

 

 

**문제 2:** "카테고리별 총 매출 계산하기"

**`order_details`** 테이블에는 다양한 주문들의 세부 정보가 저장되어 있습니다.

각 주문은 특정 'Category'에 속하며,
각 주문의 'Amount'는 그 주문의 매출 금액을 나타냅니다.
이 테이블을 사용하여 각 카테고리별 총 매출 금액을 계산하세요.

select Category,
   SUM(AMOUNT) as TOTAL_AMOUNT
from order_details
group by Category

 

동일!

 

 

 **문제 3:** "상위 5명의 고객별 총 구매액 찾기"

두 개의 테이블 **`order_details`**와 **`list_of_orders`**가 있습니다.

**`order_details`** 테이블은 각 주문의 세부사항을 포함하고 있으며,

**`list_of_orders`** 테이블은 주문에 대한 고객의 이름과 주문 ID를 포함합니다.
각 고객별로 총 구매액을 계산하여,
구매액이 가장 높은 상위 5명의 고객을 찾아보세요.

select loo.customername,
   SUM(OD.Amount) as TOTAL_AMOUNT
from list_of_orders loo 
inner join order_details od
on LOO.order_id = OD.order_id
group by LOO.customername
order by 2 desc
limit 5

 

 

unique한 값에 대한 얘기를 팀원들과 한 결과
-> 나는 customername을 기준으로 하기로 결정

 

동일!

 

 

 

**문제 4:** "여러 주문을 한 고객들의 총 구매액 계산하기"

두 테이블 **`list_of_orders`**와 **`order_details`**가 있습니다.

**`list_of_orders`** 테이블은 고객 이름과 주문 ID를,
**`order_details`** 테이블은 각 주문의 상세 금액을 포함하고 있습니다.
이 테이블들을 사용하여, 3개 이상의 주문을 한 고객들의 이름,
총 주문 횟수, 그리고 그들의 총 구매액을 계산하세요.

select distinct a.customername,
   a.total_count,
       a.total_amount
from list_of_orders loo 
inner join (select LOO.customername,
   COUNT(*) as TOTAL_COUNT,
   SUM(OD.AMOUNT) as TOTAL_AMOUNT
   from list_of_orders loo
inner join order_details od
   on LOO.order_id = OD.order_id
   group by LOO.customername) a
on loo.customername = a.customername
where TOTAL_COUNT >= 3
order by TOTAL_COUNT desc, TOTAL_AMOUNT desc

 

 

- 서브쿼리 내에서 고객 별로 count
- join 후 count를 where에서 조건으로 사용
- distinct로 동일 고객의 다른 주문들은 쳐낸다

 

SELECT 
MIN(order_date) 
FROM list_of_orders
WHERE STR_TO_DATE(order_date, '%d-%m-%Y');

 

 

str_to_date의 두번째 인수는 읽어오는 데이터의 양식을 넣는 것임

order_date의 빈 값은 의도된 것이 아니었음..

 

 

 

 

**문제 5:** "평균 수량을 초과하는 주문 찾기"

**`order_details`** 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다.

이 테이블의 각 행은 주문 ID(**`order_id`**), 카테고리(**`Category`**),

그리고 주문 수량(**`Quantity`**)을 포함합니다.
이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는
모든 주문들을 찾아보세요.

select od.order_id,
   od.category,
       od.quantity,
       a.avg_quantity
from order_details od
inner join (select Category,
   AVG(Quantity) as avg_quantity
from order_details
group by Category) a
on od.category = a.category
where od.quantity > a.avg_quantity
order by od.quantity desc

 

앞과 마찬가지로 where에 조건으로 사용하기 위해
서브쿼리에 avg를 사용

SELECT 
    order_id, 
    Category, 
    Quantity
FROM 
    order_details
WHERE 
    Quantity > (
        SELECT AVG(Quantity) 
        FROM order_details AS od2 
        WHERE od2.Category = order_details.Category
    );

 

문제는 평균 구매량을 출력하는 것을 원하지는 않았다...

그러면 굳이 서브쿼리와 join하지 않아도 된다

서브쿼리를 where 절에서 사용하는 것이 더 바람직하다고 말씀해주셨다  

 

 

 

 

> **문제 6:** "카테고리별 매출액 순위 및 누적합계 계산하기"

**`order_details`** 테이블에는 주문의 상세 정보가 포함되어 있습니다.
이 테이블은 주문 ID(**`order_id`**), 카테고리(**`Category`**), 
그리고 주문의 금액(**`Amount`**)을 포함합니다. 
이 테이블을 사용하여 각 카테고리 내에서 각 주문의 매출액 순위와 
그 카테고리 내의 누적 매출액을 계산하세요.

select order_id,
   category,
       amount,
   rank() over(partition by category order by amount desc) as amount_rank,
   sum(amount) over (partition by category order by order_id) as cum_sum_amount
from order_details
order by category, cum_sum_amount, amount

 

- rank

카테고리 내에서의 - partition by category
매출액 순위 - order by amount

- sum

카테고리 내에서의 - partition by category
누적매출액 - sum(amount)

 

 

 

문제 7: "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"

**`list_of_orders`**, **`order_details`**, 그리고 **`sales_target`** 세 개의 테이블이 있습니다.
**`list_of_orders`** 테이블은 주문 ID(**`order_id`**)와 주문이 이루어진 주(**`State`**를,

**`order_details`** 테이블은 각 주문의 금액(**`Amount`**)과 이익(**`Profit`**)을, 
**`sales_target`** 테이블은 각 카테고리별 매출 목표(**`Target`**)를 포함합니다.

이 테이블들을 사용하여 각 주별로 주문의 총 금액과 이익을 계산하고,

각 주 내에서 주문의 매출 순위를 결정하세요.
또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.

select loo.order_id,
       loo.state,
       a.total_amount,
       od2.amount,
       rank() over(partition by loo.state order by od2.amount desc) amount_rank_in_state,
       od2.category,
       st.avg_target,
       if(od2.amount >= st.avg_target/2, 'success', 'fail') as goal
from list_of_orders loo
inner join (select loo.state,
   				   sum(od.amount) as total_amount,
      			   sum(od.profit) as total_profit
   			from list_of_orders loo
   			inner join order_details od
   			on loo.order_id = od.order_id
   			group by loo.State) a
on loo.state = a.state
inner join order_details od2
on loo.order_id = od2.order_id
inner join (select category,
				   avg(target) as avg_target
            from sales_target
            group by category) st
on od2.category = st.category
order by 2, 3 desc

- (총금액, 총이익), 매출순위, 평균매출목표

구해야할 게 많아서 그만큼 서브쿼리도 늘어남
코드가 어지럽다면 그건 저 역시도 마찬가지니 상관없다고 생각합니다

 

SELECT 
    order_id, 
    Category, 
    Quantity
FROM 
    order_details
WHERE 
    Quantity > (
        SELECT AVG(Quantity) 
        FROM order_details AS od2 
        WHERE od2.Category = order_details.Category
    );

 

문제는 평균 구매량을 출력하는 것을 원하지는 않았다...

그러면 굳이 서브쿼리와 join하지 않아도 된다

서브쿼리를 where 절에서 사용하는 것이 더 바람직하다고 말씀해주셨다  

 

 

 

 

문제 8: 고객 평생 가치(CLV) 분석

당신은 list_of_orders와 order_details 두 테이블을 가지고 있으며,

이를 통해 고객별 평생 가치를 분석하고자 합니다.

list_of_orders 테이블은 고객 이름(CustomerName), 주문 날짜(order_date), 주문 ID를 포함하고, order_details 테이블은 각 주문의 금액(Amount)과 수익(Profit)을 포함합니다.

고객별로 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산하고,

이를 기반으로 고객의 평생 가치를 분석하세요.

## LTV = (평균 구매액 x 구매 횟수 = 총 구매액) x 앱 사용 기간
select customername,
      total_amount,
      buy_counts,
      round(if(datediff(max_date, min_date)=0, 1, datediff(max_date, min_date))/buy_counts) as avg_buy_frequency,
      total_amount*if(datediff(max_date, min_date)=0, 1, datediff(max_date, min_date)) as LTV
from (select customername,
             sum(amount) as total_amount,
             sum(profit) as total_profit,
             max(order_date) as max_date,
             min(order_date) as min_date,
             count(distinct order_id) as buy_counts
     from (select loo.CustomerName,
                 loo.order_id,
                   str_to_date(loo.order_date, '%Y-%m-%d') as order_date,
                   od.amount,
                   od.profit
           from list_of_orders loo
           inner join order_details od
           on loo.order_id = od.order_id
           ) a
     group by customername
     ) b
order by 5 desc, 4 desc, 3 desc, 2 desc

 

- 7번이랑 비슷한 양상으로 서브쿼리 내에서 총 합계 같은 통계값이나 str_to_date 같은 전처리를 먼저 해준다

- 한번만 산 사람은 datediff 때 0으로 나와서 if를 통해 1로 해주었다

- 맨위의 공식을 고객의 평생가치를 구할 때 사용하였다

코드가 어지럽다면 그건 저 역시도 마찬가지니 상관없다고 생각합니다

 

WITH Customer_Order_Details AS (
    SELECT 
        lo.CustomerName, 
        STR_TO_DATE(lo.order_date, '%d-%m-%Y') AS order_date,
        od.Amount,
        ROW_NUMBER() OVER (PARTITION BY lo.CustomerName ORDER BY lo.order_date) AS rn
    FROM 
        list_of_orders AS lo
    JOIN 
        order_details AS od ON lo.order_id = od.order_id
),
Order_Intervals AS (
    SELECT 
        CustomerName, 
        DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY CustomerName ORDER BY order_date)) AS days_between_orders
    FROM 
        Customer_Order_Details
    WHERE 
        rn > 1
),
Customer_Stats AS (
    SELECT 
        cod.CustomerName, 
        SUM(cod.Amount) AS Total_Amount, 
        COUNT(DISTINCT cod.order_date) AS Total_Orders,
        AVG(oi.days_between_orders) AS Avg_Days_Between_Orders
    FROM 
        Customer_Order_Details AS cod
    LEFT JOIN 
        Order_Intervals AS oi ON cod.CustomerName = oi.CustomerName
    GROUP BY 
        cod.CustomerName
)

SELECT 
    cs.CustomerName,
    cs.Total_Amount,
    cs.Total_Orders,
    cs.Avg_Days_Between_Orders,
    cs.Total_Amount / cs.Total_Orders AS Avg_Order_Value,
    cs.Total_Orders / NULLIF(cs.Avg_Days_Between_Orders, 0) AS Purchase_Frequency,
    (cs.Total_Amount / cs.Total_Orders) * (cs.Total_Orders / NULLIF(cs.Avg_Days_Between_Orders, 0)) * 1 AS Estimated_CLV -- 1년 기준 CLV
FROM 
    Customer_Stats AS cs;

 

최초로 정답 쿼리가 더 길다..

그런데 성능 면에서는 더 우수할 수 있기 때문에

쿼리문을 뜯어서 봐야겠다

 

 

 

 

 

 

실전문제

실전문제는 작동하는 것에 대해서 자세히 쓰라고 했는데 어떻게 작성해야 하는지 이해가 잘 되지 않아

나름대로 이해한 바와 보완할 수 있을 거 같은 부분에 대해 작성하였다

 

피드백

고급까지 문제를 다시 풀어보고

실전문제에 대한 질문은 개별적으로 할 것

 

+

 

플로우차트를 그려가며 쿼리문을 공부하는 것을 추천

draw.io 를 통해 웹 상에서 그릴 수 있음

 

 

실전 문제 1

아래의 쿼리를 글로 동작 방법에 대해 자세히 설명하고,

해당 SQL 쿼리의 작동 방식을 시각적으로 표현하여 제출 해주세요!

쿼리문은 너무 길어서 생략

마지막 구매일로부터 2019년 3월 31일 간의 간격을 바탕으로 고객을 세분화하여 관리하려는 코드

ntile을 통해 마지막 구매일과의 간격이 긴 고객부터 5등급으로 나누어 세분화(R)
-> 등급의 숫자가 높을 수록 최근에 구매

마찬가지로 총 구매횟수는 적은 순으로 5등급으로 세분화(F)
-> 등급의 숫자가 높을 수록 총 구매횟수가 많음

총 구매액은 적은 순으로 5등급으로 세분화(S)
-> 등급의 숫자가 높을수록 총 구매액이 큼

case when을 통해 3개의 등급을 결합하여 재분류

하지만 이는 제품의 특성(카테고리)를 제대로 반영하지 않은 것으로 보인다
가령 카테고리가 보일러라든지 냉장고 등의 대형 가전이라고 생각하고
loyal을 찍은 고객은 지속적으로 대형가전을 구매해 대행납품하는 업체 등.. 으로 생각할 수 있다

일단 소비자가 해당 카테고리에서 보편적으로 갖는 구매주기까지 고려해볼만 하다

 

 

실전문제 2

이제 아래의 쿼리를 글로 동작 방법에 대해 자세히 설명하고,

해당 SQL 쿼리의 작동 방식을 시각적으로 표현하여 제출 해주세요!

쿼리문은 너무 길어서 생략

order_details에서 카테고리, 하위 카테고리 별로 총 주문량, 총 수익, 총 매출을 구함

두번째 뷰에서 서브카테고리 중 단일판매비용과 단일판매액의 최댓값을 가져옴

마지막은 각 카테고리, 하위 카테고리별로 총 판매량, 총 수익, 총 판매액,
단일판매비용의 최댓값, 단일판매액의 최댓값을 가져왔는데 맹점이 존재한다고 생각한다

단일판매비용과 단일 판매액이 그 카테고리를 대표한다고 할 수 없다
같은 카테고리 내에서라도 성질이 상이한 것들끼리 있을 수 있다

판매액도 높고 판매비용도 높은 제품이 있을 수도 있고
판매액이 위 제품을 상회하지만 판매비용은 낮은 제품이 있을 수도 있다

두 제품이 다르지만 max로 가져온 값은 판매비용은 전자, 판매액은 후자를 가져오고
두 수치의 차이가 크지 않다면 해당 카테고리의 사업은 순수익이 그렇게 높지는 않다고 잘못 해석될 수 있다

후자의 경우인 상품만 팔게된다면 높은 수익을 가져올수도 있다

 

 

 

 

2. SQL 특강

1) join

(1) inner join - 교집합

(2) left join - 왼쪽 테이블 기준으로 일치 값만. 없으면 null

(3) right join - 위와 반대의 경우

(4) full outer join - 합집합

(5) cross join - 모든 경우의 수 만들기
     ex) (a, b) (1, 2, 3) -> a1, a2, a3, b1, b2, b3

 

 

2) 윈도우 함수

(1) over절
    : 윈도우 함수가 작동하는 행의 집합 또는 윈도우를 정의

(2) 이동평균 구하기
    : avg over 절에 인자 입력
      avg(칼럼) over(order by 정렬기준칼럼 rows between  과거row수 preceding and 미래row수 following)

(3) row_number
    : 고유한 순서 할당
      order by 에서의 기준값 중에 중복이 있을 수도 있기에 인수를 많이 추가하는 것이 바람직?

(4) rank
    : 동일한 값을 가진 항목에 동일 순위를 부여하고 다음 순위는 건너 뜀
      11 -> 3333 -> 7

(5) dense_rank
    : rank와 유사한데 다음 순서 안 건너 뜀
      11 -> 222 -> 3

 

 

3) 시계열 오퍼레이터

(1) lead
     : 시계열 에서의 리드 오퍼레이터
       lead(칼럼명, 단계수) over (partition by 칼럼 order by 칼럼)

(2) lag
    : 시계열에서의 레그 오퍼레이터
       lag(칼럼명, 단계수) over (partition by 칼럼 order by 칼럼)

 

 

4) view

(1) 생성
     : create view 이름 as
       select ~

(2) 용도
     자주 사용하는 서브쿼리나 통계량을 뷰로 지정하면 편리

(3) 수정
    : 뷰는 삭제 후 재정의가 원칙 -> delete view 이름

 

 

5) SQL 최적화

Indexing  사용 -> explain 함수를 통해 성능 분석 가능

(1) 정의
     : 인덱스란 데이터 검색 속도를 향상시키는 중요한 기능임

(2) 생성
    : create index 인덱스명 on 테이블명(칼럼명1, 2, ....)  <- 여러개 칼럼도 가능
      -> 이러면 인덱스로 지정된 칼럼을 기준으로 테이블이 저장됨

(3) 조건
     : 인덱스 선정 조건 = where 절에서 필터로 자주 사용하는 것

 

 

6) 좋은 SQL문

(1) 간결하고 명확한 select 쿼리
      : 모든 칼럼명 써주기 *쓰지 말고

(2) join시 명확한 테이블 별칭 사용

(3) 복잡한 쿼리를 단계적으로 나누어 작성
     : where having 등으로 한 쿼리를 길게 쓰지 말고
       서브쿼리를 통해 한단계 필터링 후 다음 다음 쿼리를 통해 마무리...

(4) 주석 사용하기
      -> 확장성 증가

 

 

7) SQL 관련 포트폴리오

혹자는 DBeaver창에서 쿼리문을 실행시키면 미니게임이 실행되도독 만들었다고 하셨다...

kaggle 데이터를 잘 정제하고 join 해보고 분석해보는 것이 중요할 듯 하다

 

 

 

개인과제는 혼자 해야해서 너무 힘들다...

조금 강도가 높더라도 팀 단위의 과제가 소통하는 맛도 있고 더 나은 듯 하다...