[2025.01.20] 사전캠프 6일차 SQL_If,else,case when then end,실습문제

우선,오늘 공부를 하기 전에 저번 시간에 이메일 도메인을 추출하는 쿼리식에 대한 다른 접근을 알게되어, 요약해보겠다.

SQL에서 SUBSTR(또는 SUBSTRING)과 SUBSTRING_INDEX의 차이점

1. SUBSTR (또는 SUBSTRING)

• 기능: 문자열의 특정 위치에서 지정한 길이만큼 잘라냄.
• 사용 상황: 문자열의 시작 위치와 길이를 알고 있을 때 사용.

SUBSTR(string, start_position, length)

• string: 문자열.
• start_position: 시작 위치 (1부터 시작).
• length: 잘라낼 길이 (선택 사항).

---

2. SUBSTRING_INDEX

SUBSTRING_INDEX(string, delimiter, count)

• string: 문자열.
• delimiter: 나눌 기준이 되는 구분자(예: ,, -, : 등).
• count: 구분자를 기준으로 가져올 부분의 개수.
  • 양수: 왼쪽부터 가져옴.
  • 음수: 오른쪽부터 가져옴.

예시

SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 2); -- 결과: 'apple,banana'
SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1); -- 결과: 'cherry'

---

주요 차이점

기능	SUBSTR	SUBSTRING_INDEX
용도	특정 위치에서 길이만큼 잘라냄	구분자를 기준으로 특정 부분 반환
기준	위치와 길이 (start_position, length)	구분자 (delimiter)와 나눌 개수 (count)
구분자 처리	없음	구분자를 기준으로 문자열을 나눔
예제	SUBSTR('Hello World', 7, 5) -> 'World'	SUBSTRING_INDEX('a,b,c,d', ',', 2) -> 'a,b'

---

선택 기준

• 위치와 길이를 기준으로 자르려면 → SUBSTR
• 구분자를 기준으로 특정 부분을 추출하려면 → SUBSTRING_INDEX

select substring(email, 10) "도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

강의에서는 실습코드를 이렇게 알려주셨었는데, substring_index를 쓰는게 훨씬 나을 듯 하다🔻

SELECT substring_index(email, '@',  -1) "도메인", count(1) "고객 수", avg(age) "평균 연령"
FROM customers
GROUP BY 1
ORDER BY 2 DESC

.
.
.
3-4.

#1. 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정

select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

#2. ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

select addr "원래 주소",
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

#3. 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

.
.

❓🔻음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정

❓🔻10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

->⭐30세 미만이니까 between 10 and 29라고 한 것, ⭐when절 조건 병렬한 것,⭐연령+성별 각각 매치한 것, ⭐case when then end 이후 name, age, gender도 확인차 같이 불러온 것 잘 기억해두기!
(나이와 성별로 그룹 나누기라고 모호하게 써있어서, 나이를 어떻게 나누라는 건지 문제에서 더 구체적으로 알려줬으면 좋겠다는 생각이 듦...)
.
.
🔶 [실습] 음식 단가, 음식 종류 별로 음식점 그룹 나누기
(Korean = 한식
Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식
그외 = 기타)
(가격 = 5000 미만, 5000 이상 15000 미만, 15000 이상)

select restaurant_name,
       price/quantity "단가",
       cuisine_type,
       order_id,
       case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'
            when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'
            when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'
            when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'
            when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'
            when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'
            when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'
            when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'
            when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"
from food_orders

not in 대신 else를 써도 될까? 라는 궁금증이 들었다!!!

