[SQL 3주차 수업 목표]

Query 결과를 업무에 바로 사용할 수 있도록 문자 (워딩) 를 다듬는다
수치계산 및 문자를 다듬을 때, 조건별로 다르게 적용한다
수치계산과 문자 연산이 되지 않는 경우를 배우고, 에러를 수정한다

3-1 SQL문의 기본 구조 복습 (순서 중요)

select - 컬럼 지정
from - 컬럼 가져올 테이블
where - 조건
group by - 같은 건 그룹으로 묶기
order by - 정렬 ASC(기본), DESC

이번 수업에서 배울 내용 맛보기

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

3-2 replace, substr, concat

replace = 특정 문자를 다른 것으로 변경하는 함수

사용예시 1번 - 상점명이 변경되었다. "blue" -> "pink"로 수정하라

# 사용방법
# replace(컬럼_1, 현재 값, 변경 값)

select restaurant_name "원래 상점명" # 식당이름 컬럼 -> "원래 상점명"으로 별칭
		replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
        # 식당이름 컬럼의 'Blue'를 'Pink'로 변경하고 "바뀐 상점명"으로 별칭
        
from food_orders # food_orders 테이블에서

where restaurant_name like '%Blue Ribbon%' # 식당이름 중간에 'Blue Ribbon' 들어가면 출력

사용예시 2번 - 주소의 '문곡리'를 '문가리'로 바꾸기

/* 컬럼 = 주소니까 addr 컬럼 선택
테이블 = 음식점 주소 -> food_orders
조건 = 이름이 '문곡리'가 들어가야 함
함수, 수식 = '문곡리'-> '문가리'니까 select절에서 replace 사용 + where절에서 like 사용 '문곡리' 찾기 */

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

substr = 선택한 컬럼에서 원하는 문자만 남기기

# 사용방법
substr (컬럼, 시작 위치, 글자 수)

사용예시 1번 - 서울 음식점들의 주소를 전체가 아닌 '시도'만 나오도록 수정 ex) 서울특별시 -> 서울, 경기도 -> 경기

컬럼 = 주소관련 addr
테이블 = 서울음식점 주소 food_orders
조건 = 서울특별시 -> 서울 
함수 = 서울특별시라는 주소만 찾아야 하니 like '%서울특별시%'

select addr "원래 주소"
	substr(addr, 1, 2) 
    # (주소에서, 첫 번째 글자부터, 2글자만)
from food_orders
where addr like '%서울특별시%'

concat = 앞 뒤로 붙이기

사용예시 1번 - 서울시에 있는 음식점은 '[서울] 음식점명'으로 수정하라.

/* 컬럼 = 음식점명 restaurant_name
테이블 = 음식관련 food_orders
조건 = 서울시 음식점 이름을 -> [서울] 음식점으로 
함수, 수식 = 서울 앞 뒤로 [] 필요 -> concat, 
	서울 음식점만 선택 -> like '%서울%', 
    서울 선택하고 서울 2글자만 출력 -> substr */

select restaurant_name "원래 이름"
	addr "원래 주소"
    concat('[',substr(addr, 1, 2),']', restaurant_name) "바뀐 이름"
    # [ + 서울 + ] + 음식점명 as 바뀐이름
from food_orders
where addr like '%서울%'

3-3 실습 문자 데이터를 바꾸고, GROUP BY 사용하기

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

select substr(addr,1,2), cuisine_type, AVG(price)
# 문제에 "서울" 출력하라고 되었으니 substr 사용
# 음식 타입별 -> cuisine 사용
# 평균 가격 -> AVG(price)
# 
from food_orders
# 음식 타입, 가격, 주소가 있는 테이블 -> food_orders
#
where addr like '%서울%'
# 서울 지역에 있는 -> like 사용해서 서울지역만 출력
group by 1,2
# group by절에서 1,2 는 Select절 첫 번째 컬럼, 두 번째 컬럼 가져오라는 뜻

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

