수업 목표

• Query 결과를 업무에 바로 사용할 수 있도록 문자(워딩) 다듬기
  • 문자 데이터는 있는 그대로만 사용 가능한 걸까?
• 수치계산 및 문자를 다듬을 때, 조건별로 다르게 적용하기
  • 배달 시간 구간에 따라서 수수료를 계산하고 싶은데, 시간을 조건으로 줄 수는 없을까?
• 수치계산과 문자 연산이 되지 않는 경우를 배우고 에러 수정하기
  • 수업에서 배운 대로 Query를 썼는데 왜 오류가 나는 걸까?

복습

• SQL 기본 구조

SELECT
FROM
WHERE
GROUP BY
ORDER BY

• 조회 조건과 매칭해보기
  • 주문 테이블에서 → FROM
  • 주문 수량이 1건인 주문건의 → WHERE
  • 음식 가격의 평균을 음식 종류별로 조회하여 → AVG, GROUP BY
  • 음식 가격이 높은 순서대로 정렬하기 → ORDER BY

A. 업무에 필요한 문자 포멧이 다를 때, SQL로 가공하기 (REPLACE, SUBSTRING, CONCAT)

1. Query 결과를 바로 사용할 수 없는 경우

→ 문자 데이터의 형태를 바꿔 Query 결과를 바로 사용할 수 있도록 해결

• 데이터를 조회하다 보면 Query 결과를 그대로 이용하지 못하는 경우가 있음
  • 데이터를 보니 잘못된 값이 있어요. 이전에 사용하던 값이어서 다른 문자로 수정을 해 줘야 하는데, 하나하나 수동으로 하기엔 너무 많아요.
    → 특정 문자를 다른 문자로 바꾸기
  • 주소 전체가 아닌 ‘시도’ 정보만 필요해요. 서울의 통계만 구하고 싶은데, 전체 주소가 아닌 ‘서울’로 문자를 변경할 수 있나요?
    → 원하는 문자만 남기기
  • 사업장 명과 지역이 함께 나와야 하는데 ‘사업장 [지역]’과 같은 형태로 문자 포맷을 변경할 수 있나요?
    → 여러 column의 문자 합치기
    

2. 특정 문자를 다른 것으로 바꾸기: REPLACE

• 바뀐 상점 이름, 지역 이름 한 번에 바꿀 수 있음
  • 사용 방법
    • REPLACE (바꿀 컬럼, 현재 값, 바꿀 값)
• 실습: 식당명의 ‘Blue Ribbon’을 ‘Pink Ribbon’으로 바꾸기

