[과제] SQL 개인 과제 : 복습필요

 

도전한 점

 

 

list_of_orders 주문목록	order_details 주문내용	sales_target 판매목표
order_id 주문아이디 order_date 주문날짜 CustomerName 고객이름 State 주소 City 도시	order_id 주문아이디 Amount 주문금액 Profit 수익 Quantity 주문수량 Category 카테고리 sub_category 서브카테고리	month_of_order 한달주문 Category 카테고리 Target 목표고객

 

01 데이터 전처리

2024년도 기준 [MySQL 설치하고 DBeaver에 연결하기] 초보자용 가이드북 (tistory.com)

2024년도 기준 [MySQL 설치하고 DBeaver에 연결하기] 초보자용 가이드북

 

Q. 새미콜론으로 범벅된 쿼리문을 한번에 넣어서 실행하면 실행 기준이 어떻게 되나?

 

A. 새미콜론으로 이루어진 하나의 쿼리문 덩어리마다 실행된다.

 

Q. 소문자로 된 테이블이 있다고 뜨고 자꾸 RENAME TABLE이 안되는 이유는?

 

A. 튜터님이 후에 파일을 수정해서 굳이 테이블 명을 수정할 필요가 없어진게 이유였다.

 

01-1 질의응답

 

a. List_of_Order에 비어있는 셀을 처리하는 방법은 2가지이다.
#데이터 조건으로 필터링하기
SELECT *
FROM Ecommerce.list_of_orders 
WHERE `Order Date` != '';

#데이터 삭제하기 : DELTE

DELETE

FROM Ecommerce.list_of_orders 
WHERE `Order Date` = '';

 

b. SQL 는 위부터 순서대로 실행되나요?

SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by 순입니다.
다시 말하면 Select 에서 alis 로 지정한 컬럼은 순서가 앞인 where 조건절 등에서는 작동하지 않습니다. (일반적으로)

 

c. where와 Having의 차이는 무엇인가요?

From 절에서 불러온 데이터를 행마다 True or False 판별을 통해 데이터를 필터링(혹은 조건 적용) 하는 것이 Where절의 존재 이유입니다. Having은 From 절 이후 집계된 테이블을 필터링(조건 적용) 하는 것 입니다.

 

d. mysql에서 컬럼명이 스페이스 바로 띄워져 있는 경우 어떻게 불러오나요?

