SQL 정리(개인과제 복습)(24.01.03)

5번

Q. order_details 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다. 이 테이블의 각 행은 주문 ID(order_id), 카테고리(Category), 그리고 주문 수량(Quantity)을 포함합니다. 이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는 모든 주문들을 찾아보세요.

 

▶ 답안(Ver.나)

select order_id, a.category, quantity, avg_quan
from order_details a left join
(
   select category, avg(quantity) avg_quan
   from order_details
   group by category
) b on a.category = b.category
where quantity > avg_quan
order by category, order_id

 

일단 내가 기존에 작성했던 쿼리문은 위와 같은데 내가 정말 제대로 문제를 이해하고 풀었는게 맞는지 확인하기위해서

나의 답안과 튜터님의 답안을 보지 않고 한 번 더 쿼리문을 작성해보았다.

 

문제의 요구사항은 order_details 테이블을 활용하여 각 카테고리별 평균 주문 수량을 초과하는 모든 주문을 찾으라는 것이다.

 

문제의 요구사항은 아래와 같다.

1. 카테고리별 평균 주문 수량 구하기
2. 평균주문수량과 주문 수량 비교하기

일단 카테고리별 평균 주문 수량을 구해야하는데

금번 쿼리 작성때는 with절을 이용하여 평균 주문 수량을 구하는 테이블을 만들어주었다.

with avg_table as
(
	select category, avg(quantity) avg_quantity
	from order_details od1
	group by category
)

 1) 이렇게 카테고리별 평균 주문 수량을 구하는 테이블을 만들어 준 후에

 2) 기존 order_details 테이블과 join시켜주고,

 3) '주문 수량 > 평균 주문 수량' 속성값들만 출력하게끔 쿼리를 작성했다.

with avg_table as
(
	select category, avg(quantity) avg_quantity
	from order_details od1
	group by category
)
select order_id, od2.category, quantity
from order_details od2 join avg_table on od2.Category = avg_table.category
where quantity > avg_quantity

 4) select절에 order_id, category, quantity가 출력되게끔 작성했고

 5) from절에서 order_details 테이블과 가상의 테이블인 avg_table을 join했다.

 6) 그리고 where절에 quantity > avg_quantity 를 입력함으로써 카테고리별 평균 주문 수량을 초과하는 주문들을 모두 출력하게끔했다.

 

처음 답안을 작성했을때는 from절 안에 평균 주문 수량을 구하는 서브 쿼리를 넣었는데

with절을 활용하니 가독성이 훨씬 좋아졌음을 느꼈다.

 

▶ 답안(Ver.튜터님)

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

튜터님의 경우 where절에 서브쿼리를 넣으셨다.

where절의 'quantity >' 뒤쪽에 평균 주문 수량을 구하는 서브쿼리를 넣음으로써

주문 수량과 평균 주문 수량을 비교할 수가 있다.

 

7번

Q. "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"

list_of_orders, order_details, 그리고 sales_target 세 개의 테이블이 있습니다.

list_of_orders 테이블은 주문 ID(order_id)와 주문이 이루어진 주(State)를,

order_details 테이블은 각 주문의 금액(Amount)과 이익(Profit)을,

sales_target 테이블은 각 카테고리별 매출 목표(Target)를 포함합니다.

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

각 주 내에서 주문의 매출 순위를 결정하세요.

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

 

▶ 답안(Ver.나)

select order_id,
	   state,
	   category,
	   amount,
	   profit,
	   total_amount,
	   total_profit,
	   avg_target,
	   rank() over(partition by state order by amount desc) ranking,
	   if(total_amount>avg_target*0.5, "True", "False") "T/F"
from
(
	with total_table as
	(
		select state, sum(amount) total_amount, sum(profit) total_profit
		from list_of_orders a left join order_details b on a.order_id = b.order_id
		group by State
	)
	, avg_table as
	(
		select category, avg(target) avg_target
		from sales_target
		group by category
	)
	select a.order_id, a.state, b.category, amount, profit, total_amount, total_profit, avg_target
	from list_of_orders a left join order_details b on a.order_id = b.order_id left join total_table c on a.state = c.state left join avg_table d on b.category = d.category
) a
where amount is not null

고급 난이도의 문제였기때문에 문제를 푸는데 굉장히 많은 시간이 소요됐다.

지금의 나로서는 맞추고 못맞추고를 떠나서 값을 출력해냈다는것만으로도 만족스럽긴했는데

