문자열 마법사 되는법.sql

• 문자 데이터는 있는 그대로만 사용 가능한걸까?
• 배달 시간 구간에 따라서 수수료를 계산하고 싶은데, 시간을 조건으로 줄 수는 없는걸까?
• 수업에서 배운대로 Query 를 썼는데 왜 오류가 나는걸까?

replace

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

select addr,
	replace(addr,'문곡리','용가리') as "변경 주소"
from food_orders fo 
WHERE addr like '%문곡리%';

substr

원하는 문자만 남기기

select addr "원래 주소",
       substr(addr, 1, 2) "시도" -- n부터 n글자만큼 
from food_orders
where addr like '%대구%'

concat

여러 컬럼의 문자를 합치기
concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)

select    
       replace (addr, "직할시", '광역시') as '대구 지역 음식점',
       concat ('[',cuisine_type , '] ', restaurant_name) "음식점",
       concat ('[', substring(addr, 1, 2), '] ', restaurant_name) "맛집"
from food_orders
where addr like '%대구%'

실습1

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

select    
	substr(addr, 1, 2) '지역',
    cuisine_type '음식 타입',   
	avg(price) '평균 금액'
from food_orders
where addr like '%서울%'
group by 1, cuisine_type --컬럼의 위치로도 지정할 수 있다.

실습2

이메일 도메인별 고객 수와 평균 연령 구하기

select substr(email, 10 ) as 'domain',-- 모든 아이디가 8글자다
	count(*) '조건 별 전체 고객 수',
	avg(age) '평균 연령'
from customers c 
group by 1

실습3

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

select 
	concat
		('[', substr(addr,1,2),'] ', restaurant_name, 
			' (', cuisine_type, ')') as 'restaurant',
		count(*) 'total_order'
from food_orders
group by 1

조건문 마법사 되는법.sql

IF (실습)

조건에 따라 다른 연산을 하는 방법을 알아봅시다

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

CASE (실습)

1. 2개 이상의 조건문을 지정 할 때 사용. 조건별 다른 결과 값을 생성, 컬럼으로 취급.

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

---

2. 결과 값에 기타가 너무 많아서,
   한식/아시아 결과값만 추출하고 싶은데 아직 방법을 모르겠땅.
   별칭으로는 안된다.
   (해결) 아 기존의 컬럼 결과 값을 사용 하면 됨!

select restaurant_name '식당 이름',
       cuisine_type "영문명 카테고리",
       case when cuisine_type='Korean' then '한식' 
       		when cuisine_type in ('Japanese','Chinese') then '아시아'
       		else '기타'
       end as '한글명 카테고리'
from food_orders
where cuisine_type in ('Korean','Japanese','Chinese');

---

3. 구주소인 직할시를 광역시로 변경해서 출력하고,
   ‘경기도’ 일때는 ‘경기도’만,
   ‘특별시’ 혹은 ‘광역시’ 일 때는 도시이름에 붙여서 출력

select restaurant_name '가게 이름',
       addr '가게 주소',
       case when addr like '%경기도%' then '경기도'
            when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5)
            else substr(replace(addr, '직할시', '광역시'), 1, 5)
            end "관할 지역"
from food_orders;

실습5

조건문과 수식을 이용하여 분류하고, 새로운 카데고리 생성하기.

Q1. 연령 카테고리

10세 이상, 30세 미만의 고객을 나이와 성별로 그룹 나누기 (이름도 같이 출력)
쿼리 문을 작성 하기 전에 이걸 어떻게 카테고리화 할지 생각해야 한다.

• 10대 여자, 10대 남자
• 20대 여자, 20대 남자

select name '이름', age '나이', gender '성별',
	case
		when (age >=10 and age < 20) and gender = 'female' then '10대 여자'
		when (age >=20 and age < 30) and gender = 'female' then '20대 여자'
		when (age >=10 and age < 20) and gender = 'male' then '10대 남자'
		when (age >=20 and age < 30) and gender = 'male' then '20대 남자'
	end 그룹
from customers
WHERE age >= 10 and age < 30;

정렬하면 결과 값이 깔끔하다. (큰 범주를 먼저 정렬 후, 소 범주를 정렬)

order by gender, age

Q2. 인원별 맛집 추천

음식 단가, 음식 종류 별로 음식점 그룹 나누기

• Korean = 한식
• Japanese, Chinese, Thai, Vietnamese, Indian = 아시안
• 그외 = 기타
• 가격 = 12000, 25000, 그 이상 (현 물가 반영)
  가격대별 음식 종류로 분류해야 한다.
  12000 => 한식 1인...
  25000 <= 아시안 2인...
  25000 > 기타 3인...

흠.. 줄이 길어질 것 같은데 일단 작성해 보자.
(기타는 not in 써서 표현 할 수 있음. 결과값에서 제외 시킴)

select restaurant_name '가게명', cuisine_type '음식 종류', price '가격', 
	CASE
		WHEN price <= 12000 and cuisine_type = 'Korean' then '1인 한식'
		WHEN price > 12000 and price <= 25000 and cuisine_type = 'Korean' then '2인 한식'
		WHEN price > 25000 and cuisine_type = 'Korean' then '3인 한식'
		WHEN price <= 12000 and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '1인 아시안'
		WHEN price > 12000 and price <= 25000 and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '2인 아시안'
		WHEN price > 25000 and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '3인 아시안'
		ELSE '준비 중'
	END '추천 카테고리' 
FROM food_orders 
where cuisine_type in ('Korean','Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') 

아 수량에 따른 단가 반영이 안되었다.. 단가를 다시 반영해서 수정.
단가 소수점 날리기 - floor()

select restaurant_name '가게명', cuisine_type '음식 종류', floor(price/quantity) '단가', 
	CASE
		WHEN price/quantity <= 12000 and cuisine_type = 'Korean' then '1인 한식'
		WHEN price/quantity > 12000 and price/quantity <= 25000 and cuisine_type = 'Korean' then '2인 한식'
		WHEN price/quantity > 25000 and cuisine_type = 'Korean' then '3인 한식'
		WHEN price/quantity <= 12000 and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '1인 아시안'
		WHEN price/quantity > 12000 and price/quantity <= 25000 and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '2인 아시안'
		WHEN price/quantity > 25000 and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '3인 아시안'
		ELSE '준비 안됨'
	END '추천 카테고리' 
FROM food_orders 
where cuisine_type in ('Korean','Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian')

단가 반영 전과 바뀐 결과 값의 Tamarind TriBeCa 음식점을 보면

select restaurant_name '가게명', cuisine_type '음식 종류', 
		price '가격', quantity '수량',
		floor(price/quantity) '단가'
FROM food_orders 
where restaurant_name = 'Tamarind TriBeCa'

단가를 반영 했을 시, 1인 아시안푸드 음식점인것을 정확히 알 수 있다.