SELECT  restaurant_name "기존 상점명",
        REPLACE(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
FROM  	food_orders
WHERE 	restaurant_name LIKE '%Blue Ribbon%'

• 실습2: 주소의 ‘문곡리’를 ‘문가리’로 바꾸기

SELECT  addr "기존 주소",
        REPLACE(addr, '곡', '가') "바뀐 주소"
FROM 	food_orders
WHERE 	addr LIKE '%문곡리%'

3. 원하는 문자만 남기기: SUBSTR

• 전체 데이터가 아닌 특정 문자만 필요할 때, SQL로 필요한 특정 문자만 골라서 조회할 수 있음
  • 사용 방법
    • SUBSTRING(조회할 컬럼, 시작 위치, 글자 수)
    • SUBSTR(조회할 컬럼, 시작 위치, 글자 수)
• 실습: 서울 음식점들의 주소를 전체가 아닌 시도만 나오도록 수정

SELECT addr "기존 주소",
       SUBSTR(addr, 1, 2) "시도"
FROM food_orders
WHERE addr LIKE '%서울특별시%'

4. 여러 컬럼의 문자를 합치기: CONCAT

• 원하는 문자가 여러 컬럼에 있을 때 하나로 합칠 수 있음
  • 사용 방법
    • CONCAT(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, …)
  • 붙일 수 있는 문자의 종류
    • 컬럼
    • 한글
    • 영어
    • 숫자
    • 기타 특수문자
• 실습: 서울시에 있는 음식점은 ‘[서울] 음식점명’이라 수정

SELECT restaurant_name "기존 상호",
       addr "기존 주소",
       CONCAT('[', SUBSTR(addr, 1, 2), '] ', restaurant_name) "바뀐 상호"
FROM   food_orders
WHERE  addr LIKE '%서울특별시%'

B. [실습] 문자 데이터를 바꾸고, GROUP BY 사용하기

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

a. 어떤 테이블에서 → FROM food_orders
b. 어떤 컬럼 → price, cuisine_type, addr
c. 어떤 조건 → WHERE addr LIKE ‘%서울특별시%’
d. 어떤 함수(수식) → AVG(price), SUBSTR(addr, 1, 2)

SELECT SUBSTR(addr, 1, 2) as "시도",
       cuisine_type as "타입",
       AVG(price) as "평균 금액"
FROM food_orders
WHERE addr LIKE '서울%'
GROUP BY SUBSTR(addr, 1, 2), cuisine_type

💡 조회할(범주를 묶어줄) 컬럼의 위치를 적어서 GROUP BY를 명명해줄 수 있다:
위의 예시에서
column1 = SUBSTR(addr, 1, 2) as *"시도" ,
column2 = cuisine_type as "타입",
column3 = AVG(price) as "평균 금액" 이므로
GROUP BY SUBSTR(addr, 1, 2), cuisine_type을 GROUP BY 1, 2라 적을 수 있음

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

SELECT SUBSTR(email,10) "도메인",
       COUNT(1) "고객 수",
       AVG(age) "평균 연령"
FROM customers
GROUP BY 1

💡 SUBSTR(email, 10)이 마음에 안 들어서 좀 더 찾아봤음
→ 구분자를 이용해 문자열을 잘라오는 함수 SUBSTRING_INDEX
SUBSTRING_INDEX(email, '@', -1)
→ 돌려봤더니 실습 데이터에는 at symbol이 없는 메일 주소가 있어서 예쁘게는 안 나옴

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

SELECT CONCAT('[', SUBSTR(addr, 1, 2), '] ', restaurant_name, ' (' ,cuisine_type, ')') "바뀐이름",
       COUNT(1) "주문건수"
FROM food_orders
GROUP BY 1

C. 조건에 따라 포맷을 다르게 변경해야 한다면 (IF, CASE)

1. GROUP BY처럼 조건도 카테고리별로 적용하고 싶을 때

• 범주별로 다른 연산 (계산, 문자 바꾸기) 적용 → SQL은 조건에 따라 연산을 적용할 수 있는 기능 제공함
  • ‘내가 원하는 범주’를 조건으로 주고, 해당 범주에 적용하고 싶은 것을 지정

2. IF문 기초

• 원하는 조건에 충족할 때 적용할 방법과 아닌 방법 지정
  • 조건을 지정해주는 가장 기초 문법(엑셀 기능과 유사함)
    • 사용 방법

	      IF(condition, value_if_true, value_if_false)

• 실습 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_INDEX(IF(email LIKE '%gmail%', REPLACE(email, 'gmail', '@gmail'), email), '@', -1) "이메일 도메인",
	    COUNT(customer_id) "고객 수",
	    AVG(age) "평균 연령" 
FROM 	customers
GROUP BY 1

3. CASE문 기초

• 여러 가지 조건을 지정하고 싶을 때
  • CASE문은 각 조건별로 적용할 값을 지정해 줄 수 있음
• 조건별로 지정을 해주기 때문에 여러 번 IF문을 적용한 효과를 낼 수 있음
• 사용 방법
  

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
ELSE result
END;

• 실습 1: 음식 타입을 ‘Korean’일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chinese’일 때는 ‘아시아’, 그 외에는 ‘기타’라고 지정
  

SELECT 	restaurant_name,
   		cuisine_type "원래 음식 타입",
   		CASE
   			WHEN cuisine_type='Korean' THEN '한식'
   			WHEN (cuisine_type='Japanese' OR cuisine_type = 'Chinese') THEN '아시아'
   			ELSE '기타'
      		END AS "음식 타입"
FROM 	food_orders

💡 WHEN cuisine_type IN (’Japanese’, ‘Chinese’) THEN ‘아시아’

• 실습 2: 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때에는 음식가격÷주문수량으로 지정

SELECT 	order_id,
   		price,
   		quantity,
   		CASE 
   			WHEN quantity = 1 THEN price
   			WHEN quantity >= 2 THEN price/quantity
   		END AS "음식 단가"
FROM 	food_orders

💡 IF로도 쓸 수 있음!

SELECT 	order_id,
  		price,
  		quantity,
  		IF(quantity = 1, price, price/quantity) "음식 단가"
FROM 	food_orders

• 실습 3: 주소의 시도를 ‘경기도’일 때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용
  

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 AS "변경된 주소"
FROM 	food_orders

4. 조건을 사용할 수 있는 경우: 활용 케이스 살펴보기

• 새로운 카테고리, 분류 만들기
  • 한국 음식, 아시아 음식, 미국 음식, 유럽 음식
    • 10대 여성, 10대 남성, 20대 여성, 20대 남성 등
• 연산식을 적용할 조건 지정
  • 현금일 때의 수수료율과 카드일 때의 수수료율이 다를 경우 연산식을 만들 때 IF문 또는 CASE문으로 각각 다른 수수요율 혹은 수수료 계산 방식 적용 가능
• 다른 문법 안에서 적용
  • CONCAT문으로 여러 컬럼을 합칠 때, rating이 있을 때는 rating을 넣어주고 없으면 아무것도 넣지 않도록 CONCAT 안에 IF문을 넣을 수 있음

D. [실습] SQL 조건문과 수식을 이용하여 간단한 User Segmentation 해보기

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

a. 어떤 테이블에서 데이터를 뽑을 것인가 → from customers
b. 어떤 컬럼을 이용할 것인가 → name, age, gender
c. 어떤 조건을 지정해야 하는가 → 10 ≤ age < 30 → WHERE age BETWEEN 10 AND 29
d. 어떤 함수(수식) 이용해야 하는가 → CASE
e. 그룹 나누기 → 10대 여성, 10대 남성, 20대 여성, 20대 남성

SELECT 	name,
		age,
    	gender,
    	CASE 
    		WHEN (age BETWEEN 10 AND 19) AND gender='female' THEN "10대 여성"
    		WHEN (age BETWEEN 10 AND 19) AND gender='male' THEN "10대 남성"
    		WHEN (age BETWEEN 20 AND 29) AND gender='female' THEN "20대 여성"
    		WHEN (age BETWEEN 20 AND 29) AND gender='male' THEN "20대 남성"
    	END "그룹"
FROM 	customers
WHERE age BETWEEN 10 AND 29;

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

• 음식 종류
  • Korean = 한식
  • Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식
  • 그 외 = 기타
• 음식 단가
  • 단가 구분: 5000, 15000, 그 이상 → 3가지

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;

E. [실습] 조건문으로 서로 다른 식을 적용한 수수료 구하기

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

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

SELECT	order_id "주문 번호",
  		restaurant_name "식당 이름",
  		delivery_time "배달 시간",
  		price "가격",
  		addr "주소",
  		CASE 
   			WHEN delivery_time > 30 THEN price*0.1*IF(addr LIKE '%서울특별시%', 1.1, 1)
			WHEN delivery_time > 25 THEN price*0.05*IF(addr LIKE '%서울특별시%', 1.1, 1)
 			ELSE 0
  		END "배달수수료"
FROM 	food_orders

💡 30분 이상 25분 초과(delivery_time > 25 AND delivery_time <= 30)인데 25분 초과(delivery_time > 25)만 적어도 되는 이유: 첫 번째 WHEN에서 30분 초과라는 조건을 줘서 이미 30분 초과가 걸러진 다음 두 번째 WHEN으로 넘어가기 때문
하지만 조금 더 엄밀히 하고 싶다면 BETWEEN을 활용하는 것이 좋다. (delivery_time BETWEEN 26 AND 30)

2. 주문 시기와 음식 수를 기반으로 배달할증료 구하기

• 주문 시기
  • 평일: 3,000
  • 주말: 3,500
• 음식 수
  • 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

F. Data Type 오류 해결하기

• 숫자 계산이나 문자 가공 시 자주 발생하는 오류
  • SQL문에 문제가 없는 것 같은데 문자, 숫자 계산을 했더니 에러메시지에 ‘data type’ 단어가 쓰면서 오류가 날 수 있음
    • 우리가 실습하는 MySQL과 다르게 다른 SQL 문법에서는 data type이 다를 때 연산이 되지 않을 수 있음
• 숫자가 포함되어 있지만 문자 형으로 저장이 되어있을 수 있음
  • 출력 결과 컬럼명 옆의 ‘ABC’ 혹은 ‘123’ 확인하기
    • ‘ABC’는 문자로 저장이 되어 있다는 의미
  • 따라서 문자, 숫자를 혼합하여 함수에 사용할 때에는 데이터 타입을 변경해 주어야 함
    • 숫자로 변경
      CAST(IF(rating=’Not given’, ‘1’, rating) AS DECIMAL)
    • 문자로 변경
      CONCAT(restaurant_name, ‘-’, CAST(order_id AS CHAR))
      

3주차 숙제

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

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

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 = 'Weekend' AND delivery_time >= 30 THEN 'Late'
			ELSE 'On-time'
		END "배달지연여부"
FROM 	food_orders