튜터님과 나의 답안을 비교해봤을때 확실히 튜터님의 답안이 깔끔했다.

 

7번 문제의 경우 튜터님의 쿼리문을 읽어보면서 따라서 한 번 작성, 쿼리 보지않고 스스로 한 번 작성해보았다.

우선 문제에서 사용해야하는 테이블을 list_or_orders, order_details, sales_target 세가지 테이블이다.

문제의 요구사항은 다음과 같다.

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

1번과 2번 항목은 list_of_orders 테이블과 order_details 테이블을 join해서,

3번의 평균 매출 목표는 sales_target 테이블에서 구할 수 있다.

 

우선 평균 매출 목표를 구하는 테이블을 구해보자

서브쿼리를 활용할 수도 있지만

깔끔하고 가독성 좋게 하기위해 with절로 작성했다.

with avg_table as
(
	select category, avg(target) avg_target
	from sales_target st 
	group by category
)

카테고리별 평균 매출 목표를 구해야하므로

 1) select절에 category와 avg(target)을 적어주고, category로 그룹화한다.

 

 2) 그런 다음 1번과 2번 항목에서 요구하는 값들을 구해줌과 동시에 위에서 만든 avg_table을 함께 join시켜준다.

with avg_table as
(
	select category, avg(target) avg_target
	from sales_target st 
	group by category
)
select loo.order_id,
	   loo.State,
	   sum(od.amount) total_amount,
	   sum(od.profit) total_profit,
	   rank() over(partition by loo.state order by sum(od.amount) desc) ranking,
	   case when sum(od.amount) >= (at.avg_target / 2) then 'Met 50% Target'
	   		else 'Below 50% Target' end Target_comparison
from list_of_orders loo join order_details od on loo.order_id = od.order_id join avg_table at on od.Category = at.category
group by loo.order_id, loo.State, at.avg_target

list_of_orders, order_details, avg_table 모두 특정 테이블에서의 속성값이 더 많지는 않기때문에

left, right outer join을 할 필요없이 (inner)join을 해줬다.

 3) 그리고 난 후 select 절에 문제에서 요구했던 컬럼들(order_id, state, total_amount, total_profit, ranking, target_comparison)을 적어준다.

 

문제를 풀면서 유의해야할 점이 있는데

select절에 집계함수 외 다른 컬럼명을 넣고싶으면 group by에 해당 컬럼명도 적어야 한다는 점이다.

 

group by에 컬럼명을 적어주지 않으면 보는 사람이 혼란스럽기도하고,

엔진이 아래와 같은 에러를 내기때문이다.

SQL Error [1055] [42000]: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ecommerce.loo.State' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

 

문제를 풀다 보면 아래와 같이 list_of_orders, order_details, sales_target 모두 한번에 join해서 풀면 안 되냐고 생각할 수 있다.

select loo.order_id, state, sum(od.amount), sum(od.profit), avg(st.target),
	   rank() over(partition by loo.state order by sum(od.amount) desc) ranking,
	   CASE WHEN SUM(loo.Amount) >= avg(st.target) / 2 THEN 'Met 50% Target'
       ELSE 'Below 50% Target' END AS Target_Comparison
from list_of_orders loo join order_details od on loo.order_id = od.order_id join sales_target st on od.category = st.Category 
group by loo.order_id, loo.state

그런데 문제 요구사항 3번 항목을 잘 읽어보면 '카테고리의' 라고 적혀있다.

다시말해 카테고리로 group화해야하는데

1번과 2번 항목을 구할 때는 '주'별 '주문'의 1) 총 금액 2) 총 이익 3) 매출 순위 를 구해야하기때문에(다시말해 group by에 들어가는 컬럼명이 다르기 때문에) 평균 매출 목표를 구하는 테이블은 별도로 빼야한다.

▶ 답안(Ver.튜터님)

WITH Average_Targets AS (
    SELECT 
        Category, 
        AVG(Target) AS Avg_Target
    FROM 
        sales_target
    GROUP BY 
        Category
)
SELECT 
    lo.order_id, 
    lo.State, 
    SUM(od.Amount) AS Total_Amount, 
    SUM(od.Profit) AS Total_Profit,
    RANK() OVER (PARTITION BY lo.State ORDER BY SUM(od.Amount) DESC) AS State_Rank,
    CASE 
        WHEN SUM(od.Amount) >= at.Avg_Target / 2 THEN 'Met 50% Target'
        ELSE 'Below 50% Target'
    END AS Target_Comparison