select order date from ~~ 처럼  물결표(~)위치에 있는 quote(`)를 사용하여 감싸주면 됩니다. 따옴표(')와 다릅니다!

 

c. 고객 평생 가치란? 여기에서 CLV = (평균 구매액) × (평균 구매 빈도) × (고객 유지 기간)

https://www.thedigitalmkt.com/how-to-calculate-customer-lifetime-value/

고객 생애 가치(CLV) 계산 방법과 스타벅스의 예시 | Digital Marketing Curation

 

d. 고객 평생 가치를 구하라는게 고객당 총구매액/총 주문 횟수/평균 주문 일수를 활용해서 계산하여
고객별 가치액 (개별) vs 전체고객의 가치액의 평균 -> 개별인지 평균인지 -> 개별이다.

 

e. where 1=1 은 주로 주석 처리 처리 할때 많이 사용한다.

 

02 초급 과제

 

문제 1. "최소 주문 날짜 찾기"

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

 

 

Q. 왜 date_format이든 order by 1번이든 왜 안된다냐? (위에 보니 문자형임.) 그래서 새로 만듦.

 

select substr(order_date, 7) 'year',
       substr(order_date, 4, 2) 'month',
       substr(order_date, 1, 2) 'date'
from list_of_orders
order by 'year', 'month', 'date'
limit 1

 

해설 : 단순 문자형 데이터인지 정렬이 안되어서 문자열을 뽑고 년도, 월, 일 순으로 정렬해서 limit 1값으로 잘랐다.

 

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

 

order_details 테이블에는 다양한 주문들의 세부 정보가 저장되어 있습니다. 각 주문은 특정 'Category' 에 속하며, 각 주문의 'Amount' 는 그 주문의 매출 금액을 나타냅니다. 이 테이블을 사용하여 각 카테고리별 총 매출 금액을 계산하세요.

select category,
       sum(amount) 
from order_details
group by 1

 

해설 : 카테고리 별로 그룹화 하여 주문금액의 합계를 구했다.

 

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

 

두 개의 테이블 order_details 와  list_of_orders 가 있습니다.  order_details 테이블은 각 주문의 세부사항을 포함하고 있으며, list_of_orders 테이블은 주문에 대한 고객의 이름과 주문 ID를 포함합니다. 각 고객별로 총 구매액을 계산하여, 구매액이 가장 높은 상위 5명의 고객을 찾아보세요.

select a.order_id,
       sum(amount)
from list_of_orders a join order_details b on a.order_id=b.order_id
group by 1
order by 2 desc
limit 5;

 

해설 : 주문 아이디 별로 그룹화하고 총주문금액의 합계를 구해서 전체를 총주문금액 내림차순으로 정렬한 다음 상위 5개만 출력하였다.

 

03 중급 과제

 

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

 

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

 

Q. 문제에서 3개 이상이란 말은 quantity 인가? count(order_id)인가?

Q. 문자 테이블명을 다시 문자화 시키면 왜 저렇게 반복되어서 나옴?

select 고객이름, 총주문횟수, 총구매액
from
(
select a.customername "고객이름",
       count(a.order_id) "총주문횟수",
       sum(amount) "총구매액",
       sum(quantity)
from list_of_orders a join order_details b on a.order_id=b.order_id
group by 1
having sum(quantity) >= 3
order by customername
) a

 

＊메인쿼리에 '고객이름', '총주문횟수', '총구매액'  하면 이상하게 나온다. 조심하기.

 

해설 : 고객이름 별 총 주문수량의 합계가 3개 이상인 조건이 들어가야해서 일단 메인 두 테이블을 inner join 시키고 (null 지움) 서브 쿼리에 sum(quantity)를 띄워서 groupby>having 한 다음에 메인 쿼리에서 총주문수량만 빼서 출력했다.

 

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

 

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

select category,
       avg(quantity)
from order_details
group by 1 -> 막힘

 

Q. 모든 주문 건수를 찾을 건데 여기에 어떻게 그룹화 없이 카테고리별로 평균금액 조건을 줄 수 있지? -> 그룹화를 해버리면 서브 쿼리로의 역할을 못하게 되니까 이게 문제였다.

A. 보류하고 6번 풀다가 답이 생각났다. 내 블로그 누적합 1번 부분을 참고해서 응용 해보면 될 것 같다.

 

select order_id, category, quantity
from
(
select order_id,
       category,
       quantity,
       avg(quantity) over(partition by category) avg_quantity
from order_details
) a 
where quantity > avg_quantity

 

해설 : 중요한 건 그룹화 없이 평균을 뽑아내는 거였다. 이게 안되어서 6번으로 잠깐 넘어갔는데 누적합 노트필기에서 응용할 수 있는 해답을 찾았다. 카테고리 별 평균 값을 낸 서브쿼리를 만들고 메인쿼리에 조건을 줘서 select 1, 2, 3만 하면 됨.

 

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

 

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

select order_id,
       category,
       amount,
       rank() over(partition by category order by amount desc) '매출액순위',
       sum(amount) over(partition by category order by amount desc) '누적매출액'
from order_details

 

해설 : 문제가 '각 카테고리 내에서 각 주문의 매출액 순위와 그 카테고리 내의 누적 매출액을 계산하세요.' 이니까 카테고리 별로 순위를 매길 수 있는 'rank over order by' 와 누적합을 구하는 'sum over order by'를 이용해서 category 별로 파티션을 나눈 다음에 주문금액이 가장 많은 순으로 정렬했다. order_id는 모두 살려서 order by 따로 없이 출력한다.

 

04 고급 과제

 

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

 

list_of_orders, order_details, 그리고 sales_target 세 개의 테이블이 있습니다. list_of_orders 테이블은 주문 ID(order_id)와 주문이 이루어진 주(State)를, order_details 테이블은 각 주문의 금액(Amount)과 이익(Profit)을, sales_target 테이블은 각 카테고리별 매출 목표(Target)를 포함합니다. 이 테이블들을 사용하여 각 주별로 주문의 총 금액과 이익을 계산하고, 각 주 내에서 주문의 매출 순위를 결정하세요. 또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.

 

＊세 개이상의 테이블 join은 join을 이용해서 따로 콤마(,)나 AND 없이 join 만으로 연결한다. (null 때문에 inner함.)

select *
from list_of_orders a left join order_details b on a.order_id=b.order_id
                      left join sales_target c on c.category=b.category

 

Q. 각 주문의 총 금액이 여기서 해당 카테고리의 평균 매출 목표에서 state인가? 뭐지? 이상한데?

 

select order_id, state, sum_amount, sum_profit, rank_amount,
       if('avg_target/2' < sum_amount, '달성', '미달') "평균50%달성여부"
from
(
select a.order_id,
       state,
       sum(amount) over (partition by state) sum_amount,
       sum(profit) over (partition by state) sum_profit,
       rank() over (partition by state order by amount) rank_amount,
       avg(target) over(partition by state) / 2 'avg_target/2'
from list_of_orders a join order_details b on a.order_id=b.order_id
                            join sales_target c on c.category=b.category
) a

 

해설 : (그룹화 없이) 주 별로 총주문금액, 총이익, 주 내에서 총주문금액 순위, sum_amount가 (avg_target)/2 넘었는지 조건을 주고 출력하였다. 찜찜한 게 총주문금액이 다 같아서 랭크가 겹친다는 것이다. 좀 이상하다.

 

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

 

당신은 list_of_orders와 order_details 두 테이블을 가지고 있으며, 이를 통해 고객별 평생 가치를 분석하고자 합니다. list_of_orders 테이블은 고객 이름(CustomerName), 주문 날짜(order_date), 주문 ID를 포함하고, order_details 테이블은 각 주문의 금액(Amount)과 수익(Profit)을 포함합니다. 고객별로 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산하고, 이를 기반으로 고객의 평생 가치를 분석하세요.

 

 

Q. 주문 간 평균 일수는 뭐지, 고객의 평생 가치를 분석하는 건 무지?

 

05 실전 과제

 

보류

 

좋았던 점

• 답이 뭔지는 몰라도 7번까지는 그냥 풀어서 좋았는데 8번부터는 대체 뭐지?

 

시도할 점

• 풀이 복습하고 부족한 점 보충하기