📖TIL(Today I Learned)

TIL 5일 차 [Subquery와 Join을 이용하여 복잡한 연산을 한 번에 결합]

highcat 2024. 11. 13. 19:34

 

어느덧 4주 차에 접어들었는데 복습 위주로 공부해야
이번 주 안으로 5주 차까지 끝낼 수 있을 것 같다.

저번 시간에 배운 포맷 변경과 조건문도 한 번 더 정리해 놨다.
문자를 변경하고 싶을 때

  1. REPLACE : 지정한 문자를 다른 문자로 변경
  2. SUBSTRING : 특정 문자만 추출
  3. CONCAT : 여러 문자를 합하여 포맷팅

 

하나의 조건문(if)과 여러 조건문을 넣을 때

  1. IF  
          if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

   2. CASE WHEN END 
          case when 조건1 then 값(수식)1
                   when 조건2 then 값(수식)2
                   else 값(수식)3 end 

 

 

 

 

 

 

여러 번의 연산을 한 번의 SQL 문으로 수행하기

 

 

- Subquery 가 필요한 경우

 

  • 여러번의 연산을 수행해야 할 때
→ 수수료를 부과할 수 있는 시간을 구하고
→ 구해진 시간에 주문 금액별로 가중치를 주고
→ 가중치를 적용한 결과로 최종 예상 배달비를 계산할 때

 

 

  • 조건문에 연산 결과를 사용해야 할 때
→ 음식 타입별 평균 음식 주문금액 따라 음식비 상/중/하 를 나누고 싶을 때

 

 

  • 조건에 Query 결과를 사용하고 싶을 때
→ 30대 이상이 주문한 결과만 조회하고 싶을 때

 

 

 

Subquery 문의 기본 구조

/* subquery*/ 는 안 써도 됨(그냥 어딨는지 표시용)

 

 

 

주문 테이블에서 음식 주문 시간이 25분 보다 초과한 시간을 가져오기

 

from에 괄호()를 넣고 a 까지

 

 

 

 

User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

 

음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기

(수수료 구간 -

~5000원 미만 0.05%

~20000원 미만 1%

~30000원 미만 2%

30000원 초과 3%)

 

 

▶ a 괄호 안에 계산된 값을 이용하여, 밖에 있는 b 괄호의 case 문에 사용

 

 

 

음식점의 지역과 평균 배달시간으로 segmentation 하기

평균적으로 20~30분이 소요

 

20~30분을 기준으로 나눔 / 한글로 "시도" 를 넣으면 안 돼서 city로 변경 (위에 "시도"는 큰 따옴표 빼면 가능)

 

 

 

복잡한 연산을 Subquery 로 수행하기

 

 

음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고,
주문수량과 음식점수 별 수수료율을 산정하기

    음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5%
    음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8%
    음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
    음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%

 

 

 

먼저 '음식 타입별 지역별 총 주문수량과 음식점 수를 연산'

count_res = 음식점 수 / total_quantity = 주문 총 수량

 

 

 

 

100% = 1.00 / 0.5% = 0.005

음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

     할인조건 수량이 5개 이하 → 10%
     수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
     이 외에는 일괄 1%

 

먼저 '음식점의 총 주문수량과 주문 금액을 연산'

sum(quantity) = 주문 수량 합계 / sum(price) = 주문 금액 합계

 

 

 

 

위 쪽 SELECT restaurant_name,

                         sum_price,

                         sum_quantity,
까지 넣어줘야 값이 아래로 쭉 나열 된다

 

 

 

 

 

 

 

 

 

 

 

▼          JOIN            ▼

 

 

 

 

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

 

 

  • 주문 정보에다가 고객 정보의 이메일을 가져와야 할 때
  • 두 테이블 모두 공통으로 가지고 있는 컬럼을 기준으로 값을 가져온다.

▼

  • 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있게 한다.

 

 

LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미

INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회


                       (14번)
이메일이 없는 번호까지 모두 조회



이메일이 있는 번호까지만 모두 조회

 

 

 

  • JOIN 의 기본 구조

 

주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기

 

food_orders 와 customers 를 customer_id 기준으로 합친 뒤
select 으로 보고 싶은 값을 조회한다.

b 값 중, a에 없는 것은 포함 X

 

 

 

JOIN 으로 두 테이블의 데이터 조회하기

 

    한국 음식의 주문별 결제 수단과 수수료율을 조회하기
  (조건 : where)

       조회 컬럼 : 주문 번호,
                         식당 이름,
                         주문 가격,
                         결제 수단,
                         수수료율

*결제 정보가 없는 경우도 포함하여 조회 = left join

 

결제 정보가 없는 경우까지 포함하여 조회

 

고객의 주문 식당 조회하기

조회 컬럼 : 고객 이름,
                  연령, 성별,
                  주문 식당

*고객명으로 정렬, 중복 없도록 조회

 

*고객명으로 정렬 ▶ order by c.name
NULL 값을 제거하려면

where c.nameis not null
을 넣어준다

 

NULL 없이 순서대로 뜬다.

 

  • 중복 없도록 조회까지 하려면?

DISTINCT 를 넣어준다 ▼

강도연 / 49세 / Blue Ribbon Sushi 값이 하나만 조회됨

JOIN 으로 두 테이블의 값을 연산하기
주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

조회 컬럼 : 주문 번호,
                  식당 이름,
                  주문 가격,
                  수수료율,
                   수수료

*수수료율이 있는 경우만 조회

가격 x 수수료율 = 수수료(total_vat로 지음)

 

4주 차 문제

 

 

 

  • 정답 제출

나이(age)는 then을 이용해보려고 했는데 안 된다.

그리고 Group by가 왜 a 밖이 아닌 안에 뒀는지도 궁금하다..

튜터님께 여쭤봐야할 것 같다.

 

 

 

이번 문제 생각보다 어려웠다..😥





4주 차는 이해하는데 3일 정도 쓴 것 같은데 생각보다 오래 걸렸다..
그리고 항상 Group by 절에서 막히는 것 같아 좀 더 알아봐야겠다.

마지막 5주 차는 얼마나 걸릴까..

 

24.11.14 추가 메모 : 계산하는 함수가 들어가면 group by 절이 들어가야함