FROM 
    list_of_orders AS lo
    JOIN 
    order_details AS od ON lo.order_id = od.order_id
    JOIN 
    Average_Targets AS at ON od.Category = at.Category
GROUP BY lo.order_id, lo.State, at.Avg_Target;

 

8번

Q.

당신은 **list_of_orders**와 order_details 두 테이블을 가지고 있으며, 이를 통해 고객별 평생 가치를 분석하고자 합니다.

list_of_orders 테이블은 고객 이름(CustomerName), 주문 날짜(order_date), 주문 ID를 포함하고,

order_details 테이블은 각 주문의 금액(Amount)과 수익(Profit)을 포함합니다.

고객별로 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산하고, 이를 기반으로 고객의 평생 가치를 분석하세요.

 

이 문제도 고급 난이도의 문제로 문제를 푸는데 꽤 많은 시간이 소요됐다.

이번 문제에서 활용해야하는 테이블은 list_of_orders, order_details 두 개다.

 

문제의 요구사항은 다음과 같다.

1. 고객별 총 구매액, 총 주문 횟수, 주문간 평균 일수 계산
2. 이를 기반으로 고객평생가치 분석하기

고객평생가치라는 개념이 나왔다.

8번 문제의 경우 답이 맞냐 틀리냐보다는 아래 두 가지에 의미를 뒀다.

1. 포기하지 않고 끝까지 쿼리문 작성해서 결과값 도출하기
2. 고객평생가치를 도출하기위한 개념들 숙지하기

그렇다보니 튜터님의 쿼리와 내가 작성한 쿼리가 많이 다를수밖에 없어서

튜터님의 쿼리를 보며 이해하는 시간을 가지려고한다.

 

▶ 답안(Ver.나)

select CUSTOMERNAME, round(APV * APFR * ACL) CLTV
FROM
(
	select customername, sum(amount) / count(a.order_id) APV,
	(
		select COUNT(ORDER_ID) / COUNT(distinct CUSTOMERNAME) APFR
		from list_of_orders
	) APFR,
	(
		select sum(lifespan) / count(distinct customername) ACL
		from
		(
			select datediff(max(str_to_date(order_date, '%d-%m-%Y')), min(str_to_date(order_date, '%d-%m-%Y'))) lifespan, customername
			from list_of_orders loo
			group by customername
		) ACL_TABLE
	) ACL
	from list_of_orders A join order_details B on A.order_id = B.order_id
	group by CustomerName
) A
group by CUSTOMERNAME
order by CLTV desc

일단 내가 작성한 쿼리를 간략하게 설명해보겠다.

고객평생가치를 뜻하는 CLTV를 구하기위해서는 APV, APFR, ACL를 먼저 구해야하는데

APV는 평균구매가치를,

APFR은 평균구매빈도를,

ACL은 평균고객수명을 의미한다.

 

각각의 산식은 아래와 같다.

APV(평균구매가치) = 일정 기간 동안의 총 매출 / 일정 기간 동안 발생한 구매 횟수

APFR(평균구매빈도) = 일정 기간 동안 구매한 횟수 / 해당 기간 동안 구매한 전체 고객 수

ACL(평균 고객 수명) = 몇 년에 걸쳐 방문하고있는지 / 해당 기간 동안 구매한 전체 고객 수

 

APV, APFR, ACL을 구하기위한 서브쿼리를 FROM절 안에 넣고,

바깥 SELECT절에서 APV, APFR, ACL을 곱한 CLTV를 구했다.

이 때 CLTV는 고객평생가치로 고객별로 구해야해서 customername으로 그룹화해줬다.

 

▶ 답안(Ver.튜터님)

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;

튜터님의 쿼리문 살펴보자

 

튜터님의 쿼리를 보면 굉장히 복잡해보일수도있는데

조금만 집중해서 보면 한 눈에 들어오는 쿼리임을 알 수 있다.

 

customer_order_details, order_intervals, customer_stats 3개의 with절을 만들고

마지막 with절을 가져와서 select절에 출력하고자하는 컬럼들을 출력했다.

여기서 3개의 with절이 아무런 연관성이 없는 것이 아니라 순차적으로 몸집을 키워나가는 것을 알 수 있는데

무슨 말이냐하면 가장 먼저 customer_order_details를 만들고,

두번째 with절인 order_intervals 테이블을 작성할 때는 앞서 만들어 놓은 customer_order_details를 활용한다.