SELECT restaurant_name,
       price/quantity "단가",
       cuisine_type,
       order_id,
       CASE 
            WHEN (price/quantity < 5000) AND cuisine_type = 'Korean' THEN '한식1'
            WHEN (price/quantity BETWEEN 5000 AND 15000) AND cuisine_type = 'Korean' THEN '한식2'
            WHEN (price/quantity > 15000) AND cuisine_type = 'Korean' THEN '한식3'
            WHEN (price/quantity < 5000) AND cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') THEN '아시아식1'
            WHEN (price/quantity BETWEEN 5000 AND 15000) AND cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') THEN '아시아식2'
            WHEN (price/quantity > 15000) AND cuisine_type IN ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') THEN '아시아식3'
            ELSE 
                CASE 
                    WHEN (price/quantity < 5000) THEN '기타1'
                    WHEN (price/quantity BETWEEN 5000 AND 15000) THEN '기타2'
                    WHEN (price/quantity > 15000) THEN '기타3'
                END
       END "식당 그룹"
FROM food_orders;

결과는 똑같이 나온 듯하다!
✅다만, ELSE를 사용할 때는 포함되지 않은 데이터를 처리하는 디폴트 조건이므로, 논리적 오류가 없는지 반드시 검토!

조건문으로 서로 다른 식을 적용한 수수료 구해보기

- 1) [실습] 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)

(지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음
 시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)

ㅋㅋㅋㅋ처음문제보고 잠시 멍... 결국 혼자서 못해냈음.

곱하는게 없음에 1을 넣을 생각....! 못했음,!!!!

💡지역에 관한 내용은 if로 넣었어야 했다.
💡## 문제 접근 커멘트

1. 기본 정보 추출

   • 필요한 정보: 식당 이름 (restaurant_name), 주문 번호 (order_id), 배달 시간 (delivery_time), 가격 (price), 주소 (addr).

2. 수수료 계산 조건

   • 시간 조건:
     • 배달 시간이 25분 초과 30분 이하 → 음식 가격의 5%.
     • 배달 시간이 30분 초과 → 음식 가격의 10%.
   • 지역 조건:
     • 서울일 경우 계산된 수수료에 1.1배를 곱함.
     • 기타 지역은 추가 계산 없음.

3. 구현 방법

   • CASE 문을 사용하여 시간 조건과 지역 조건에 따라 수수료를 계산.
   • IF 함수로 지역 조건 처리:
     • 서울(addr LIKE '%서울%')이면 1.1, 아니면 1을 반환.

4. 쿼리 구조 요약

   • 기본 SELECT 구문에 CASE 문과 IF 함수를 조합하여 수수료 계산.
     최종답안

select restaurant_name,
       order_id,
       delivery_time,
       price,
       addr,
       case when delivery_time>25 and delivery_time<=30 then price*0.05*(if(addr like '%서울%', 1.1, 1))
            when delivery_time>30 then price*1.1*(if(addr like '%서울%', 1.1, 1))
            else 0 end "수수료"
from food_orders

🔴여기서 between 26 and 30 then price~ 써줘도 됨 !!!!!

.
.

-2) [실습] 주문 시기와 음식 수를 기반으로 배달할증료 구하기

아까 구한 식을 활용하여, 혼자 이것저것 해봤다

다른 quantity, price 등의 항목은 빼고 계산식만 만들어보았다.
💡if(()) 괄호 잘 닫기 !
💡case when then 병렬 쓰면 end 닫기 !

solution 1

select order_id,
       price,
       quantity,
       day_of_the_week,
       if(day_of_the_week='Weekday', 3000, 3500)*(if(quantity<=3, 1, 1.2)) "할증료"
from food_orders

이렇게 If 만을 써서 병렬로 묶거나

solution 2

select case when day_of_the_week='Weekday' then 3000*if(quantity>3,1.2,1)
when day_of_the_week='Weekend' then 3500*if(quantity>3,1.2,1)
 end "배달할증료",
 order_id,
       price,
       quantity,
       day_of_the_week
from food_orders

case와 if를 둘 다 사용해서 쿼리식을 만들 수 있었다.

.
.

내일 이어서 공부할 것

🔑오늘 막혔던 부분, 어떤 부분이 부족했는지 다시한번 3주차 숙제 리뷰하면서 4주차 학습 넘어가기

3주차 숙제 다음 조건으로 배달시간이 늦었는지 판단하는 값을 만들기
주중 : 25분 이상
주말 : 30분 이상