select substr(email,10) as "도메인", # 문제에서 이메일 주소가 8글자로 고정이라 substr 가능
# 만약 이메일 주소 길이가 다르다면 substring.index() 사용가능 
# 하지만 여기 이메일 주소 @ 없는 것도 있어서 못씀
	COUNT(1) as"고객 수", 
	avg(age) "평균나이" 
from customers
group by 1

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

select CONCAT(
	'[', SUBSTR(addr, 1, 2), ']', restaurant_name, cuisine_type
    	) "지역별 음식점",
	COUNT(order_id) "주문건수"
from food_orders 
group by 1

3-4 IF, CASE

IF문 구조

if (컬럼_1 = A, //  A면 ' ' 출력, //  아니라면 ' '출력)

if문 사용예시

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

SELECT  restaurant_name ,
	cuisine_type ,
	IF (cuisine_type ='Korean', '한식', '기타')
FROM food_orders

[실습2]
02. 번 실습에서 ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정

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

[실습3]
03. 번 실습에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용

SELECT 
  SUBSTRING(
    IF(email LIKE '%gmail%', REPLACE(email, 'gmail', '@gmail'), email),
    10
  ) AS "이메일 도메인", #여기서 substring(...,10)은 10번째부터 도메인시작
  COUNT(customer_id),
  AVG(age)
FROM customers
GROUP BY 1;

case 문

case 
when '조건1' then '값(수식)1'
when '조건2' then '값(수식)2'
else '값(수식)3' # 위에 모든 조건에 충족하지 않을 때

5 [실습] SQL로 간단한 User Segmentation 해보기

1) [실습] 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)

SELECT age "나이",gender "성별", name "이름",
case
	when (age between 10 and 19) and gender = 'male' then "10대 남성"
	when (age between 10 and 19) and gender = 'female' then "10대 여성"
	when (age between 20 and 29) and gender = 'male' then "20대 남성"
	when (age between 20 and 29) and gender = 'female' then "20대 여성"
	end "그룹"
from customers c 
where age between 10 and 29

2) [실습] 음식 단가, 음식 종류 별로 음식점 그룹 나누기
    
    (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

3-6 [실습] 조건문으로 서로 다른 식을 적용한 수수료 구해보기

- 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

- 2) [실습] 주문 시기와 음식 수를 기반으로 배달할증료 구하기
    
    (주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500
     음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)
     
     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

3-7 SQL문에 문제가 없는 것 같은데 왜 오류가 나나요_ (Data Type 오류 해결하기)

컬럼명 옆에 ABC, 123 이렇게 적혀있는데

ABC = 문자열 데이터

123 = 정수형 데이터다.

그리고 문자열과 정수형은 아래 함수로 변환이 가능하다.

--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 

--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

3주차 숙제

1. 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.

주중 : 25분 이상
주말 : 30분 이상

SQL 문의 기본 구조로 시작
조건을 여러번 적용할 때 if, case 문 중 어떤 것을 이용할지 결정
조건에 ‘주중, 주말’ 조건과 ‘배달시간’ 조건을 동시에 줄 때 사용 할 논리연산자 결정

select 
order_id,
restaurant_name,
day_of_the_week,
delivery_time,
case
	when day_of_the_week = 'Weekday' and delivery_time >= 25 then 'Late'
	when day_of_the_week = 'Weekday' and delivery_time < 25 then 'On-time'
	when day_of_the_week = 'Weekend' and delivery_time >= 30 then 'Late'
	when day_of_the_week = 'Weekend' and delivery_time < 30 then 'On-time'
end "지연여부"
FROM food_orders

내가 만든 쿼리도 같은 값이 나오지만

정답은 else를 사용해서 쿼리를 좀 더 단순하게 만들 수 있었다.

case when day_of_the_week='Weekday' and delivery_time>=25 then 'Late'
            when day_of_the_week='Weekend' and delivery_time>=30 then 'Late'
            else 'On-time' end "지연여부"

다음에는 풀이가 끝나고 어떻게 하면 더 간단하게 만들 수 있을지에 대해 생각해 봐야겠다.