마지막 with절인 customer_stats 테이블을 작성할 때는 두번째 테이블인 order_intervals 테이블을 활용한다.

 

첫번재 with절

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
)

 

우선 list_of_orders와 order_details 테이블을 join한다.

그런 다음 customername, order_date, amount, rn(고객이름 기준 order_date의 순서)를 구해준다.

여기서 order_date의 경우 기존 문자에서 날짜로 변경해줘야하므로

str_to_date함수를 적용해준다.

 

두번째 with절

,
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
)

 

두번째 with절에서는 order_intervals라는 테이블명처럼 날짜간의 간격을 구해주는데

select절을 보면 LAG라는 함수가 적용된 것을 볼 수 있다.

LAG함수란 특정 날짜의 이전 날짜를 구하는 함수이며 구조는 아래와 같다.

lag(확인하고싶은 컬럼명 or 속성값, n, 대신 입력할 값) over(partition by 기준이 되는 컬럼 order by 확인하고싶은 컬럼명)

확인하고싶은 컬럼명(or 속성값)의 n일 전(n이 입력되지 않으면 default값인 1일 전의 값을 구하게 됨) 값을 구할 수 있다.

만약 n일 전의 값이 없다면 '대신 입력할 값'이 출력된다.

 

그리고 기준이 되는 컬럼을 기준으로 그룹화시키고, 확인하고싶은 컬럼명의 속성값들이 오름차순(or 내림차순)으로 정렬된다.

 

이번 문제에서의 경우 order_date 그러니까 이전 주문 날짜를 확인하고싶은 것이기때문에

LAG(order_date)를 적어주고,

고객명을 기준으로 날짜순서로 오름차순 정렬하고자하기에

PARTITION BY CustomerName ORDER BY order_date를 적어준다.

 

쿼리문을 보면 LAG함수만 있는게 아니라 DATEDIFF 함수도 함께 적혀있는데

order_date와 order_date 이전 날짜의 간격을 구하고자하기때문이다.

 

where절에서는 각 고객별 주문 순서가 1을 초과하는 고객들만을 추려줬는데

이렇게하는 이유는 각 고객의 첫 번째 주문은 이전 주문이 없기 때문에 이전 주문과의 일수 차이를 계산할 수 없기때문이다.

 

세번째 with절

,
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
)

세번째 with절은 앞서 만들어놓은 customer_order_details테이블과 order_intervals 테이블을 left join하여 만들어준다.

left join을 하는 이유는 order_intervals테이블에는 없는 속성값들이 customer_order_details 테이블에는 존재하기때문이다.

왜 그럴까? 앞서 두번째 with절 order_intervals를 만들때 주문 간격이 2 이상인 고객들만을 추리는 where절을 작성했기때문이다.

 

세번째 customer_stats테이블에서는 고객이름을 기준으로 그룹화한 후

'고객이름', '총 매출액', '총 주문건수', '주문 간격의 평균' 을 구해준다.

 

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;

고객평생가치를 구하는 방식은 굉장히 다양하다고 알고있다.

나의 경우 앞서 언급했던 APV, APFR, 그리고 ACL을 활용하여 고객평생가치를 계산하였는데

튜터님의 경우 아래 공식을 사용하셨다.

CLV = (평균주문금액)×(평균구매빈도율)×(평균고객관계지속기간)

평균주문금액 = 고객별 총 매출액 / 고객별 총 주문 횟수

평균구매빈도율 = 고객별 총 주문 횟수 / 관찰 기간(주문 간격의 평균)

평균고객관계지속기간 = 1년

 

그래서 위 쿼리문을 보면

평균주문금액을 구하기위해

cs.Total_Amount / cs.Total_Orders AS Avg_Order_Value

 

구매빈도를 구하기위해

cs.Total_Orders / NULLIF(cs.Avg_Days_Between_Orders, 0) AS Purchase_Frequency

쿼리를 작성했다.

 

구매빈도 쿼리에서 NULLIF라는 함수가 나오는데

구조는 다음과 같다.

NULLIF(expression1, expression2)

expression1과 expression2가 같으면 결과는 NULL이 되고 그렇지 않으면 expression1의 값이 반환된다.

문제에서 이 NULLIF함수를 사용한 이유는 평균주문간격이 0인 경우가 있으면 구매빈도를 계산할 수없기 때문이다.

 

이렇게 평균주문금액과 구매빈도를 계산해준 후

CLV를 구하기위해서 평균주문금액 * 구매빈도 * 고객관계지속기간을 해주면 된다.