2024.11.08 TIL(AI 9기)

[TIL]

SQL 강의

특정문자를 다른 문자로 바꾸기

• 함수명 : replace
• 사용 방법

replace(바꿀 컬럼, 현재 값, 바꿀 값)

 

원하는 문자만 남기기

• 함수명 : substring (substr)
• 사용 방법

substr(조회 할 컬럼, 시작 위치, 글자 수)

 

여러 컬럼의 문자를 합치기

 

• 함수명 : concat
• 사용 방법

concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)

 

• 붙일 수 있는 문자의 종류
  • 컬럼
  • 한글
  • 영어
  • 숫자
  • 기타 특수문자

 

 

1) [실습] 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)

select substring(addr, 1, 2) "서울",
		cuisine_type "타입",
		AVG(price) "평균금액"
FROM food_orders 
WHERE addr like '%서울%'
GROUP BY 1, 2 
#GROUP BY에 1, 2는 컬럼 순서대로 substring(addr, 1, 2), cuisine_type을 뜻하고, 온전히 다 적어줘도 된다.

 

 

2) [실습] 이메일 도메인별 고객 수와 평균 연령 구하기

SELECT SUBSTRING(email, 10) "도메인", #불러올 글자에서 마지막까지 모두 불러오려면 앞글자 숫자만 적으면 된다.
		COUNT(customer_id) "고객 수",
		AVG(age) "평균 연령" 
FROM customers
group by 1 #첫컬럼만

이 실습에서 선생님이 이메일 아이디 글자수를 맞춰두셔서 섭스트링에 10을 적을 수 있었지만, 만약에 글자수가 다르다면 어떻게 해야할지 의문이다.

 

그래서 이 부분을 질문방에 올려서 물어봤다.

답변 : substr과 instr을 통해 출력할 수 있다. instr은 지정한 값의 인덱스 위치를 나타낸다. instr(email, @)+1을 하면 email값의 @ 문자 뒤의 인덱스 값을 나타낸다.

select substr(email, instr(email, @) + 1)

 

 

3) [실습] ‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기

SELECT CONCAT('[',SUBSTRING(addr, 1, 2), ']',restaurant_name, '(', cuisine_type, ')'), 
	COUNT(1) 
FROM food_orders
group by 1

 

IF문

사용방법

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

#예시1
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
select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

 

case문

여러번의 if문을 적용 한 효과

각 조건별로 적용 할 값을 지정해 줄 수 있다.

 

사용방법

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end

#예시1
select order_id,
       price,
       quantity,
       case when quantity=1 then price
            when quantity>=2 then price/quantity end "음식 단가"
from food_orders

#예시2
select restaurant_name,
       addr,
       case when addr like '%경기도%' then '경기도'
            when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5)
            else substring(addr, 1, 2) end "변경된 주소"
from food_orders

 

조건을 사용할 수 있는 경우

• 새로운 카테고리 만들기
• 연산식을 적용할 조건 지정하기
  • 수수료를 계산할 때 흔히들 현금 사용, 카드사용을 나누는데, 각각의 수수료율 연산식을 만들 때 if 문 혹은 case 문으로 각각 다른 수수료율 혹은 수수료 계산 방식을 적용할 수 있다
• 다른 문법 안에서 적용하기
  • if, case 문 안에 다른 문법이나 연산을 넣을 수도 있지만, 다른 문법 안에 조건문을 넣을 수도 있다
  • 예를 들어 concat 문으로 여러 컬럼을 합칠 때, rating 이 있을 때는 rating 을 넣어주고 없을 때는 아무것도 넣지 않도록, concat 안에 if 문을 넣어줄 수 있다.

#긴 예시
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

 

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

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

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